|
|
Report DefinitionsPurpose
The Calculation Builder appears in a pop-up window and lets you select an existing SQL function template to use as the basis for the SQL function you wish to use. When you have selected the template, the form displays further fields where you select the data the function is to use.
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
- To display the Calculation Builder, on the Design tab of a Report Definition form click the Calculation Builder icon (
) to the right of the column in which you want to display the results of a SQL function. - When the Calculation Builder appears, it displays a single field. 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 developers in your team have added.
- When you select a function, a template appears with further fields that allow you to identify data columns and the way you want the function to process them.
If you use a function that compares date values, you can compare a date data property
- with another date property;
- with a symbolic date value such as "yesterday" (you cannot use SmartPrompt to provide the symbolic date); or
- with an actual date value, such as "01/01/2010". Use the date format for your locale.
- Click Submit to apply the function to the report column. Click Cancel to abandon the operation and close the Calculation Builder.
Note that you can specify another SQL function as a property for a value in a SQL function. The Calculation Builder provides a fields and SmartPrompt assistance wherever this is possible. Note, however, that application performance may degrade if you specify SQL functions within SQL functions beyond five depth layers. If you need a more complex function than five layers of nesting can support, create a custom function to provide the SQL statement your report needs, and reference that custom function directly.
Note that, if you have a calculation involving CASE WHEN logic, and you want drill down to work properly in a report using the calculation, define your calculation to return a NULL value instead of 0 when a condition fails.