Question
Common Wealth Bank of Australia
AU
Last activity: 26 Aug 2020 8:17 EDT
What is the easiest way to calculate the sum of each column in a repeating grid?
I have a requirement to show a pagelist in an editable repeating grid. The pagelist contains of 12 integer properties.
I need to show the sum of each column as an additional row in the repeating grid as read-only.
Do we have an OOTB approach to solve this issue or does it have to be custom built?
Accepted Solution
Common Wealth Bank of Australia
AU
I did a workaround for the above requirement as currently there is no OOTB way for calculating the sum of each column in a repeating grid.
In our requirement, we had only fixed pagelist which helped a bit in fixing this issue.
Got this working with a fair amount of declare expressions and a single data transform.
Below are the steps we implemented:
Define a separate "Example" page property similar to the data class of the pagelist
- Declare an expression for each integer property in the Example page, which will hold the sum of all the pages except the total page (explicitly mention the subscript) in page list.
- Create a Data transform which copies the value from "Example" to the last total page of the pagelist property.
In the Section which has Repeating Grid do the following steps,
- On change of each element in the repeating grid, run the data transform which copies the value from "Example" page to the Total page of the page list.
- In the row visibility, apply the when condition to show the row as "readonly" if the current row in the pagelist has a label "Total".
Regards,
Sri
Pegasystems Inc.
US
You can try using declare expressions. Make sure "Enable expression calculation?" is checked in the harness.
Easier option would be to use Report Definition as the source of the grid. You can use Aggregate functions such as SUM in the report definition.
Common Wealth Bank of Australia
AU
Thanks for your reply Rahul.
I tried using declare expression, there are just so many rules that needs to be written for each property. Hence , I was asking is there any default aggregate function.
To give more insight of the requirement:
The pagelist is an editable table and users will be entering data in the screen, on change of each of the integer property, the sum should be calculated.
Citicorp Services India Private Limited
IN
Hi Srividhya,
I will try to understand your question. You have 12 integer properties , which would be displayed as 12 columns, You need to add additional row to display the sum of the twelve columns values(Sum of the values in the first row). Whether my understanding correct? Why don't we display the sum as 13th column? It will be easy if we display the pagelist as repeating grid.
We can achieve your requirement using OOTB way. But you need to use embedded sections with repeating dynamic layouts. I believe we can't achieve your requirement with repeating grid layout.
Please share your thoughts
Thanks,
Gowrishankar.
Common Wealth Bank of Australia
AU
Thanks for your reply Gowrishankar.
The total is for each column and not for each row, so we cannot show it as the 13th column.
Functionally in our requirement, sum of column means a different from sum of rows.
As stated in my earlier reply, the sum must be calculated on the fly, when the users enter the value on the screen for each column in the repeating grid.
Do you still think this can be acheived in repeating dynamic layouts?
Regards,
Sri
Citicorp Services India Private Limited
IN
Hi Srividhya,
I am sorry, still I could not understand the requirement clearly. Could you please confirm whether the requirement is similar to the picture posted below?
Thanks,
Gowrishankar.
Accepted Solution
Common Wealth Bank of Australia
AU
I did a workaround for the above requirement as currently there is no OOTB way for calculating the sum of each column in a repeating grid.
In our requirement, we had only fixed pagelist which helped a bit in fixing this issue.
Got this working with a fair amount of declare expressions and a single data transform.
Below are the steps we implemented:
Define a separate "Example" page property similar to the data class of the pagelist
- Declare an expression for each integer property in the Example page, which will hold the sum of all the pages except the total page (explicitly mention the subscript) in page list.
- Create a Data transform which copies the value from "Example" to the last total page of the pagelist property.
In the Section which has Repeating Grid do the following steps,
- On change of each element in the repeating grid, run the data transform which copies the value from "Example" page to the Total page of the page list.
- In the row visibility, apply the when condition to show the row as "readonly" if the current row in the pagelist has a label "Total".
Regards,
Sri