Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

The Visual FoxPro 9 Report Writer : Page 6

Microsoft has significantly improved the Report Writer in Visual FoxPro 9, while maintaining compatibility with previous versions of Visual FoxPro Reports, making the new Report Writer a great blend of the old and the new.


advertisement
 
Figure 16. Print Group Subtotals: Use a Multiple-Detail report to print group subtotals at the beginning of the group.
Tricks with Multiple-Detail Bands
The previous section showed how to print data from three different child tables on the same report. But a multiple detail band report doesn't necessarily have to have multiple child tables. The same child table can be used in more than one detail band. Group Totals
Prior to Visual FoxPro 9, printing subtotals in the Data Group Header band (shown in Figure 16) was very difficult. The data had to be preprocessed to calculate the totals prior to running the report. With Visual FoxPro 9, no preprocessing is required. Follow these steps to create this report:

  1. Create the Data Environment: Add the Customer and Vehicles tables. Set the InitialSelectedAlias property to the Customer table.
  2. Create a Data Group: Set the Data Group expression to the Customer PK. Do not place any objects in the Data Group Header or the Data Group Footer bands.
  3. Create the multiple detail bands: Select "Optional Bands..." from the Report menu. Click the Add button to add one more detail band to the report. Click OK to exit the dialog box.
  4. Define the Detail #1 band: Double-click the gray bar of the Detail #1 band to invoke the Properties dialog box. Set the target alias to "Vehicles", remembering to use the quotes. Check the Associated header and footer bands checkbox. Do not place any objects in the Detail 1 band.
  5. Define the Detail #1 Footer band: Add the Customer Name and Address objects to the band. Add the total vehicles and total premiums Label objects to the band. Add a Field object for the total vehicles; set the expression to vehicles.premium, set the Calculation type to Count, and set the Reset based on to Detail1. Add a Field object for the total premiums; set the expression to vehicles.premium, set the Calculation type to Sum, and set the Reset based on to Detail1.
  6. Define the Detail #2 band: Double-click the gray bar of the Detail 2 band to invoke the Properties dialog box. Set the target alias to "Vehicles", remembering to use the quotes. Check the Associated header and footer bands checkbox. Add any other objects needed in Detail 2 band.
 
Figure 17. Printing Percentages with Details: Use a Multiple-Detail report to print percentages along with each detail line.
The above report definition tells the Visual FoxPro 9 Report Writer to process the Vehicles table twice for each customer in the Customer table. The first time, it calculates the total records and dollar amount for the customer and then prints them. The second pass through the Vehicles table prints the details. This process repeats for each customer in the Customer table. Percentages
Another reporting concept is to show percentages of totals of each detail line, as the detail line prints. This can also be handled with Multiple-Detail bands, as shown in Figure 17. Follow these steps to create this report:

  1. Define the Data Environment: Add the Customer and Vehicles tables. Set the InitialSelectedAlias property to the Customer.
  2. Create a Data Group: Set the Data Group expression to the Customer PK. Add the Customer Name and Address objects to the Data Group Header band.
  3. Create the multiple detail bands: Select "Optional Bands..." from the Report menu. Click the Add button to add one more detail band to the report.
  4. Define the Detail #1 band: Double-click the gray bar of the Detail #1 band to invoke the Properties dialog box. Set the target alias to "Vehicles", remembering to use the quotes. Check the Associated header and footer bands checkbox. Do not place any objects in the Detail 1 band.
  5. Create some report variables: Create a variable named rnTotalPremium, set the Value to store to Vehicles.premium, set Calculation type to sum, and set the Reset based on to Detail 1. Create a variable named rnPercent, set the Value to store to ROUND(100 * vehicles.premium / rnTotalPremium, 2), and set the Calculation type to None.
  6. Define the Detail #2 band: Double-click the gray bar of the Detail 2 band to invoke the Properties dialog box. Set the target alias to "Vehicles", remembering to use the quotes. Check the "Associated header and footer bands" checkbox. Add any objects needed in the Detail 2 band. Add the Percent object to the Detail 2 band with an expression of rnPercent.
  7. Define the Detail #2 Footer band: Add the total premium object, set the expression to vehicles.premium, set the Calculation type to Sum, and set the Reset based on to Detail 2. Add the total percent object, set the expression to rnPercent, set the Calculation type to Sum, and set the Reset based on to Detail 2.
The above report definition tells the Visual FoxPro 9 Report Writer to process the Vehicles table twice for each customer in the Customer table. The first time totals the premium so it can be used in the second pass. The second pass through the Vehicles table prints the data for the customer, using the report variable that was calculated during the first detail band. This process repeats for each customer in the Customer table. A Lot to Learn
The Visual FoxPro 9 Report Writer has many new features to help you create better reports. The new reusable data environments allow you to share data environments among reports. Various aspects of reports, such as objects and bands, can be protected. A better development experience has been created through the new user interface. The new object layout enhancements and data group enhancements offer more options than previously available. And finally, the new multiple-detail band feature opens up many more reporting options. Together, all these improvements and enhancements give you the ability to create more complex reports than ever before.


Cathy Pountney is a Microsoft Visual FoxPro MVP and has been developing software for over 22 years, thirteen of which as an independent consultant specializing in FoxPro. In 2001 she had the privilege of spending six months as a contractor onsite in Redmond with the Microsoft Fox Team. Currently she works for Optimal Solutions developing Visual FoxPro applications for schools. Cathy has spoken at many FoxPro conferences and user groups across the U.S., written articles for various magazines, and her book, "The Visual FoxPro Report Writer: Pushing it to the Limit and Beyond," is available from Hentzenwerke Publishing.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date