The Calculation Builder

In the Report Viewer, the Report Editor, and the Report Definition rule form, you can use the Calculation Builder to select an SQL function, a literal value, or a column name. You can build nested expressions in the Calculation Builder by using an SQL function as the parameter value for another SQL function.

Use the Calculation Builder when you want your report to show values that are not available as properties. For example, if you have a date property but you want your report to show the day of the week, use the "day of the week" function.

A large number of standard SQL function templates are provided. If these standard rules do not meet the needs of your report, you can create a Function Alias rule. For more information, see About Function Alias rules.

Operation

You open the Calculation Builder by clicking the Calculation Builder icon.

When the Calculation Builder opens, it displays a single field where you can select an SQL function to use. Type the first few characters of the function you want, or use the Down Arrow key to display a list of available functions. Standard functions are listed first, followed by custom functions that were created by application developers.

When you select a function, a description of the function and its use is displayed, along with prompts for any parameter values that are required.

Note: When entering parameter values for a function, you must specify a literal value, a property, or an SQL function of the correct data type.

If you use a function that compares date values, you can compare a date column by using one of the following:

  • A date property.
  • A symbolic date value such as yesterday. You must click Select Value rather than using the SmartPrompt to enter a symbolic date.
  • A literal date value, such as 01/01/2013. Use the date format for your locale.
Note: While expressions using SQL functions can be nested, application performance might degrade if you use highly-nested SQL functions. Consider creating custom SQL functions that contain highly complex logic.

Also, if you have an SQL function that includes CASE WHEN logic, and you want drill-down in charts to work properly, define your SQL function to return a NULL value instead of zero when a condition fails.

Customizing the function list

The SQL functions shown in the Data Explorer and listed when you click the Calculation Builder icon change if a data transform named pyReportEditorFunctions is available for the primary class of the report. This data transform rule generates a Code-Pega-List, which specifies a list of SQL functions to be shown, using the function names. The system populates labels and data types at run time. If pyReportEditorFunctions returns a null list, the Calculations tab does not appear in the Calculation Builder for the Report Editor. Developers always see the full list of functions when working directly with the Report Definition rule form.

To provide a customized list for a report:

  1. Identify the class in which the report is or will be.
  2. In that class, create a new data transform named pyReportEditorFunctions .
  3. In the data transform, on the Pages & Classes tab, include these pages with their respective classes:
    • pgAliasFunctions – Code-Pega-List
    • ResultsPage – Rule-Alias-Function

    You can use other names for the pages, as long as you use the same names on the Definition tab. You must use the indicated classes.

  4. In the Action column of the Definition tab, select Update Page and set the target as the page assigned in step 3 for Rule-Alias-Function.
  5. Expand the Update Page action. Set the target as .pyRuleName , and set it equal to a defined alias function (such as pxLength ).
  6. Right-click on the action value Update Page and select the Add Sibling Below option.
  7. In the newly-generated action value, select Update Page. Select the page assigned to Code-Pega-List in step 3 as the target page.
  8. Expand the action. Select Append to. In the target, select pxResults .
  9. In the relation field, select An existing page . In the source column, select the page associated with Rule-Alias-Function in step 3.

This process creates a single function in the Calculation Builder. Repeat as needed to assemble the full list of functions to display.

Limitations for reporting on unoptimized properties

Whenever possible, optimize the properties you want to use in your reports. Reporting on unoptimized properties causes a performance drain on the system. If you create reports using unoptimized properties, you will see warning messages in the Report Editor or the Report Definition rule form.

Unoptimized decimal and double properties on Microsoft SQL Server

On SQL Server, there is a limitation to the size of the value that a report definition rule can report on using the Calculation Builder to get values out of the BLOB (that is, from an unoptimized property). If a value is greater than precision 18, scale 6, SQL server returns an error.