Report Definitions Using the Calculation Builder

The Calculation Builder can be invoked in several places in the Report Viewer, Report Editor and Report Definition rule form -- wherever you can selecta SQL function as well as a literal value or column name. You can build nested expressions in the Calculation Builder, using a SQL function as the parameter value for another SQL function.

PRPC provides a large number of standard SQL function templates: see Atlas — Standard SQL function rules for a descriptive list. If none of the standard rules fit the needs of your report, you can create your own Function Alias rule. See About Function Alias rules.

Operation

The Calculation Builder icon appears wherever it is available. Click the icon to open the form.

When the Calculation Builder appears, it displays a single field where you can select a SQL function to use. Type the first few characters of the function you want, or use the down-arrow to display a list of available functions. Standard functions appear first, followed by any custom functions application developers have added.

When you select a function, a description of the function and its use appears, together with prompts for any parameter values required. Note: When entering parameter values for any function, you must be careful to specify a literal value, property, or SQL function of the correct data type.

If you use a function that compares date values, you can compare a date column

• With a date property;
• With a symbolic date value such as yesterday (you must click the Select Value button and not use SmartPrompt to enter a symbolic date); or
• With a literal date value, such as 01/01/2013. Use the date format for your locale.

Click Apply Changes to finish defining the function, or Cancel to close the Calculation Builder without defining a function.

Note: While expressions using SQL functions can be nesterd, application performance may degrade if you use highly-nested SQL functions. Consider creating custom SQL functions that contain highly complex logic.

Also, if you have a 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 0 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 is 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 runtime. If pyReportEditorFunctions returns a null list, the Calculations tab does not appear in the Report Editor's Calculation Builder. Developers always see the full list of functions when working directly with the Report Definition.

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 and 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 itself.

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 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.

About Report Definitions