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:

 

RBDetail21

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:

 

RBDetail22

New Gather Total variable, Loc:State

 

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

 

RBDetail23

Dictionary with new Loc:State variable

 

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

 

RBDetail24

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:

 

RBReport7

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