Is it possible to perform grouping in Telerik Reporting using nested data from a JSON Datasource?

[Marcel]

We have a JSON dataset where one of the nodes is a list of products. We have generated a DataSource based on this product list. Within each product node, there is an AdditionalInfos node, which is a list of data that complements each product. Find example attached.

        "ProductList": {
           "Product": [
          {
            "@Item": "Item1",
            "@BeginDate": "17/05/2023",
            "@Total": 253054.99,
            "AdditionalInfos": {
              "AdditionalInfo": [
                {
                  "@Key": "Vehiculo",
                  "@Value": "MCG02"
                },
                {
                  "@Key": "Terminal",
                  "@Value": "TERMINAL A"
                },
 
              ]
            }
          },
          {
            "@Item": "Item2",
            "@BeginDate": "16/05/2023",
            "@Total": 1234678.12,
            "AdditionalInfos": {
              "AdditionalInfo": [
                {
                  "@Key": "Vehiculo",
                  "@Value": "MCG03"
                },
                {
                  "@Key": "Terminal",
                  "@Value": "TERMINAL B"
                },
 
              ]
            }
          },
          {
            "@Item": "Item3",
            "@BeginDate": "15/05/2023",
            "@Total": 5646548.35,
            "AdditionalInfos": {
              "AdditionalInfo": [
                {
                  "@Key": "Vehiculo",
                  "@Value": "MCG04"
                },
                {
                  "@Key": "Terminal",
                  "@Value": "TERMINAL B"
                },
 
              ]
            }
          },
         ]
	}

Our goal is to generate a graphical representation of the product lines grouped by the AdditionalInfo lines with the Key "Terminal." The desired output should be as follows:

Terminal A
        Item1      17/05/2023       253054.99
Terminal B
        Item2     16/05/2023       1234678.12
Item3     15/05/2023       5646548.35

We would like to know the best approach to achieve this in Telerik Reporting Standalone. Is it possible to generate a DataSource with all the information at the same level and then perform grouping based on it?

Any guidance or examples would be highly appreciated. Thank you in advance for your assistance!

[Dimitar]

Hello Marcel,

Thank you for the sample data!

It is indeed possible to achieve the desired layout with the given data structure, however, the column headers being nested in the AdditionalInfos array means that some data bindings will be required.

Firstly, we need to get to the Product array so we can use a JsonDataSource component and we may have a JSONPath filter that gets only the data we will use, for example:

For more details on what JSONPath is and how to use it, you may have a look at the Writing JSONPath expressions to filter JSON data - Telerik Reporting article.

Applying the above filter in the "Data selector" input, we will retrieve the shown below the terminal name. However, to show the terminal name itself, we need a data item whose data source is bound to the AdditionalInfos.AdditionalInfo field, for example:

For more details on how data bindings work, please refer to the Binding to Data Overview - Telerik Reporting article.

In the above image, there is also a binding to the visible property of a list report item(I chose to use a list for showing the terminal name). This is required because we cannot do a grouping on the nested @Value filed which holds the terminal name. This is due to the structure of the data. So, instead, the binding will hide the list when the previous terminal name is the same as the current, thus appearing as if grouped.

I have attached the sample report that I created while writing my reply, please have a look at it and let me know if you have any further questions or if you need further assistance.

Thank you for using Telerik Reporting!

Regards,
Dimitar
Progress Telerik

[Marcel]

Hi Dimitar,

Thank you very much for your answer. We like your approach, however if we change the order of the lines the grouping may be incorrect. In the example below ,the order of the Terminal lines is TERMINAL A, TERMINAL B, TERMINAL A:

{
   "ProductList":{
      "Product":[
         {
            "@Item":"Item1",
            "@BeginDate":"17/05/2023",
            "@Total":253054.99,
            "AdditionalInfos":{
               "AdditionalInfo":[
                  {
                     "@Key":"Vehiculo",
                     "@Value":"MCG02"
                  },
                  {
                     "@Key":"Terminal",
                     "@Value":"TERMINAL A"
                  }
               ]
            }
         },
         {
            "@Item":"Item2",
            "@BeginDate":"16/05/2023",
            "@Total":1234678.12,
            "AdditionalInfos":{
               "AdditionalInfo":[
                  {
                     "@Key":"Vehiculo",
                     "@Value":"MCG03"
                  },
                  {
                     "@Key":"Terminal",
                     "@Value":"TERMINAL B"
                  }
               ]
            }
         },
         {
            "@Item":"Item3",
            "@BeginDate":"15/05/2023",
            "@Total":5646548.35,
            "AdditionalInfos":{
               "AdditionalInfo":[
                  {
                     "@Key":"Vehiculo",
                     "@Value":"MCG04"
                  },
                  {
                     "@Key":"Terminal",
                     "@Value":"TERMINAL A"
                  }
               ]
            }
         }
      ]
   }
}

The result for this case is: 

Terminal A
        Item1     17/05/2023       253054.99
Terminal B
        Item2     16/05/2023       1234678.12
Terminal A
        Item3     15/05/2023       5646548.35

but we would like it to be:

Terminal A
        Item1     17/05/2023       253054.99
        Item3     15/05/2023       5646548.35
Terminal B
        Item2     16/05/2023       1234678.12

Is there any way to achieve that?

Thank yoy very much!

[Dimitar]

Unfortunately, the 'TERMINAL' data being as nested as it means that we cannot sort it. To be more specific, the problem is not necessarily the nested level in which the terminal data is but rather that the rest of the above is a level or two above it.

The solution would be to sort the data before sending it to the report. You may do that using the ObjectDataSource Component where you can write code that will return the sorted data. I suggest having a look at the Binding ObjectDataSource to a BusinessObject - Telerik Reporting article for an example of how the component works.

Please let me know if you need additional assistance.

[Marcel]

Hi Dimitar, thanks for your comments and references to using ObjectDataSource.

In our case, we finally have used Groups, grouping by =Item(1,Fields.AdditionalInfos.AdditionalInfo).[@Value] . In fact, using the function Item to access to the AdditionalInfos positions has been very useful for us and we will use it in other cases.

Thanks again!