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
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.
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!
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?