How can I add advanced calculations in my report builder reports using SQL or RAP?

Answer: ExpressMaintenance includes the top rated reporting tool, Report Builder.  Using SQL within Report Builder, users can produce comprehensive reports presenting data in just about any desired format.   Reports often need to include calculated fields as well as advanced calculations based on logical conditions.

Using Report Builder For Simple Calculations

Several standard detail row (record) calculations are readily available in the Design tab of Report Builder.  These include count, sum, minimum, maximum and average.  You can easily add a calculated field within your report detail, group or footer bands.  See the report builder help for more details.  Sub reports are also used to present totals and aggregate fields.

Using SQL For Calculations

Because a report can consist of multiple data queries, SQL can be used to generate calculated fields as well as aggregate calculations from your data.  SQL includes support for aggregate queries such sum, count, average, minimum, maximum, etc.  In addition, SQL allows you to use logical operators with “where” statements.  To get more information on the SQL syntax and commands, see the SQL Learning More Tech Bulletin – Click Here.

Using RAP For Advanced Calculations

There are times when users desire to produce reports with certain logical events and variables.  For example, suppose you want to list all work scheduled for a range of dates but would like separate counts of the open and completed work orders in the summary band.   This would require the use of certain “if .. then” logic in the calculation (see sample below).  ReportBuilder includes a powerful but advanced language for this purpose.

Report Application Pascal, RAP allows users to create their own calculations within reports.   RAP extends Report Builder by giving end users the ability to code their own event handlers and extended calculations. The Calc workspace is an Object Pascal development environment that is designed for ease of use by non-developers. The Code Explorer offers varied views of the report code module; the Code Editor is a syntax-sensitive Pascal editor; the Code Toolbox serves double duty, providing both a partial list of supported identifiers as well as a drag & drop code creation facility.

To utilize the RAP capabilities of Report Builder, you must:

  1. Download and install the very latest release of ExpressMaintenance.  Rap was made available starting in version 5.5.1.27.
  2. Turn on the feature under Utilities / Set Application Properties / Enable RAP Interface.
  3. View the ExpressMaintenance Application Help including the Report Builder / RAP Interface topic for complete details on the RAP aspect of Report Builder.
  4. Download the pdf documentation – What is RAP.
  5. Download the pdf documentation – Learning RAP.
  6. Consider downloading and working through the Report Builder learning tool – click here.

Note: Express Technology does not currently support end users with this new tool.  RAP is a powerful extension to Report Builder that we are pleased to make available to our customers.  However, we cannot provide technical support on RAP at this time.

For a more advanced example report using RAP, please download the ‘PMs Services Analysis Report’ and accompanying pdf file from our reports download page – click here.

Example Report

Below is an example report that uses two variable fields to perform the calculations desired.  In this example, we are listing all work orders scheduled between a range of dates.  However, at the end of the report in the summary band, we want separate counts of the open and completed work orders.  You can download and import the report used in this example (Work Orders List By Category) at the Reports Download Page – Click Here.

1. Design Tab

Notice that two calc Variable fields Variable1 and Variable2 have been placed in the Summary band of the report:

 

2. Calc Tab

In the Calc tab, code is placed on the first variable to perform the if .. then..else logic.  If the Work Order has a completed date of 0 (null), the value of Variable1 is increased by 1, else the value of Variable2 is increased by 1 indicating the work order is completed.

3. Preview Tab

When previewing the report, you can see that the variables have been calculated and placed in the summary band of the report.

 

Additional General Information

The following is additional overview information about RAP taken from the Digital Metaphors website.

The RAP user interface can be configured to meet the needs of your end users. There are three basic configurations available:

1. Calculations Dialog

For casual end users, a simple Calculations dialog is accessible from the speedmenu of the new TppVariable component. This dialog provides a place for calculations to be entered and returned via a single Result parameter. The calculation result is then displayed in the variable when the report prints.

In order to configure the user-interface to provide this level of functionality, set the RAPInterface riNotebookTab property to False and the riDialog property to True. The figure below illustrates the Calculations… option available when the end user accesses the speed menu of a variable:

VariableSpeedMenu.GIF (12290 bytes)

When this option is selected, the Calculation dialog is displayed. The end user can then create code to assign the value of the variable.

Variable - Calc Dialog

2. Calc Tab with Variables

For more sophisticated users, RAP can be configured to display as an additional tab in the Report Designer.  The new ‘Calc’ tab shows the bands of the report in a tree view, along with the associated variables.  The end user can click on the variables and code the calculation in a syntax-sensitive edit window.

In order to configure the user-interface to provide this level of functionality, set the RAPInterface riNotebookTab property to True.  When the report designer is displayed, the Calc tab will be visible, and the variables within each band will be listed.

CalcEnd2.GIF (12375 bytes)

 

3. Calc Tab with All Objects

The highest level of functionality is provided by the ‘Event’ view. This capability shows all of the report components in a tree view. When a component is selected, all of the events for the component are displayed. The user can then select an event and code an event handler.

CalcEnd3.GIF (14320 bytes)

Calc Tab Views

In order to configure the user-interface to provide this level of functionality, set the RAPInterface riNotebookTab property to True and set all of the RAPOptions to True. When the report designer is displayed, the Calc tab will be visible. By right-clicking on the treeview on the left, a speed menu will be displayed that shows the different views of calculations available:

a. Variables

This view shows the variables in each band of the report.

VariableVariableView.GIF (11269 bytes)

b. Events

This view shows the report and all of the objects within it. An event handler can be coded for any object.

CalcVariables.GIF (14126 bytes)

c. Module

This view shows the global declarations, functions, procedures, and events.

CalcCModule.GIF (10809 bytes)

  1. Download the pdf documentation – What is RAP.
  2. Download the pdf documentation – Learning RAP.
  3. For a more advanced example report using RAP, please download the ‘PMs Services Analysis Report’ and accompanying pdf file from our reports download page – click here.