In a Report Definition report, you can populate a column of the report using an SQL function. For example, assume you want a report that shows how many work items were resolved in each quarter of the year. You can set the value of a column in the report to the result of the function pxQuarter, which returns the first day of the quarter in which the date value you provide falls:

You can then group the resolved work items in the report by the value in the "pxQuarter" column, to show for each quarter how many were resolved.

For complex manipulation of report data, you can go further and also "nest", or embed, a second SQL function that provides the value that your main SQL function operates on.

Managers who do not have developer privileges cannot work directly with the Report Definition rule form. However, they can create reports using nested SQL functions in the Report Browser that they access through their manager portal.

Note that application performance may degrade if you nest SQL functions within SQL functions beyond five levels. 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.

Suggested Approach

Here is an example of developing a report that uses a nested SQL function to populate a column.

Requirement: you, as a manager, need to provide a report that shows all open work items from the current year, and for each, how long is has been open.

You can tackle this job in the following steps:

1. In the Report Browser, create a new report.
2. Adjust the filters in the report so it covers the current year.
3. Use the Function Builder to populate a column showing the number of days each work item has been open. This step involves nesting a SQL function to provide the current date, so you can compare it with the work item's creation date.
4. Remove the columns you don't need and review the report.

Prerequisites

To perform the steps in this example, you need:

• Access to the Report Browser
• An unlocked RuleSet version in your access group's Production RuleSets list. This RuleSet Version is where the system saves the the Report Definition rule you create when you create a new report in the Report Browser.

1. Create a new report

1. Open the Report Browser and click the New Report button.
2. In the form that appears, specify the class of the work items on which you want to report. Name the report, give it a short description, and save it to one of your personal categories.

2. Adjust the filters

When you create a new report, the system chooses default database columns for it and sets the report filter to standard values. You can delete or modify columns you don't need, and adjust the filter to match the report's requirements.

In this case, the default report filter is to include only unresolved work items updated in the last seven days:

To adjust the filter statement, click the "Updated in Last 7 Days" link. In the form that appears, click the magnifying glass button to the right of the Value field:

The Pick Value form appears. You can select a value in human-friendly form, such as "last week". In this case, select "Current Year":

Click OK to accept the value. On the Filter form click Submit to accept the revised filter. The report now covers open work items updated in the past year:

3. Add a column with the number of days each item has been open

Click the Edit Columns to Include icon on the toolbar to display the Columns to Include form. Click the add icon at the top right of the form to add a new column.

Click the Function Builder icon to the right of the new column's Column Name field to display the Function Builder. In the "Select a function" field enter the first few characters of the word "difference" and click the down-arrow. The system provides likely matches for what you have typed:

Select the pxDifferenceInDays function. The form changes to provide additional fields where you specify the two dates to compare:

In the first input field, type "create" and click the down arrow. Accept the system suggestion of pxCreateDateTime.

In the second field, we have to call a function that returns the current date and time. We can then subtract the create date from the current date to get the number of days the work item has been open.

Click the function icon to the right of the second field: the form changes to display the current function and let you select a function to embed to provide the second value. In the Select a function field, type "curre" and click the down arrow key:

Accept the system suggestion of pxCurrentDateTime and click Submit to add the embedded function to the main function. Click Submit in the main function form to use the function to populate the new column

Change the column heading for the new column to "Days Open":

4. Remove unneeded columns

Click the garbage can icon  above each column you do not want, to remove it. In this case, leave only the Work ID, Create Date/Time, and Days Open columns.

5 Review the report

When you click OK on the Columns to Include form, the report updates to reflect the changes you made.

Nesting SQL functions lets you manipulate data--performing calculations, concatenating text strings, and so on--to provide exactly the information you want your report to display.

Additional information

How to create a report

Reporting in PRPC 6.2 - Nine tutorial videos

How to create custom SQL functions for reporting

How to use symbolic dates in report selection criteria