Collapse AllExpand All

4. Total Fields

We have seen how field objects may be used to retrieve text (i.e., user email addresses) from a database. In this section we will show how to create running totals using field objects.

Our goal for this exercise is to provide a running total of all active users. There are currently three active users in the database. We will add a fourth user and make that user inactive, as shown in the following screen:

Master list of xTuple users

If we are successful, the field object should ignore the inactive user and include only the three active users in the running total.

To begin, we will create a report footer section in the report definition. As you may recall, report footers print only on the last page of a report—which is exactly what we want. We want the running total to print at the end of the report.

Note

For more information on report footers, see the Report Footers section in the Report Writer Basics chapter.

To create a report footer section, we select the Section Editor option from the Document menu. The following screen will appear:

Add report footer using Section Editor

As you can see, we have selected the Report Footer option. Selecting this option causes a report footer to be added to the report definition, as shown in the following screen:

Report footer section added to report definition

Now that we have added the Report Footer section, our next step is to add a field object to the section. The field object will display the running total of all active users.

To add a field object to the report definition, we select the Field icon from the toolbar and then click in the Report Footer section. We place the field object in the section, as shown in the following screen:

Field object added to Report Footer section

Now that we have placed the field object, we must define its properties. Double-clicking on the field object opens the Field Properties screen:

Properties for running total field

As you can see, we have filled in the following properties for the running total field object:

Query Source

We selected the detail query from the list of available query sources. This is the query whose SELECT statement includes the reference to the usr_active column.

Column

Here we enter the name of the usr_active column—since this is the column whose data we need for the field object. A column must be referenced in the SELECT statement of the associated query source to successfully retrieve data from the database.

Display as Running Total

By selecting this option, we indicate we want the records on active users to be displayed as a running total. When the running total is calculated, active users will be assigned a value of "1", while inactive users will be assigned a value of "0".

Note

When running totals are calculated for columns designated as Boolean, the true values are assigned a value of "1", while false values are assigned a value of "0". In this example, active users would be assigned a value of "1", while inactive users would be assigned a value of "0".

Built-in Locale Format

We specify we want the running total to be displayed as a quantity.

Preview

The preview shows us both the name assigned to the field object usr_active:detail and also the font choice. To make the font consistent with the other field objects in the report definition, we select the FONT button and specify 8-pt bold Arial.

After we select the OK button, we see the field object has been updated in the report definition, as shown below:

Running total field added

We have added the field object which will retrieve data from the usr_active column and display the information as a running total. Next, we need to insert a label object to appropriately identify the field.

To add the new label, we select the Label icon from the toolbar and then click in the Report Footer section. We place the label object to the left of the usr_active:detail field, as shown in the following screen:

Label object added next to running total field

Now that we have placed the label object, we must define its properties. Double-clicking on the label object opens the Label Properties screen:

Running total label properties

As you can see, we have entered "Total Active Users:" in the Text field. This text identifies the running total, as shown in the following screen:

Label object identifying running total

We have now added the running total to the report definition—and labeled it appropriately. Once we save the report definition changes to the database, we will be ready to run the report and see the results.

To run the Users master list report from xTuple, select the Maintain Users option from the System module menu. After selecting the PRINT button, the following report is generated:

Running total appearing on printed report

If we scrutinize the example closely, we see the appearance of the report would benefit if we added a horizontal line separating the usernames from the running total. In the next section, we will add a separator line.