Report Totals/Sum with Nested Tables

[James]

I have a nested table in a textbox of a table and I am having trouble getting the summary of all the records in the nested table.

I can get the summary of each group in the nested table.  However, I cannot get the summary for all the records together.

I have posted the data structure with sample data below.  

The data source of the parent table is "= ReportItem.DataObject.TicketReportsDC"
I have added a row "Outside Group - Below" and I have tried "=Sum(Fields.TicketDataContract.TicketJob.TicketDetails.OrderAmount)".  This gives me an error :'OrderAmount' is not defined in the current context. 

The data source of the nested table is "= ReportItem.DataObject.TicketDataContract.TicketJob.TicketDetails"
I have added a row "Outside Group - Below" and put "=Sum(Fields.OrderAmount)" in the appropriate textbox. This gives me the Summary for the individual ticket. ($1650.00,  $1200.00, and $625.00 for the below sample data)

I have tried other possibilities and am not haveing any luck getting this.  I believe it is due to the fact that the "TicketDetails" is a list within a list (TicketReportsDC) in the Data Structure.  

Can anyone give me any ideas for referencing the "OrderAmount" at the top level?  With the sample data below I would expect  a total of $3475.00.

 

Thanks

James

-----------------Data Structure with Data ------------------
{
  "TicketReportsDC": [
    {
      "TicketID": 1212
      "TicketDataContract": {
        "TicketJob": {
          "TicketDetails": [
            {
              "Item#": 123
              "Quantity": 3
              "OrderAmount": 1500
            }
            {
              "Item#": 124
              "Quantity": 1
              "OrderAmount": 100
            }
            {
              "Item#": 125
              "Quantity": 2
              "OrderAmount": 50
            }
          ]
        }
      }
    }
    {
      "TicketID": 1240
      "TicketDataContract": {
        "TicketJob": {
          "TicketDetails": [
            {
              "Item#": 123
              "Quantity": 2
              "OrderAmount": 1000
            }
            {
              "Item#": 124
              "Quantity": 2
              "OrderAmount": 200
            }
          ]
        }
      }
    }
    {
      "TicketID": 1245
      "TicketDataContract": {
        "TicketJob": {
          "TicketDetails": [
            {
              "Item#": 123
              "Quantity": 1
              "OrderAmount": 500
            }
            {
              "Item#": 124
              "Quantity": 1
              "OrderAmount": 100
            }
            {
              "Item#": 125
              "Quantity": 1
              "OrderAmount": 25
            }
          ]
        }
      }
    }  
  ]
}

[Neli]

Hi James,

You can try implementing UserFunction or Custom Aggregate Function which has to return the Grand total for all groups.

Another option is to add a field in the datasource which contains the total for each level of nesting.

Regards,
Neli
Progress Telerik

[James]

Neli,

Thank you.  Unfortunately,  I am using the stand alone reporting tool against a 3rd party system and I cannot add any functions into their system.  So my hands are tied.  I have asked them about a summary field in the datasource.  I just wanted to check to see if I was missing something or there were any other options.

 

Thanks again.

James

[Neli]

Hello James,

With these restrictions, the only option is to add a total for each Ticket ID in the JSON. I hope you will manage to arrange that.

Regards,
Neli
Progress Telerik