Question regarding Report Definition - I need to calculate a percentage and assign it to a column. The percentage should be calculated based on the result of 2 aggregated columns that is in the report definition.
I have a situation where in Report Definition, I need to calculate a percentage and assign it to a column. The percentage should be calculated based on the result of 2 aggregated columns that is in the report definition. Can you please let me know if this achievable?
Message was edited by: Marissa Rogers - Added Category, moved from Mesh Help
Accepted Solution
Pegasystems
IN
You can create sub-reports which can calculate the aggregate values. The main report can calculate the percentage by using the pxDivide and pxMultiply SQL functions.
Pegasystems Inc.
US
This is not the kind of thing you can do with a normal SQL query, so a report def really can't do it because you need the aggregated value before you calculate the relative percentage. That said, if you use a pie chart to display your aggregate values, I believe it will show the percentages in the grid, which it calculates post SQL call.
Alternately, you could write your report def to get the aggregated values. Next, you would call the report def from an activity and calculate the percentages (manually or declaratively) while placing the values into a second class structure and finally display the results in a grid layout.
Hope one that helps.
Accepted Solution
Pegasystems
IN
You can create sub-reports which can calculate the aggregate values. The main report can calculate the percentage by using the pxDivide and pxMultiply SQL functions.
Nordea
SE
Hi Rajiv,
Any help with the same scenario? see on the below link:
https://collaborate.pega.com/question/calculating-percentage-rd-through-sub-report
Regards,
Kousik
Public Safety Canada
CA
Hi,
I don't know if this helps you, but we've had a similar requirement to display both the count and its percentage value in the report definition results. We've actually just now come up with a super simple solution!
(BTW, we are working on version 7.1.8)
As an example, we are starting from OOTB report pyAgeByFlowAssignment that is listed in the Report Browser on the Manager Portal as "Timeliness by Flow and Task". This report displays the counts of cases that are within goal, within deadline and past deadline. The report definition uses a SQL function alias called pxSLAHistoryTimeliness. This function normally returns a 1 for a case that meets the various SLA criteria or a NULL if it doesn’t. And the report normally works by providing a SUM of the column to give us the counts we needed.
So for the percentages, we modified a copy of this function alias to instead return a 0, rather than NULL, if false. This is key. Then we added a row to the report definition to call our modified copy and aggregate the results by AVERAGE (instead of SUM). Apply a column format of Percentage, and voilà, a percentage is displayed in your table!
No need for any sub-reports, activities, or other data classes, or even having to fiddle with Multiply and Divide functions.
Presumably, you could create any new function alias to define your specific criteria for your desired counts and then aggregate by SUM to display a count and AVERAGE for the percentage.
Hi,
I don't know if this helps you, but we've had a similar requirement to display both the count and its percentage value in the report definition results. We've actually just now come up with a super simple solution!
(BTW, we are working on version 7.1.8)
As an example, we are starting from OOTB report pyAgeByFlowAssignment that is listed in the Report Browser on the Manager Portal as "Timeliness by Flow and Task". This report displays the counts of cases that are within goal, within deadline and past deadline. The report definition uses a SQL function alias called pxSLAHistoryTimeliness. This function normally returns a 1 for a case that meets the various SLA criteria or a NULL if it doesn’t. And the report normally works by providing a SUM of the column to give us the counts we needed.
So for the percentages, we modified a copy of this function alias to instead return a 0, rather than NULL, if false. This is key. Then we added a row to the report definition to call our modified copy and aggregate the results by AVERAGE (instead of SUM). Apply a column format of Percentage, and voilà, a percentage is displayed in your table!
No need for any sub-reports, activities, or other data classes, or even having to fiddle with Multiply and Divide functions.
Presumably, you could create any new function alias to define your specific criteria for your desired counts and then aggregate by SUM to display a count and AVERAGE for the percentage.
We were struggling with this one for a while, so I hope it helps someone else out there too!
Accenture
IN
Hi Rajiv,
I am also working on same scenario. Had created a sub report and able to calculate percentage also. But these two report definitions have same filter conditions. If a filter criteria on the main report is changed through report browser, the changed value is not passed to the sub report. The sub report is still taking the default filter conditions mentioned in the rule. Can you please suggest how to overcome this?
Thanks,
Arindam
Pegasystems
IN
Hi Arindam,
Unfortunately, there is no option currently to pass the filters of the main report to the sub report from the report browser / editor changes. Please file an enhancement request with Pega for this.
-Rajiv
Pegasystems Inc.
IN
Hi Arindam,
An enhancement has been filed for this in our internal portal. I will send you the necessary information via a private message. Please work with your Account Executive to track it.
Regards,
Lochan | Community Moderator | Pegasystems Inc.