Tutorial - Lesson 15

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


A common addition to many reports is printing total fields or the number of matching database entries for a group of information. This information is normally placed within a Group Footer report band, so it prints at the end of that related group of database information.


For this example let's assume that we want to print the total number of database record(s) for each state. This information will print at the last page of that group of information as a summary. Here's how to do it:


Create a Sorting Band on the STATE database field (if one doesn't already exist)


Create a Group Footer report band (if one doesn't already exist) on the STATE database field


Add a new frame inside the new Group Footer report band


Click on the Dictionary button for the new Frame


Underneath the 'User Variables' section of the dictionary will be an entry labeled Gather Totals. Each of the Group Header / Group Footer report bands that you have defined will have an entry here titled 'Group Totals'. Highlight the entry for the STATE database field:



Group Totals for State


Click the '+' button at the top of the Dictionary dialogue window: RBPlusButton


A new dialogue box appears to create a new variable based on the selected database field:


Adding a new User Variable in Report Builder


As displayed above, there are many different ways of manipulating the information to be stored in the new variable. Here is a breakdown of each item:


Assign Name: Enter the desired name of the variable here. This name must be unique; do not use the same name as the database field! An easy way of keeping the name unique is by using a prefix, such as LOC; for example, for the database field Res:STATE a good variable name would be LOC:STATE


Field/expression to total:  Use the Dictionary button to help select the database field(s) that you want to manipulate here.


Picture: Define how the variable will be printed on your report by using the Picture dialogue box. (Make it a 3-place numeric: @n3 )


Total Type: Define how the selected database field(s) will be manipulated. From the drop list you can select Sum, Average, Count, Minimum, Maximum or Evaluate. Select Count for this lesson.


Evaluate On: Define when the variable will be modified; from the drop list you can select 'At Each Record Retrieve', 'At The End Of The Group', 'At The End Of The Report' or 'At The End Of Every Page'. Select At Each Record Retrieve.


Here is the dialogue box already filled out to build the new variable:



New Gather Total variable, Loc:State


Here is how the dictionary appears after adding the new variable:



Dictionary with new Loc:State variable


And here is the modified Group Footer report band to make use of the new variable information:



Group Footer frame with State total counter


Notice we preceded the use of the variable with text and the variable for the state name (Res:State).  Here's an example of what the output will look like:



Sample report with State counter at end


To recap; in this lesson you learned:


How to create a variable used in a Group Header / Group Footer band


How to manipulate the characteristics of this new variable (appearance, how information is gathered from the database)


How to combine the Group Header / Group Footer variable with text for printing meaningful information in the report band