OpenRPT Product Guide

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:

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.