How do I sum a nested Json property within a table

[Marvin]

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
                }
            ]
        }
    ]
}

 

[Dimitar]

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

[Tursunkhuja]

Thanks Dimitar,

We solved this problem by creating our own user function.