Question
Common Wealth Bank of Australia
IN
Last activity: 21 Mar 2018 13:47 EDT
What is the Difference between Sub Reports and joining the Table in Report Definition
Please any one explain detail what is the difference between using sub reports in report definition to the joining the table in report definition.
Please share scenario when we go for sub reports and when we go for Join condition in report definition.
Accepted Solution
Pegasystems Inc.
US
Hello,
Joins are useful when we associate two different classes and present columns which belong to the class in the second class. For example, we use a predefined association between Assign-Worklist and the work class to display columns belonging to both these classes.
Sub reports join two different classes using specific join conditions and the results from the second report can be used to filter the first report or can display as part of the first report.Assume we want columns that we joined from other tables to display aggregate calculations, for example we want to display a report that shows a list of all purchase requests, and includes columns for the time when the request is created, the operator who created the request and case ID, and all these columns come from the purchase request work class. Now we need to include the aggregate calculations on subcases of the purchase request: the date the first subcase was resolved, the date the last subcase was resolved, and the subcase count. This requires us to use a sub report.
Hello,
Joins are useful when we associate two different classes and present columns which belong to the class in the second class. For example, we use a predefined association between Assign-Worklist and the work class to display columns belonging to both these classes.
Sub reports join two different classes using specific join conditions and the results from the second report can be used to filter the first report or can display as part of the first report.Assume we want columns that we joined from other tables to display aggregate calculations, for example we want to display a report that shows a list of all purchase requests, and includes columns for the time when the request is created, the operator who created the request and case ID, and all these columns come from the purchase request work class. Now we need to include the aggregate calculations on subcases of the purchase request: the date the first subcase was resolved, the date the last subcase was resolved, and the subcase count. This requires us to use a sub report.
Another use case for sub reports is to use the results of a report as a filter for the calling report. Joins can be used in some of these cases but sub reports are useful when we want to exclude the choices based on the results of a sub report. For example, we want to know the list of all operators who have no assignments past goal.
This is what the explanation provided by Pega documentation, hope it might give you clear idea when to use what.
Regards
Mahesh
Pegasystems Inc.
IN
Hi,
Subreports enable you to reference results from any report definition in a main report. A report definition used as a subreport can be run like any other report.
Note: Consider subreports as a way of combining data using IN, HAVING, and WITH clauses.
Hi,
Subreports enable you to reference results from any report definition in a main report. A report definition used as a subreport can be run like any other report.
Note: Consider subreports as a way of combining data using IN, HAVING, and WITH clauses.
Subreports can be defined in classes different from the main report. You can access data in different classes similar to the way you would use a class join or an association. You commonly use subreports to satisfy complex reporting requirements. For instance, you can use subreports to filter results. This approach allows you to include or exclude data. You can also use subreports to display aggregate calculations on specific rows in a main report. You use two different methods to create a subreport: join filters or aggregation.
Using join filters to create a subreport
Assume you want to display the task information for each purchase request recently updated by each operator.
Do the following things to create the report:
-
Create a subreport for purchase requests that retrieves the most recent update date by update operator.
-
On the main report in the Query tab, add columns for the requested data for each case and specify the subreport you want to reference.
-
On the main report's Data Access tab, add the subreport.
-
Create a join filter condition for the subreport. The filter defines how you want to join the subreport data to the main report. As shown in the following example, the subreport Update Operator column is matched to the update operator value (.pxUpdateOperator) in the main report.
-
On the Design tab, add a filter condition so that the update date value is equal to the update data value in the subreport.
When you run the report, it shows, for each operator, information about the purchase request the operator most recently updated. For each case, the report displays the update date and time retrieved from the subreport.
Using aggregation to create a subreport
You can use subreports to display aggregate calculations on specific rows. For example, assume you want to list the managers in the Engineering division who have more than ten direct reports.
You would do the following things to create the report:
-
Create a subreport that shows the managers who have direct reports, and how many they have. You use filter conditions to limit the data to managers who are part of the Engineering division and report directly to someone.
-
On main report's Data Access tab, add the subreport.
-
Create a join filter condition to join data from the subreport to the main report where the value in the subreport's operator column matches the value in the operator column in the main report.
-
Create a filter condition to use only data from the subreport where the number of reports-to instances is greater than ten.
When you run the report, it displays managers in the Engineering divisions who have more than ten direct reports.
You can refer below links for more details.
https://docs-previous.pega.com/when-and-how-use-sub-reports-report-definition-reports
https://community.pega.com/sites/default/files/help_v731/procomhelpmain.htm
https://community.pega.com/sites/default/files/help_v731/procomhelpmain.htm
Thank You
Pegasystems Inc.
US
Hello,
Subreport is again a separate query and that results are given to the main query for further condition applied to the main query. Subreport is used when there is no direct relation or in some complex requirements.
Whereas Join condition is used when we have the direct relationship with the table.
Lets assume we want to show some aggregate in list RD which is not possible without subreport, similary if you want to show some count as one column in the list report only then subquery can return the count and the main report still will remain as list type.
Hope this might give you some idea.
Regards
Mahesh
Pegasystems Inc.
US
To explain further, Join table can be used when we have a common column between the two reports with same data type. Join condition will provide you 3 types of joins. where as if you have a table1 with 10 columns and 1000 records in it then our subreport will return 5 columns and 500 records data, now the main report will query the data available in this subreport i.e. 5 columns and 500 records instead of querying the database table. Hope it might give you clear picture about both the concepts.
Accepted Solution
Pegasystems Inc.
US
Hello,
Joins are useful when we associate two different classes and present columns which belong to the class in the second class. For example, we use a predefined association between Assign-Worklist and the work class to display columns belonging to both these classes.
Sub reports join two different classes using specific join conditions and the results from the second report can be used to filter the first report or can display as part of the first report.Assume we want columns that we joined from other tables to display aggregate calculations, for example we want to display a report that shows a list of all purchase requests, and includes columns for the time when the request is created, the operator who created the request and case ID, and all these columns come from the purchase request work class. Now we need to include the aggregate calculations on subcases of the purchase request: the date the first subcase was resolved, the date the last subcase was resolved, and the subcase count. This requires us to use a sub report.
Hello,
Joins are useful when we associate two different classes and present columns which belong to the class in the second class. For example, we use a predefined association between Assign-Worklist and the work class to display columns belonging to both these classes.
Sub reports join two different classes using specific join conditions and the results from the second report can be used to filter the first report or can display as part of the first report.Assume we want columns that we joined from other tables to display aggregate calculations, for example we want to display a report that shows a list of all purchase requests, and includes columns for the time when the request is created, the operator who created the request and case ID, and all these columns come from the purchase request work class. Now we need to include the aggregate calculations on subcases of the purchase request: the date the first subcase was resolved, the date the last subcase was resolved, and the subcase count. This requires us to use a sub report.
Another use case for sub reports is to use the results of a report as a filter for the calling report. Joins can be used in some of these cases but sub reports are useful when we want to exclude the choices based on the results of a sub report. For example, we want to know the list of all operators who have no assignments past goal.
This is what the explanation provided by Pega documentation, hope it might give you clear idea when to use what.
Regards
Mahesh
NIH - National Institute on Aging
US
Class Join contains 3 types of JOINS.
Only Include Matching Rows = INNER JOIN
Inlcude All Rows in this class = LEFT OUTER JOIN
Include All Rows in Joined Class = RIGHT OUTER JOIN
Thanks, Ramesh