Tutorial - Lesson 17

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! Mail us feedback on this topic!  


The topics covered in lessons 15 and 16 in the Tutorial can also help you print a Grand Total on the last page of your report; it's just a matter of changing some settings for the variable.


Add a new Report Band of the type Report Footer (click on Band, then New, then Report Footer)


Create a new frame inside this new report band; click on the Dictionary button


Inside the Dictionary dialogue window left click the Grand Totals item; this is located under the Gather Totals section of the User Variables section.


Click the '+' button to add a new variable


Assign a unique name for this new variable; select the database field containing the dollar amount that you want added together. Change the picture length to a longer length than what the selected database field is already set to, and add the '$' character to the string. The "Total Type" will be "sum", and it will be evaluated 'At each record retrieve':


Adding a Grand Total variable


After clicking the Save button the dictionary screen is updated:


Updated Dictionary with Grand Total variable


Sample output:


Sample report with Grand Total variable


If you want to put forth the effort, you could define variables for each of the possible states that the database can have (50 states), then modify the Report Footer detail band to print a state by state breakout of sales. To perform this neat trick you would:


Create a variable in the Dictionary for all 50 state abbreviation codes in the Grand Totals section of the dictionary. The variable would make use of 3 internal functions of Report Builder: Clip(), Upper() and Choose(). The first two functions are used to convert the State database field into a consistent format for testing with the Choose() function. The Choose() function is used to compare the current database record against the known state abbreviation code; if it matches the database field containing the total sales for this customer living in the state is added to the variable (to keep a running total); otherwise it is skipped.


In the Report Footer report band a new frame is added. The Dictionary button is clicked with the empty frame being highlighted. Under the Grand Totals section of the User Variables a new variable is created by clicking the '+' button.


For this sample report a new variable is being created for state abbreviation code "FL" (all respondents living in Florida). Here is what the variable information looks like:


Adding new State abbreviation variable


The Save button is clicked, which inserts the new variable into the frame:


New variable in the Footer band


Another trick being used is using the Center attribute for the frame contents for both the text string "Florida" and the Loc:Florida variable. By using the border attributes, a table effect is achieved, which makes the summary page look very attractive.


The pattern of adding a new frame for the state abbreviation code, and another frame for a new variable for that state abbreviation code is repeated. You can elect to stack them any way you desire; the choice is yours! Here is how the report band looks at the end:



Report band with table effect


As shown above, four different states are being tracked, with the total dollar sales printed below. Here is the sample output:


Sample output with totals in Footer band


To recap; in this lesson, you learned how to:


Add a Report Band


Create a new Frame                


This completes the Report Builder series of Tutorials.  Most procedures you will use to design and run reports have been covered in these 17 lessons. If you still have questions about the proper use of the Report Builder tools, please contact ComQuest Technical Support.