Table of Contents

Article

When and how to use sub-reports in Report Definition reports

Published July 8, 2011 — Updated October 7, 2014

Summary

 

A sub-report is a Report Definition report that provides some or all of its results to another Report Definition report that invokes it. Using a Report Definition rule as a sub-report does not change the rule in any way, and the original report can still be accessed directly in its own right.

Users can designate sub-reports within Report Definition reports to simplify assembling data from several classes, or to satisfy complex conditions.

For example, the requirements for a report might state: "For each operator, show information (ID, label, status, last update) for the last work item they updated within a given work type." A way to accomplish this would be:

  1. Create a sub-report that retrieves the most recent update date by update operator within the work type.
  2. Create the main report to select the task information for all work items.
  3. Within the main report reference the sub-report, matching the update operator and the update date columns in the two reports.
  4. Display data about those work items that satisfy the criteria for both reports.

Version 5 reports (list views and summary views) cannot serve as sub-reports.

This article demonstrates how to use sub-reports to satisfy requirements that would be difficult to meet in other ways, by examining four use cases:

 

Suggested Approach

The general procedure has four steps:

  1. Analyze the requirements for the report to identify information a sub-report could provide.
  2. Create one or more sub-reports that provide that information.
  3. Create a main report, and use information from the sub-report or sub-reports.
  4. Verify that the reports requirements have been satisfied.

Note that, while you can create reports and potential sub-reports in the Report Browser and Report Viewer, you must open the main report's rule form in the Designer Studio and specify its sub-reports on the rule form's Data Access tab.

Note also that you can review the SQL statement the Report Definition rule creates by running the report and then clicking the clipboard icon clipboard to display the clipboard. Click UserPages > pyReportContentPage (Code-Pega-List) and review the contents of the pxSQLStatementPost property.

The following use cases can be satisfied using this general procedure.

Four use cases

  • Use Case 1: For each operator, show task information for the work item the operator most recently updated within a specific work type. The task information should include work item ID, label, status, and date last updated.

    This use case illustrates using a sub-report to select the data to include

  • Use Case 2: List the operators who have not updated work items of a specific type within the past week.

    This use case illustrates using a sub-report to select the data to exclude.

  • Use Case 3: List the managers in a specific division who have more than ten direct reports.

    This use case illustrates using a calculated value from a sub-report in the main report.

  • Use Case 4: List the average number of direct reports for the managers in a specific division.

    This use case illustrates using multiple sub-reports in a single main report.

 

Use Case 1: For each operator, show task information for the work item the operator most recently updated within a specific work type. The task information should include work item ID, label, status, and date last updated.

Plan

Create a sub-report to list the most recent updates by all operators. Match the data from that sub-report to the data of a report of when all work items were updated. Where there is a match between the date/time of a work item's update and the most recent update date/time for an operator, include information about the work item and the person who last updated it.

Steps

1. Create the report definition rule for the sub-report, and save it in the class of the work type on which you want the report. On the Design tab, give the report two columns: the first column holds the operator IDs of operators who created or updated work items. The second column holds the "MAX" (highest) date-time for that operator in the .pxUpdateDateTime values for all the work items in the class.

sub-report

2. The sub-report includes a single row for each operator who has created or updated a work item, showing the date and time of that person's most recent action. Save and run the sub-report to see the requested data.

subreport results

3. Create the report definition rule for the main report. Give it five columns to hold the requested data for each work item: update operator, work item ID, work item label, status, and the update date and time.

main report

4. On the Data Access tab, add the sub-report. You refer to its properties in the main report by a text prefix that you specify here.

add the subreport

5. Click the Configure button to display the Configure Sub-Report form. This is where you specify how data from the sub-report is joined to the data in the main report.

configure sub-report form

  • In the Number of Rows... area select More than 1 Row.
  • In the Where will you use this Sub-Report? area, check all three checkboxes. This lets the sub-report and its columns appear as options in selection menus on both sides of filter conditions you set, and in the Columns to Include section on the main report'sDesign tab.
  • In the How will you join results... section, set a filter condition to join data from the sub-report to the main report where the value in the sub-report's Update Operator column matches the value of property .pxUpdateOperator in the main report.
  • Set the relationship to only include matching rows from both reports.
  • In the How will you refer to columns in Sub-Report... section, provide an alias for each column you want to reference in the Sub-Report. The aliases appear as SmartPrompt options in selection menus in the main report.
  • Click OK to save the configuration. Click Cancel to close the form without saving your edits.

6. On the Design tab, in the Rows To Include section, add a filter condition as in the image below. Work items are included only if the work item's update date/time exactly matches a date/time entry in the sub-report.

The Filter Conditions field must refer to every filter you create, using the Conditions field entries.

In this case, your condition is that the work item's .pxUpdateDateTime value Is Equal to sub.Update_Date_Time, the value in the sub-report.

set the filter

7. Save and run the report. The Filters line above the report data summarizes the filter you established.

full report results

The report shows, for each operator, information about the work item the operator most recently updated.

Use Case 2: List the operators who have not updated work items of a specific type within the past week.

Plan

Create a sub-report that lists all the operators who have updated work of a specific type within the past week. Use the sub-report as a filter, comparing with a list of all operators and removing all entries where there is a match between the two reports.

Steps

1. Create a sub-report in the class of the work types to report on. On the Design tab, give the report a single column, listing the update operators for all work items.

set subreport

In the Rows To Include section, add a filter condition so the report only includes update operator IDs from work items that have been updated in the last seven days.

When you select a column that holds date/time data, you can select in the Relationship field from a series of symbolic dates such as "Today", "Last Month", or, in this case, "Last 7 Days".

set rows to include

2. Run the sub-report to see the list of update operators for work items that meet the filter conditions.

sub report

3. Create the main report definition rule in the same class as the work items on which it reports. On the Design tab, give this report only one column, which holds the operator IDs of the operators who have not updated work items in the period selected in the sub-report.

main report

4. On the Data Access tab, reference the sub-report. You refer to its properties in the main report by a text prefix you specify here.

attach subreport

5. Click the Configure button to display the Configure Sub-Report form. This is where you specify how data from the sub-report is joined to the data in the main report.

configure sug-report form

  • In the Number of Rows... area select More than 1 Row.
  • In the Where will you use this Sub-Report? area, check the Right Hand Side of Filter conditions checkbox. This lets the sub-report and its columns appear as options in selection menus on the right side of filter conditions you set on the main report'sDesign tab.
  • In the How will you join results... section, you do not need to specify a filter.
  • Since you have no filter, set the filter relationship to Do not match rows.
  • In the How will you refer to columns in Sub-Report... section, provide an alias for each column you want to reference in the Sub-Report. The aliases appear as SmartPrompt options in selection menus in the main report.
  • Click OK to save the configuration. Click Cancel to close the form without saving your edits.

 

6. On the Design tab, in the Rows To Include section, check the Remove Duplicate Rows checkbox. Add a filter condition to exclude any entry where the update operator ID matches the update operator ID of a row in the sub-report (the people who recently updated work items).

The Filter Conditions field must refer to every filter you create, using the Conditionsfield entries.

In this case, your condition is that the work item's .pxUpdateDateOperator value Is Not Equal to sub1.Update_Operator, the value in the sub-report.

set filter

7. Save and run the report. The Filters line above the report data summarizes the filter you established.

main report
The report shows operators who did not update work items in the past week, and who therefore were not included in the sub-report.

Use Case 3: List the managers in a specific division who have more than ten direct reports.

Plan

Presume that the division is "Engineering".

Create a sub-report that finds anybody people in the division report to, and counts up how many direct reports each person has. Then, in the main report, show only information from the sub-report if the number of direct reports is greater than ten.

 

Steps

1. Create a sub-report that applies to Data-Admin-Operator-ID. Save it in any convenient class in your application.

2. On the Design tab, in the Columns To Include section,, give the report two columns. The first column displays the operator ID of anyone who has a direct report. The second column counts the number of direct reports.

Note: Although the Column Name field in each column is marked as required, it is not required for a column using the COUNT summary function.

subreport columns

3. In the Rows To Include section, add two filter conditions to limit the data to operators who are part of the Engineering division and report directly to someone.

The Filter Conditions field must refer to every filter you create, using the Conditions field entries. In this case, the entry is A AND B.

The A condition looks for operators whose .pyOrgDivision property equals "Engineering". The B condition looks for operators whose .pyReportTo property is not null. Both conditions must be true for the operator to contribute to the report results.

filter conditions

4. Run the sub-report to see the operators who have direct reports, and how many they have.

subreport

5. Create the main report definition rule. Set its AppliesTo key part to Data-Admin-Operator-ID.

6. On the Data Access tab, in the Sub-Reports area, add the sub-report. You refer to its properties in the main report by the prefix you specify here.

associate subreport

7. Click the Configure button to display the Configure Sub-Report form. This is where you specify how data from the sub-report is joined to the data in the main report.

configure sub-report form

  • In the Number of Rows... area select More than 1 Row.
  • In the Where will you use this Sub-Report? area, check all three checkboxes. This lets the sub-report and its columns appear as options in selection menus on both sides of filter conditions you set, and in the Columns to Include section on the main report'sDesign tab.
  • In the How will you join results... section, set a filter condition to join data from the sub-report to the main report where the value in the sub-report's Update Operator column matches .pxUpdateOperator in the main report.
  • Set the relationship to only include matching rows from both reports.
  • In the How will you refer to columns in Sub-Report... section, provide an alias for each column you want to reference in the Sub-Report. The aliases appear as SmartPrompt options in selection menus in the main report.
  • Click OK to save the configuration. Click Cancel to close the form without saving your edits.

8. On the Design tab, in the Columns To Include section, create two columns. The first column holds the operator IDs of those who have direct reports. The second column has the count of those reporting to each listed operator, drawn from the sub-report "Count_of" column.

set main report columns

9. In the Rows To Include section, check the Remove Duplicate Rows checkbox. Create a filter to use only data from the sub-report where the number of reports-to instances is greater than ten.

The Filter Conditions field must refer to every filter you create, using the Conditions field entries. In this case, the entry is A.

The A condition looks entries where the count in the Count_of column is greater than 10.

set filter

10. Save and run the report. The Filters line above the report data summarizes the filter you established.

main report

There are now only three entries, those operators who each have more than ten members of the Engineering division reporting to them.

Use Case 4: List the average number of direct reports for the managers in a specific division.

Plan

Presume that the division in question is "Engineering".

Create a sub-report counts the number of operators who belong to the Engineering division. Create a second sub-report that lists the division members to whom others report directly.

Then, in the main report divide the value from the first sub-report by the value from the second sub-report, to find the average number of direct reports for each team member to whom others report.

1. Create a sub-report that applies to Data-Admin-Operator-ID.

On the Design tab, in the Columns To Include section, give the report a single column, providing a COUNT of the number of operator IDs that match the filter condition you set in the next step.

subreport column

2. In the Rows To Include section, add a filter condition so the report includes only operators whose .pyOrgDivision property equals "Engineering". Set the Filter Condition field to "A", matching the entry in the Condition column for the filter.

subreport filter

3. Run the first sub-report. It returns an ID count of the members of the Engineering Division.

the subreport

4. Create a second sub-report, saving it to the same RuleSet and Version as the first sub-report. Set its Applies To class to Data-Admin-Operator-ID.

On the Design tab, in the Columns To Include section, give the sub-report a single column. Use the COUNT DISTINCT summary function. The column shows the number of distinct entries the report finds in the .pyReportsTo property of operators who satisfy the filter condition you set in the next step.

subreport column

5. In the Rows To Include section set the same filter condition as you did in the first sub-report.

subreport filter

6. Run the second sub-report. It returns the number of managers (those with direct reports) in the Engineering Division.

subreport b

7. Create the main report, with the same Applies To. Save it into to the same RuleSet version as the two sub-reports.

On the Data Access tab, in the Sub-Reports section, add the two sub-reports. You refer to them on the Design tab by the tex prefixes you provide here.

associate subreports

8. For each sub-report, click the Configure button to display the Configure Sub-Report form. This is where you specify how data from the sub-report is joined to the data in the main report.

configure sub-report form

  • In the Number of Rows... area select 1 Row.
  • In the Where will you use this Sub-Report? area, check the first and third checkboxes. This lets the sub-report and its columns appear as options in selection menus on the right side of filter conditions you set, and in the Columns to Include section on the main report'sDesign tab.
  • In the How will you join results... section, you do not need to set a filter condition.
  • Since you have no filter condition, set the relationship to Do not match rows.
  • In the How will you refer to columns in Sub-Report... section, provide an alias for each column you want to reference in the Sub-Report. The aliases appear as SmartPrompt options in selection menus in the main report.
  • Click OK to save the configuration. Click Cancel to close the form without saving your edits.

 

9. On the Design tab, add a column that draws the count of employees from the first sub-report. To locate the property name, type the prefix of the sub-report in the Column Name field, followed by a period. Click the down-arrow to see a list of all the columns in the sub-report, and select the one you want. (In this case, since there is just one column, the choice is easy.)

column one

 

10. Following the procedure from the previous step, add a column that draws from the second sub-report the number of managers.

subreport 2

11. Add a third column. Click the Function Builder icon expression builder icon to create a function to populate this column.

The Function Builder appears in a pop-up window. It provides a large number of functions to select from, and displays additional input fields depending on the function you select.

In the Select a function field, enter "divide" and click the down arrow. Select the function that appears.

function builder choice

The Function Builder provides two Function Input fields for the properties the selected function requires. Enter the name of the first sub-report, followed by a period, in the first field, click the down arrow, and select the column you want from the display. Similarly, select a column from the second sub-report for the second field. Click Submit to save the function.

expression builder

The main report now has three columns:

three columns

12. Save and run the report. It displays the number of members of the Engineering Section and the number of managers, and calculates the average number of direct reports per manager. Since you applied no filters to the data in the main report, the Filters entry lists "none".

the report

By using sub-reports, you can construct Report Definition reports that can provide precisely the data needed for a situation.


90% found this useful

Have a question? Get answers now.

Visit the Pega Support Community to ask questions, engage in discussions, and help others.