How can we do sum of nested Json property values

[Tursunkhuja]

I have the report that contains a table to show some payment information, and underneath there is textBox to show sum of PaymentAmount property values.

See attached sample report image to see. The report has jsonDataSource and it has collection property of "PaymentLinks" which has 4 items and each item of the collection has "PaymentAmount" integer value. The table is showing "PaymentAmount" property value. 

I have to calculate sum of "PaymentAmount" column and set the result in TextBox next to "TotalPaymentAmount:". How should I do that?

FYI: If you right click on the table last row, you can see "Add Total" on the context menu. Maybe we can use this to sum of the column values?

The jsonDataSource contains this inline JSON:

{            
     "TotalLinksAmount": 10,       
     "PaymentLinks": [
        {                   
             "PaymentAmount": 10         
        },
        {               
             "PaymentAmount": 100       
        },
        {
             "PaymentAmount": 25     
        },
         {
             "PaymentAmount": 30     
        }

     ]
}  

[Neli]

Hello Tursunhuja,

The sum of all PaymentAmounts can be displayed next to TotalLinksAmount through the steps below which are applied in the attached report:

1) Set the JSON DataSource to the report;

2) Add a textbox displaying:

= Fields.TotalLinksAmount

3) Add a table to be able to access the inner properties of the JSON property that is a collection (PaymentLinks) and calculate their sum;

4) Set its datasource through the following Binding:

Property path: DataSource

Expression: = Fields.PaymentLinks

5) Add "=Fields.PaymentAmount" in a table detail cell. You need to type it manually as it is a property of a collection item.

6) Right-click over the table -> Insert row -> Outside Group Below;

7) Set the Value of the new row as follows:

="Total PaymentAmount: " + Sum(Fields.PaymentAmount)

Regards,

Neli
Progress Telerik

[Tursunkhuja]

I'm using filtering to limit the table rows. For example, I have 20 rows on my table and I'm showing only 5 of them, because I don't need other extra rows. I did those steps you wrote by inserting new row and using aggregate function Sum(). But that Sum function summing only 5 selected rows values. For my case it should sum all of 20 rows values even only first 5 of the rows are shown.

How do I do this?

[Neli]

Hello Tursunhuja,

To achieve such functionality you need to use Conditional Formatting instead of Filtering to hide the rows. I modified the previously provided sample in the following way:

1) Click on the field which contains "=Fields.PaymentAmount", then from the menu with Properties select ConditionalFormatting.

2) Set the following rule:

        Expression: =Fields.PaymentAmount

        Operator: >

        Value: =50

Set Style -> Layout - > uncheck Visible

The expression for calculating the sum will remain the same.

You can find attached the updated version of the report.

 

Regards,
Neli
Progress Telerik