Collapse AllExpand All

5. Counter Fields

The last change we will make to the UsersMasterList report definition in this chapter is to add a total count of all displayed users. This total will complement the running total of active users, which we have already added, since the UsersMasterList report may display both active and inactive users.

To provide this total information, we will add a "COUNTER" variable to the detail query source. The "COUNTER" variable will increment by 1 for every row returned by a query. For example, if a column contains 10 rows of data, the "COUNTER" will total "10" after the query has been run.

To add the "COUNTER", we open the detail query source by double-clicking on it from the list of available query sources. Next, we add the "COUNTER" to the query’s SELECT statement, as shown in the following screen:

Counter added to query source

As you can see, the "COUNTER" syntax is highlighted at the end of the SELECT statement. The "1 AS COUNTER" syntax indicates two things: 1) For every row of data retrieved from the database, that row will be assigned a value of "1", and 2) the result set of retrieved rows will be stored in memory in a temporary column we have called COUNTER. Notice we have been careful to add a comma after the reference to the locale_code column. The comma separates the "COUNTER" from the other items in the SELECT statement series. We do not insert a comma after the "COUNTER" line.

Now that we have inserted the "COUNTER" into the query source, we are ready to add a field object to display the results.

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:

Counter field 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 counter field

As you can see, we have filled in the following properties for the "COUNTER" 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 temporary COUNTER column.

Column

Here we enter the name of the temporary COUNTER 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 all the records written to the COUNTER column to be displayed as a running total. When the running total is calculated, each record (i.e., displayed user) will be assigned a value of "1".

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 COUNTER: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:

Counter field added to Report Footer section

We have added the field object which will retrieve data from the COUNTER 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 "COUNTER:detail" field, as shown in the following screen:

Label object added next to Counter 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:

Counter Label Properties

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

Label object identifying counter

We have now added a "COUNTER" 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:

Counter appearing on printed report

We have now reached the end of the exercises contained in the this chapter. Over the course of this chapter we have taken a hands-on approach to learning fundamental report writer functionality. You should now understand how the report writer retrieves information from a database and displays that information in printed form. More advanced functionality will be explored in subsequent chapters.