This is a migrated thread and some comments may be shown as answers.

How do I sum a nested Json property within a table

2 Answers 108 Views
Report Designer (standalone)
This is a migrated thread and some comments may be shown as answers.
Marvin
Top achievements
Rank 1
Veteran
Iron
Marvin asked on 09 Feb 2021, 03:33 PM

As you can see in the screen shot, I have a table with values, but the one property is a collection of values.

I need to summarize the values in the nested collection in the "Payments" column of the report. If I try to reference the field "PaymentAmount" I get an error "The expression contain object 'PaymentAmount' that is not defined in the current context".

I understand I could add another table inside the field, but that doesn't work well in my case.

Here is my Inline Json Datasource:

{
    "value": [
        {
            "DocNumber": "952",
            "PaymentLinks": [
                {
                    "PaymentAmount": 1000.00
                }
            ]
        },
        {
            "DocNumber": "953",
            "PaymentLinks": []
        },
        {
            "DocNumber": "954",
            "PaymentLinks": [
                {
                    "PaymentAmount": 11.00
                },
                {
                    "PaymentAmount": 12.00
                },
                {
                    "PaymentAmount": 10.00
                }
            ]
        }
    ]
}

 

2 Answers, 1 is accepted

Sort by
0
Dimitar
Telerik team
answered on 12 Feb 2021, 12:47 PM

Hello Marvin,

To use deep data such as the field PaymentAmount you need to drill down up to the point where you can have access to those fields. There are a couple of approaches that could achieve that.

For example, you could use the built-in Item function which gives you access to array elements by index. This way you could access any element no matter how deep it is, however, the drawback is that you need to specify the index which might be a problem since based on the sample data that you have provided the PaymentLinks array might contain one, three, or zero elements.

You could also have a second data source where you could apply the JSON path filter on the data as explained in the How to Use JSONPath to filter JSON data article, then you could paste a table next to the original, that uses the filtered JSON data source and therefore has access to the PaymentLinks array.

Also, you mention that nesting data items such as a table do not work for you, perhaps that is because the result gets repeated for the number of elements in the PaymentLinks array of that row? As a workaround, just like the above suggestion, you could paste a table/crosstab/list next to the original table, where nest more data items using Bindings to set their DataSource until you get access to the PaymentsLink array directly. Note that when binding a data source like this you won't be able to see the Fields in the expression dialog but if the binding is correct the corresponding fields will be available.

I have attached a sample report where I have used the 3rd approach, please examine it carefully and let me know if you have any questions.

I look forward to receiving an update from you. Thank you for using Telerik Reporting.

Regards,
Dimitar
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Tursunkhuja
Top achievements
Rank 2
Veteran
answered on 17 Mar 2021, 09:53 AM

Thanks Dimitar,

We solved this problem by creating our own user function.

Tags
Report Designer (standalone)
Asked by
Marvin
Top achievements
Rank 1
Veteran
Iron
Answers by
Dimitar
Telerik team
Tursunkhuja
Top achievements
Rank 2
Veteran
Share this question
or