How can we do sum of nested Json property values

4 posts, 0 answers
  1. Tursunhuja
    Tursunhuja avatar
    32 posts
    Member since:
    May 2018

    Posted 18 Oct 2019 Link to this post

    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     
            }

         ]
    }  

  2. Neli
    Admin
    Neli avatar
    252 posts

    Posted 18 Oct 2019 Link to this post

    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

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. Tursunhuja
    Tursunhuja avatar
    32 posts
    Member since:
    May 2018

    Posted 11 Nov 2019 in reply to Neli Link to this post

    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?

  4. Neli
    Admin
    Neli avatar
    252 posts

    Posted 14 Nov 2019 Link to this post

    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

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top