2012 FoxPro Lifetime Achievement Award

Wow .. during the keynote of the 2012 Southwest Fox conference (www.swfox.net), I was presented with the 2012 FoxPro Lifetime Achievement Award. This was a surprise to me! Nobody told me this was going to happen! I was so caught off guard that I didn't even say "Thanks" or give an acceptance speech! Heck .. I'm only 25, so how in the world could I be considered a candidate for a "Lifetime" award (humor me .. it's my fantasy.) Seriously, though, where do I even begin to express how humbled I am to have received this? The list of previous recipients includes many well-respected people in the FoxPro Community (Click here for a full list).

I began working with Fox products in 1989. It took 10 years before I actually attended my first conference and realized there was a great FoxPro Community. I quickly discovered it was so gracious, so helpful, so supportive and so welcoming. It didn't take long before I stepped out my comfort zone and began writing articles, writing books, and speaking at conferences. It's been a wonderful journey. It has forced me to learn things that I wouldn't have otherwise known. It has forced me to dig deep, be tenacious, and not accept "that can't be done" as an answer. It has forced me to keep my skill set sharp and continue to discover new and creative solutions.

What I hadn't predicted was what a wonderful group of friends I would gain along the way. The FoxPro Community is such a friendly group of people. They share their knowledge openly and freely. They truly want to help others. I've been lovingly dubbed the "Reporting Queen" by the FoxPro Community and I wear that crown with great honor. I'm so glad that I've been given the opportunity to give back to the community that has given so much to me.

To everyone in the FoxPro Community, please accept my heartfelt "Thanks" for your friendship, your knowledge, your support, and this wonderful award!

8th Microsoft MVP Award Received!

I am thrilled that once again I have been honored with the Microsoft Most Valuable Professional award for my contributions to the VFP Community. This is my 8th year in a row and it's always a great feeling to be recognized for your efforts. The sad news is there are only 14 recipients this year. That means many other well-deserving people who are serving the Fox Community didn't receive the award. On the other hand, it also makes me feel even more honored that I'm included in this group of wonderful people. See https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Visual+FoxPro for a complete list of VFP MVPs.

New VFP 9 SP2 and Sedna Book Available

After a really long time in gestation, “Making Sense of Sedna and SP2” has finally been published by dFPUG in both English and German. This book, co-authored by Tamar E. Granor, Toni Feltman, Doug Hennig, Rick Schummer, Bo Durban, and me, goes into tremendous depth on VFP 9 Service Pack 2 and Sedna, including installation gotchas, things that work, things that almost work, and how to make the use of the new features in both products. “Making Sense of Sedna and SP2” is available now from Hentzenwerke Publishing as an e-book and will be available in printed version in July.

Special Southwest Fox Rehearsal in Lansing, MI

Southwest Fox is the best FoxPro conference in North America and this year, 8 of the 16 speakers live in the Great Lakes region. We’re proud of this fact .. but .. it’s created a problem because they’re all vying for limited user group slots to practice their sessions. To overcome this issue, the three FoxPro user groups in Michigan have banded together to create a special one-day event as a platform for these speakers to practice their conference sessions. Please join us for this wonderful event!

Info at a glance:

Southwest Fox Rehearsal

Saturday, August 29, 2009

8:30 – 5:30

Springhill Suites Marriott, Lansing, MI

Cost: $20.00 donation to cover beverages, snacks and lunch

Speakers/Sessions:

Mike Feltman – HTML and Visual FoxPro

Toni Feltman – Data Driven Applications

Walt Krzystek – SQL Server Tips & Tricks

Jody Meyer – Speak to Me: Applying MS Office Automation to Real Business Needs

Paul Mrozowski – Refactoring Legacy Code

Cathy Pountney – Making the Most of VFP 9 SP2 Reports (Part I)

The FoxPro Community Gives:

The organizers and speakers are all donating their time to this event. In addition, MMFUDG, GRAFUG, and White Light Computing are donating money to cover the cost of the room and equipment. All we’re asking from you is a $20 donation to cover the cost of snacks, beverages, and lunch. All profits above our expenses will be donated to the Ceil Silver Ambassador Fund.

The Ceil Silver Ambassador Fund is intended to raise money from the VFP community to bring a developer to the Southwest Fox conference as an ambassador for the developers in his or her country. This gives the recipient the opportunity to meet and share experiences with developers attending Southwest Fox and gives other attendees the opportunity to learn about VFP development in the recipient's country. The Fund is named in memory of Ceil Silver, a long-time member of the Visual FoxPro community, who passed away in January, 2009. Read more about the fund here: http://www.swfox.net/ambassador.aspx.

You can help too by making an additional donation to the Ceil Silver Ambassador Fund. To sweeten the pot the organizers of the Southwest Fox Conference have made a generous offer. Any person who is not already registered for Southwest Fox 2009 and makes a donation over the $20 will receive a dollar-for-dollar discount off their registration for Southwest Fox (over the $20, up to $125). It’s like getting the Super-Saver Early Bird discount AFTER the deadline. In fact, the organizers will even throw in a free pre-conference session for those who donate $50 or more above the $20 food cost.

Registration:

Only 40 seats are available so please register soon to ensure your spot. To register, please send an email confirmation along with your lunch choice (see below) to Rick Schummer (DAFUG) and send payment of $20 (or more) by check or paypal.

PayPal:

rick@rickschummer.com (No credit cards please / Use the “Personal” option)

Check:

DAFUG

c/o Rick Schummer

42759 Flis Dr.

Sterling Heights, MI 48314

Hotel:

For anyone interested in staying at the hotel, a room rate of $89 has been negotiated. Just mention that you’re attending this special one-day seminar.

Springhill Suites Marriot

111 South Marketplace Boulevard

Lansing MI 48917

Schedule:

Here’s the planned schedule (order of the speakers will be determined at game time).

8:30 – 9:45 1st presentation

10:00 – 11:15 2^nd presentation

11:30 – 12:45 3^rd presentation

Lunch (30 mins)

1:15 – 2:30 4^th presentation

2:45 – 4:00 5^th presentation

4:15 – 5:30 6^th presentation

We hope to see all of you there!!

Rick Bean (MMFUDG), Rick Schummer (DAFUG), Jody Meyer (GRAFUG), Cathy Pountney (GRAFUG)

LUNCH CHOICES:

You have the choice of a sandwich or wrap.

If you want a sandwich - Choose one from each category:

Breads: White / Wheat / Marble Rye / Roll / Croissant

Meats: Ham / Roast Beef / Turkey / Salami / Pastrami / Chicken / Liverwurst

Cheeses: American / Cheddar / Swiss / Provolone

If you want a wrap – Choose one of the following:

Caesar Salad Veggie Wrap

Italian Veggie Wrap

Available Condiments – Choose as many as you want:

Mustard / Dijon / Mayo / Miracle Whip / Lettuce / Tomato / Onion / Pickle / Banana Peppers

Side choices – Choose one of the following:

Chips (default) / Fruit Cup / Cottage Cheese / Applesauce / Coleslaw

Deli spear – Available upon request.

Beverages

Coffee, tea & bottled water will be available throughout the day.

A selection of pop will be available as well (feel free to offer suggestions).

Southwest Fox 2009

I hope you'll consider joining everyone at Southwest Fox 2009. The Visual FoxPro conference for Visual FoxPro developers will be held October 15-18, 2009 at the Arizona Golf Resort and Conference Center. Register by July 1st for the Super Saver registration that offers $125 off the conference fee and includes one free pre-conference session.

Southwest Fox includes a terrific selection of sessions from great presenters. Each of the 28 regular conference topics will be offered twice and the conference materials will include a white paper for each, so you can learn about the topics you can't fit into your schedule. There will be four sessions to choose from in each of the regular conference slots, so you'll always be able to find a topic that appeals. Plus the four pre-conference sessions give you a chance to come up to speed in areas you know you need.

You can find the complete list of speakers (http://www.swfox.net/speakers.aspx) and topics (http://www.swfox.net/sessions.aspx) on the Southwest Fox website.

Going to a conference is one of the best investments you can make in your career. The opportunity to learn about Visual FoxPro and related technologies from industry experts is worth the cost of admission. But a conference also gives you the chance to interact with your colleagues, renewing old relationships, turning virtual friendships into live ones and forming new ones. The time you spend talking with people between sessions, at meals, and in the hotel bar or lobby after hours turns out to be as valuable as the sessions themselves.

If you've never been to a VFP conference or to Southwest Fox, make 2009 the year you give it a try. If it's been a while since you made it to a conference, I urge you to come back. And if you were at Southwest Fox last year, you know why you shouldn't miss this year's edition.

On top of all the benefits Southwest Fox offers you personally, there's also a benefit for your local user group. For each member of a user group who attends, their group will receive $25. That will help run the group, contributing toward bringing speakers in from elsewhere, and providing occasional meeting refreshments. Please let your local user group know if you plan on attending so they can let the organizers know you are a part of the group.

Cathy to speak at Southwest Fox 2009

I'm excited to announce that I will be speaking at Southwest Fox 2009 and will be making two presentations:

Making the Most of VFP 9 SP2 Reports (Part I)

In addition to lots of bug fixes, VFP 9 SP2 offered up some major enhancements to the Visual FoxPro Report Writer. This session (the first of a 2-part series) explores the new Dynamics, Advanced, and Rotation features. It also takes you behind the scenes and explains how these features are implemented. If you want to write professional reports with lots of pizzazz, this session is a must-see.

You will learn:

• How to correctly install SP2 and obtain the hotfixes
• How to use the new Dynamics feature (e.g. negative numbers in red)
• How to use the new Advanced features (e.g. HTML-related properties)
• How to use the new Rotation feature
• How the new features are implemented behind the scenes

Making the Most of VFP 9 SP2 Reports (Part II)

Part I of this 2-part series shows you how to use the new reporting features introduced in VFP 9 SP2. This session steps it up several notches by showing you how to add your own features. Not only will you learn how to implement those features on your reports, but you'll also learn how to make the features available in the Report Builder's UI. In addition, you'll see a "best practices" methodology that makes it easy for developers to share their enhancements with one another. You don't want to miss this session. Come be a part of history in the making!

You will learn:

• How to dynamically shrink a font
• How to create text and graphic watermarks
• How to modify the Report UI to include your custom features
• How to data-drive all your custom features
• How to share new features with other developers

There's a wonderful lineup of speakers and sessions this year so I hope you all can make it to Mesa, Arizona from October 15 through the 18th. You can read all the details on the website: www.swfox.net.

SET TALK appears to be on when running reports with SP2

I've been using VFP 9 SP2 ever since it first came out which was a long time ago. Yeah, I know there was that nasty Data Group bug, but to me, the enhancements in SP2 far outweighed the negative. Now that Microsoft has released a hotfix for the Data Group bug, I'm hoping more developers jump on the bandwagon and start adopting SP2. That said, a colleague reminded me last night about a bug where text is echoed to the screen as if a mysterious SET TALK ON had been issued.

This is a bug, but it’s one that you can easily fix yourself. You see, some of the new enhancements in SP2 were not implemented in the core VFP exe. They were implemented with a creative use of several Report Listener subclasses that reside in the FFC directory. When you run reports with SP2, the default behavior is to utilize these Report Listeners instead of the base VFP Report Listener class. The bug exists in one of those classes so you can change it yourself.

Modify the fxListener class of the _ReportListener class library and change the code in the LoadReport method. Simply move This.setFRXDataSessionEnvironment() so it comes before This.createHelperObjects() and the problem is solved.  

If you’re running on Vista, however, you have to be aware of the Virtual Store and work around it. When you edit the fxListener class, Vista makes a copy of the _ReportListener class library in the Virtual Store and your modifications are actually saved there and not in the C:\Program Files\Microsoft Visual FoxPro 9\FFC directory as you expected. Because a Virtual Store is specific to each user, it means the change you just made only works for the login you’re currently using. As soon as you switch to another login, the changes don’t exist for that user. To permanently fix the bug, move the _ReportListener class library from your Virtual Store back to the C:\Program Files\Microsoft Visual FoxPro 9\FFC directory, overwriting the original. Also, be sure to delete the _ReportListener class library from the Virtual Store for all users. If you forget this step, those users could be accessing an old copy of the class library.

VFP 9 SP2 Hotfix Updated

The VFP 9 SP2 Hotfix which addresses the Report Data Group bug (download from http://code.msdn.microsoft.com/KB968409) has been updated. This latest file includes the missing VFP9T.DLL (Multi-threaded runtime) file. In addition, the other hotfixes are no longer password protected.

Installing VFP 9 SP2

Now that Microsoft has related the HotFix for the Report Data Group bug (read about it here), developers who have been holding off are now upgrading to VFP 9 SP2. This is great and I'm glad to see it. However, I'd like to take the time to remind everyone about some "gotchas" that might happen if you don't follow the right procedure when upgrading. 

If you have VFP 9 SP1 installed on your machine, there's a problem with the installation when upgrading from SP1 to SP2. It doesn't tell you there's a problem, but it doesn't fully install everything. The recommended procedure is to completely uninstall VFP 9 from your machine. Then re-install VFP 9, without any service packs. Once that step is completed, install Service Pack 2. Do NOT install SP1 .. skip that entirely. Just go straight to SP2. 

If you are running Vista, you also have to remember about the Virtual Store so as not to get bitten. Whenever you change a file in the Program Files directory, Vista creates a copy in your Virtual Store and redirects all future access to the new file. When Visual FoxPro opens a class, it does something that makes the O/S think it's been changed. Therefore, if you have ever opened any classes in VFP's FFC directory, those classes are now copied and sitting in your Virtual Store. That means when you upgrade to SP2, it installs new FFC files in the Program Files directory, but Vista keeps referring to the old ones in the Virtual Store whenever you access them. It completely ignores the new ones loaded with SP2. To get around this, delete the files from your Virtual Store ... HOWEVER ... if you have actually changed any of the FFC classes, you'll need to make copies of the files in the Virtual Store before deleting them so you can go back and compare your changes to the new SP2 versions and merge as needed.  

For those of you who want to install SP2 and still keep plain-Jane VFP 9 or VFP 9 SP1, that's not a problem. Rick Schummer wrote a great whitepaper that explains how to do this (Rick's whitepaper). 

VFP 9 SP2 Hotfix Minor Glitch

A few days ago I blogged about Microsoft releasing a hotfix for the VFP 9 SP2 Report Data Group bug (read it here). Last night, Rick Schummer blogged about a slight glitch with the hotfix and let us all know that Microsoft will have a resolution quickly. Instead of repeating what he said, I'll just point you to his blog post: (read it here). 

 

Microsoft fixes Data Group Bug in Reports!

Woo Hoo!!   Yippee!!  Hooray!!  I'm jumping for joy!!

I'm extremely excited to let everyone know that Microsoft has fixed the serious Data Group bug in VFP 9 SP2 reports which I blogged about here. The bug is officially described in the following Microsoft KB article, where you'll also find detailed instructions on how to get the fix. 

FIX: The group header of a data grouping is not printed at the top of each page as expected after you intall Microsoft Visual FoxPro 9.0 Service Pack 2 (968409)

I've heard lots of developers say they've held off upgrading to SP2 because of this one bug. So those of you that fall in this category .. it's time to upgrade!! There are no more excuses!! 

Several people in the FoxPro Community, myself included, worked hard at getting this bug fixed and spent a lot of time testing it. Please ... I don't want to hear any bitching from anyone about how long it took to get this. Instead, let's all focus on the positive side which is the fact that Microsoft listened and provided a fix to a serious bug.

P.S.  Read Rick Schummer's blog for more thoughts about this bug fix.  

Workaround: Gap with Multiple Data Groups

In November, 2007, I reported a bug in the VFP 9 Report Writer that related to data groups (Bug 312572). This isn't the major data grouping bug that everyone's been talking about, but rather a smaller one I stumbled on while doing all my testing. It has to do with a strange gap of whitespace appearing on the report under certain circumstances as shown in the following:



After getting some insight from a developer at Microsoft, I was able to understand what was going on behind the scenes. That knowledge helped me figure out a workaround for this issue.

The report has three data groups. The first data group breaks on an expression of: .T. The second data group breaks on a field in the data: GrpField. The third group is used to print some whitespace every 5 detail records. Its expression is: INT(rnLine / 5). The report also has a variable defined to help count the number of detail lines: Name = rnLine, Value to store = 0, Initial Value = 0, Reset value = GrpField, Calculation = Count.

The problem is that the third data group uses the rnLine report variable which is reset on the second data group. As the second data resets, it alters the value of the report variable, which messes with the data grouping and completely confuses the Report Writer.

The solution is to alter the report variable in a slightly different way so it doesn't confuse the data grouping. Only two slight changes are required, but the changes are enough to make the Report Writer happy. First, change the definition of the report variable to reset at the end of the report instead of the second data group.

The second change is to add the following to the On Entry Expression in the Data Group Header of the second Data Group: _VFP.SetVar('rnLine', rnLine - MOD(rnLine,5)). This change manipulates the report variable by reducing the value back to the last number that triggered a break. It's a sneaky workaround that's pretty effective as shown in the following example that doesn't have the gap:



As with most of the Visual FoxPro Report Writer, a little creative thinking can go a long way!

VFP9 Help File fix

As many of you know, the Help file for VFP 9 is a mess. There's good news on that front!! Instead of repeating what Rick Schummer blogged about, I'm just going to direct you to his blog.

http://rickschummer.com/blog/2009/01/vfp-9-sp2-help-file-fixed-coming-to.html

Thanks to the Southwest Fox Attendees

I want to give a huge thanks to all the people who attended Southwest Fox 2008, and especially those who attended my sessions. The conference organizers have tallied up the evaluations and forwarded all the scores and comments to the speakers. We really do read them and we really do appreciate them. The comments help us know what we're doing right and what we need to improve.

I'm extremely giddy to share the news that you made me the #1 speaker of SWFox this year. My "Customizing Your Vertical Market Application" session ranked #1 amongst 34 session topics. My "Introduction to the VFP 9.0 Report Writer" precon ranked #2 for the morning session and #4 for the afternoon session. I can't believe it .. I had the #1, #2, and #4 sessions of the entire conference. Wow .. did I mention how giddy I am? :-)

The written comments were great. I thank all of you for taking the time to write them. I can't list them all, but here's a sampling of some.

From the Report Writer Precon:

"Cathy did a great job of keeping me engaged and interested."

"Her presentation was easy to follow/understand and had entergy/liveliness so it was interesting."

"Couldn't have been better."

"I finally learned how to use Report Listeners."

"Practical, real world tips and tricks."

"Great white paper content."

From the Customizing a Vertical Market App Session:

"Outstanding idea and implementation that I can apply to my applications."

"The speaker portrayed the concept in a very straightforward and organized way."

"I loved this session. Great ideas! Great presentation. Love the pictures!"

"The session was both fun and informative. I liked how Cathy demonstrated the approach to extending a vertical market app by making fun of the organizers. Even more fun when at least one of them was in the room at the time."

"Made a complex topic, and discussed it in a simplified manner, that allowed a beginner, such as myself, to absorb the key concepts."

"Lively, useful, good examples, entertaining."

"The go-kart thing was very funny."

"Best value session."

"This stuff races." (with a picture of a go-kart next to it)

"Wanted to see if Cathy would change her session after hearing last night that she lost to Doug and Rick. Amazed she did. Does she get extra credit for doing so? In my book, yes!"

"Best session of the entire conference. Absolutely loved the report factory - this will pay for the entire conference."

Again, I want to thank all of you for attending my sessions and taking the time to fill out the evaluations. We've all been to one or more sessions over the years that are full of content, but boring and hard to follow. Sometimes you leave thinking the speaker is really knowledgeable, but you're scratching your head because you didn't understand it and aren't sure how to go back to the office and apply what you just saw. My goal when giving sessions is to share my knowledge in a manner that's fun and easy to absorb. I'm also hopeful that my passion for software development shines through and rubs off on others. Based on the feedback, it looks like I'm right on track (pun intended).

Now that I'm done thanking the attendees .. I have to turn my attention to the conference organizers. Word has it from Tamar that Rick and Doug tried as hard as they could to come up with an angle to skew the rankings so I didn't come out on top. They relished their go-kart win over me and have been having a lot of fun rubbing it in. They knew ... I mean they REALLY knew .. that I would turn around and rub this in their faces. And they're right ... nah nah nah nah!! ;-)

Seriously though .. the ribbing is all in fun. I have a lot of respect for all the speakers at Southwest Fox, even Rick and Doug.

MVP Award

I'm happy to announce that Microsoft has bestowed upon me the Visual FoxPro MVP Award for the 6th year in a row. I always enjoy interacting with the Fox Community and sharing my knowledge whenever possible. Thanks to the entire Fox Community for letting me be a part of you!

For a complete list of other FoxPro MVPs, click here.

Workaround: Dangling Datasession Left After Running Report

In December, 2007, I posted about a bug in VFP 9 that causes a dangling datasession after running a report from form with a private datasession and using report listeners chained together as successors. Microsoft still hasn't fixed the problem, but I now have a workaround for the bug.

First of all ... I have to give credit where credit is due. I didn't come up with the solution. It was given to me by Rick Hawkins ... so thanks Rick!! But now that I have the solution, I want to share it with everyone who follows my blog (all three of you.)

The solution is actually quite simple ... just before you instantiate any report listener object, switch to datasession 1, instantiate the object, then switch back to the previous datasession.

LOCAL lnDataSession, loListener, loListener2

lnDataSession = SET('DataSession')
SET DATASESSION TO 1
loListener = CREATEOBJECT('ReportListener')
loListener.ListenerType = 1
loListener2 = CREATEOBJECT('ReportListener')
loListener.Successor = m.loListener2
SET DATASESSION TO (m.lnDatSession)

REPORT FORM Whatever OBJECT m.loListener


I'd rather Microsoft fix the bug, but I guess a workaround is the next best thing!

How Do I ... Total fields for one detail band only

When multiple detail bands were introduced to Visual FoxPro it was a great addition that was long overdue. However, there's one aspect of that feature that wasn't clearly thought through and that's Calculations and Report Variables.

Let's say you create a report with multiple detail bands. Then you add a data group to the report and in the data group footer you add some fields that are defined to calculate using the SUM option. The problem that can arise is the calculation is applied to every record in both detail bands and maybe you only want to sum the records in the first detail band. Hmmm ... now what?

There's a workaround, but it takes some extra work on your part. Start by defining a Report Variable called rnDetail; Set the Value to Store to 0; Set the Initial Value to 0; Set the Reset option to End of Report; and set the Calculation option to Sum. This creates a Report Variable that the Report Engine doesn't ever change but now you have something you can manipulate.

The next step is to make sure each of the detail bands are marked to include the associated header and footer bands. In the OnEntry Expression of the Detail Header 1 band, enter the expression: _VFP.SetVar('rnDetail', 1). In the OnEntry Expression of the Detail Header 2 band, enter the expression: _VFP.SetVar('rnDetail', 2). These two expressions reset the Report Variable to 1 or 2 at the beginning of the respective detail band so now you have a way to know which detail band you are on.

The next step is to create Report Variables for each of the fields you want to sum. For example, if you want to sum the Qty and Price in the first Detail Band, create a Report Variable called rnQty and set the Value to Store to IIF(rnDetail = 1, Qty, 0). Create another Report Variable called rnPrice and set the Value to Store to IIF(rnDetail = 1, Price, 0). Leave the Calculation option of both variables at the default of None.

The final step is to add the field objects to the data group footer band. Instead of using the name of the field such as Qty or Price, use your new Report Variables of rnQty and rnPrice for the expression. Set the Calculation options to reset at the data group. Now you have some calculations in the data group footer that only contain values from the first data detail band.

This concept can be used in various situations when you need to create calculations or Report Variables that only get processed on one of many detail bands. Once you have the rnDetail Report Variable in place, you can reference it as needed. Just be sure this Report Variable is at the top of the list of Report Variables before any other Report Variables that need to access it. The Report Engine processes the Report Variables in the order they appear in the list.

Call to Action - Install VFP 9 SP2

Rick Schummer just blogged a compelling call to action for everyone to install VFP 9 SP2 and test it. He took the time to write a whitepaper that explains how to install SP2 in a different directory than your VFP 9 RTM (Release to Manufacturing) or VFP 9 SP1 versions. This way you can leave your production version (RTM or SP1) as is, yet still have SP2 on the same machine so you can test your applications.

I can tell you that I had already previously followed his advice and have SP1 and SP2 running on my machine without any problems. I encourage everyone else to do the same. Rick did a great job in explaining why you should do this so I'm just going to point you to his blog which also has the link to his whitepaper.

http://rickschummer.com/blog/2008/03/vfp-9-rtmsp1sp2-one-machine.html

Please take the time to install and test SP2. If we all do this, the entire FoxPro Community, including yourself, will benefit!

How Do I ... Create some complicated data groups and build a recap?

I recently had to create a very complicated report for a client that covers several different techniques. I thought it would be good to share this modified sample with everyone.

At first, the report sounded simple (don't they all!) Create a list of orders, sorted and subtotaled by customer and each customer should start on a new page. That's easy, I can handle that. Put the orders in the detail band. Create a data group on the Customer and mark it to start on a new page. Put subtotals for the customer in the Data Group Footer band.

Oh .. the client says .. the list of orders per customer can get quite long. Can we have ledger-style shading or a line every so many lines? Sure, I think I can handle this. Oh ... the client says again ... we'd also like subtotals at the bottom of each page showing the totals of just that page. That should be easy .. I can use the page footer band. Oh wait .. as I think that through some more, it's not going to work because on the last page for the customer, the data group footer band would print and then the page footer would show up at the bottom. That's not cool. Hmm ... these two requirements combined make things a little sticky .. but I'm sure I can come up with the answer.

Then comes the killer requirement .. Remember those page subtotals we want at the bottom of each page .. well .. at the end of the customer we'd also like a recap that shows each one of those subtotals like the following:

Page 1, $999.99
Page 2, $999.99
Page 3, $999.99

Yuk .. I really don't like that one especially combined with the ledger-style and page subtotals. Now I'm really going to have to put some thought into this report. I think my head is spinning!

For this example, I'm using VFP 9 SP2 (although it should work with previous versions because I'm not using a ReportListener or any of the new features.) I'll use the TasTrade data stored in C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data. I wanted to be flexible with the number of lines per ledger section and the number of lines per page so I decided to control them with a variable. That way, if the customer changes their mind down the road I can easily change it.

I also didn't want to mess around with trying to build the data for the recap ahead of time. Again, I was thinking down the road about using this technique for other situations and if I were to use it on a report with stretchable fields, I didn't want to use hard-coding to "count" each record and figure out the appropriate page it falls on. I just wanted to let it flow and let the report build the recap data for me no matter how many records appeared on a page. I also didn't want to have to hard-code all the variables to be totaled. I wanted some generic code that totals all the numeric or currency fields.

Let's start by looking at the final report as shown below in Figure 1. The first page shows the records for the first customer. The second page shows the "recap" for that customer, which only contains one page. The third and fourth pages do the same thing for the second customer. The fifth through seventh pages show the details for the third customer and the last pages shows the "recap" of each of the three previous pages which belong to the third customer.









Figure 1: The final printed report.

Next, let's look at the simple program that I use to create the data and print the report. Notice that I use two variables to control the groupings. I could have hard-coded this in the report itself, but this is more flexible.

*-- Build the data
SELECT '1' AS RecType,
    Orders.Customer_ID, ;
    Customer.Company_Name, ;
    Orders.Order_Number, ;
    Orders.Order_Date, ;
    CAST(Orders.Discount AS N(14,2)) AS Discount, ;
    CAST(Orders.Freight AS N(14,2)) AS Freight, ;
    CAST(NVL(SumLines.nPrice, 0) AS N(14,2)) nPrice ;
    FROM 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\Orders' ;
  LEFT OUTER JOIN 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\Customer' ;
    ON Customer.Customer_ID = Orders.Customer_ID ;
  LEFT OUTER JOIN ;
      (SELECT OrdItems.Order_ID, ;
        SUM(OrdItems.Unit_Price * OrdItems.Quantity) AS nPrice ;
        FROM 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\OrdItems' ;
        GROUP BY OrdItems.Order_ID ;
      ) SumLines ;
    ON SumLines.Order_ID = Orders.Order_ID ;
  INTO CURSOR tmpReportData READWRITE

*-- Index the data
SELECT tmpReportData
INDEX ON Customer_ID + RecType + Order_Number TAG SortKey
GOTO TOP

*-- Set some variables to control # of lines
*-- for each section and # of lines per page.
nMaxLinesPerLedger = 2
nMaxLinesPerPage = 6

*-- Run the report
REPORT FORM ComplexGroups_Recap TO PRINTER PROMPT PREVIEW

Besides the above program, I also need a function that I'm going to call from the report itself. This function will update the cursor that is driving the report by either adding a new record for a particular page, or adding to the numeric fields for the given page. Note that I have to take great care to move the record pointer back to where it was when this function was called. Otherwise, you'll make a mess of the running report. Add the following function to the bottom of the program that runs the report so it's available to the report.

************************
FUNCTION AddToPageTotals
************************
LPARAMETERS tcField, txPage, tcSeek, tcIndex

LOCAL loRecord, lnRecNo, lcAlias, lcSeek, lx

*-- Don't do this on the total records themselves!
*-- (Can you say "infinite loop")
IF RecType = '2'
  RETURN
ENDIF

*-- Remember where we are
lnRecNo = RECNO()
lcAlias = ALIAS()

SCATTER NAME m.loRecord
m.loRecord.RecType = '2'
m.loRecord.&tcField = txPage

lcSeek = EVALUATE(STRTRAN(m.tcSeek, 'XTMPX', 'm.loRecord'))
IF SEEK(m.lcSeek, m.lcAlias, m.tcIndex)
  *-- add the amount fields to the existing record
  lnFields = AFIELDS(laFields)
  FOR ln = 1 TO lnFields
    lcField = laFields[m.ln, 1]
    lx = EVALUATE(m.lcField)
    IF VARTYPE(lx) $ 'NY'
      REPLACE (m.lcField) WITH NVL(EVALUATE(m.lcField),0) + NVL(m.loRecord.&lcField,0)
    ENDIF
  ENDFOR
ELSE
  *-- Add the record for this page
  INSERT INTO (m.lcAlias) FROM NAME m.loRecord
ENDIF

*-- Go back to the "detail" record that we were processing
GOTO m.lnRecNo

Now let's get to the definition of the report, which is the meat of this solution. Figure 2 shows how the report definition will look when we're done.


Figure 2: The report definition.

Start by creating 3 report variables:

• rnLinesOnPage


• Value to Store = 0
• Reset = Report
• Calculation Type = Count


• rnLinesForCustomer


• Value to Store = 0
• Reset = Report
• Calculation Type = Count


• Dummy


• Value to Store = AddToPageTotals('Order_Number', TRANSFORM(_PageNo, '999999'), 'XTMPX.Customer_ID + XTMPX.RecType + XTMPX.Order_Number', 'SortKey')
• Reset = Report
• Calculation Type = None

At first thought, it might look like I made a typo above because none of the variables reset at the Customer group or Page group. But trust me .. it's not a mistake. Just bear with me and it will make sense soon.

The last variable is used to call the function we created earlier. It looks a little complex but that's just because I made the function generic so I can reuse it on other reports. You could simplify it by taking away all the parameters and hard-coding the function.

Next, create 4 different Data Groups:

• 1: Customer_ID, New Page Number 1, Reprint group header on each page


• 2: RecType, New Page


• 3: INT(rnLinesForCustomer / nMaxLinesPerPage), New Page


• 4: INT(rnLinesOnPage / nMaxLinesPerLedger)

Expand the #1 Data Group Header band and put the Customer field.

Put the detail fields in the Detail band and lay them out as you desire.

Expand the #4 Data Group Header band and add a horizontal line to separate the chunks of detail records. I made my light gray so it's not so distracting.

Expand the Data Group Footer bands for #3 and #4 so you can add some totals. Copy your numeric fields from the Detail band into the two different group footer bands. Next, change the Calculate option on each to SUM and reset on the applicable group.

Now go ahead and add whatever column headings you want. You can use Print When logic of RecType = '1' if you want to print something for just the regular data. Use RecType = '2' if you want to print something on just the page recaps.

At this point you might think we're done, but we're not. Remember when we defined the report variables I mentioned that we are not resetting them at the appropriate data groups. The reason is that whenever you try to alter the variable that is being used to control the groups, the VFP Report Writer gets completely confused. You end up with more breaks than you want and often times you get pages with no detail records and just some totals.

So here's the trick behind this whole report. We need to reset the report variables ourselves. Well, actually, we aren't resetting them, we're fudging them. The problem is that if we reset them to zero, we get the extra breaks I just mentioned. So instead of resetting them to zero, we fudge them to a value that makes the next page start at a clean point.

We are going to alter these variables in the On Entry and On Exit Expressions of the #2 Group Footer band as follows:

On Entry: _VFP.SetVar('rnLinesForCustomer', rnLinesForCustomer- MOD(rnLinesForCustomer, nMaxLinesPerPage))

The above "expression" resets the rnLinesForCustomer variable to a value that is equal to what the count would be if the page was completely full of lines, without resetting it for each page. So if we allowed 10 lines per page, at the end of the first page we reset it to 10, at the end of the 2nd page we reset it to 20, and so on. Regardless of how many lines actually printed, we just make it think that full pages have been printed. By doing this, we avoid the funky extra breaks that happen when you try to zero out the variable.

On Exit: _VFP.SetVar('rnLinesOnPage', rnLinesOnPage- MOD(rnLinesOnPage, nMaxLinesPerLedger))

The above "expression" does the same thing for the ledger breaking as the previous one did for the page breaking. It really doesn't matter which one is in the On Exit and which one is in the On Entry. I just needed to reset two variables and this gave me a place to do each one.

That's it. We're done. Simple, right? Well .. of course it's simple when you already know the answer. Trust me .. it wasn't that simple for me to come to this conclusion, but now you can benefit from my troubles. If you want the sample program and report I used to create this blog, just send me an email and I'll gladly send it to you.

New VFP Magazine Announced!

Rather than retype what Rick Schummer did ... here's a link to his blog describing the new magazine by Rainer Becker:

http://rickschummer.com/blog/2007/12/fox-rocks-with-foxrockx.html