Summary

V6 SP2 provides an extensive array of SQL functions for use in report definition rules. However, users who cannot find a function that suits their project needs can create custom SQL functions and make them available in the functions list that appears when a manager is updating a report in the Report Browser.

In this example, the manager wants a SQL function that will assign each work object to a category depending on the work object's current priority or urgency. The manager will use the grouped data in a trend report to show how many work objects are in each category at various points on a time line. The manager wants to use these categories:

• 0-25
• 26-50
• 51-75
• 76-90
• 91-100

Suggested Approach

To create a new SQL function, create a Function Alias rule:

1. In the Rules Explorer, select Technical and right-click on Function Alias. Select the New option.
2. In the New rule form, select or enter Embed-UserFunction in the Applies To field. Specifying any other class creates a Java function alias rule. Provide the RuleSet and Version where you intend to use the SQL function. For Alias Type, accept the default value of Other. This field applies to Java function alias rules. Click Create to create the rule and display the rule form:
   
3. On the Reference tab, provide any input parameters the function requires, one per line in the Input Parameters section. In this example the function needs the current urgency or priority of the work object: give the parameter a name and description, set the Data Type to Decimal, and set the Prompt Type to Autocomplete:
   
4. In the Presentation section, set the Return Type and Category as Text, and provide a Pattern Template. The pattern template is a structured description of what the function does with the input parameters it receives, and appears as a help when the manager is choosing which function alias to use in a report definition report. The parameters are identified by number in the order that they appear in the Input Parameters section.
   
   Provide an Echo Template, which identifies the function in a report definition rule that uses it. For this function, a special Output Format is not required.
   
5. In the Source section, provide the SQL fragment for the function:
   
6. Save the rule. This automatically populates the fields on the Generated Output tab.

When a manager uses the Report Viewer to modify a report, the custom SQL function alias appears as an option in the SQL Function Builder:

Additional information

Using the Report Viewer to define and evolve simple reports

Report Definition rules - Beyond the basics