Report Totals/Sum with Nested Tables

4 posts, 0 answers
  1. James
    James avatar
    2 posts
    Member since:
    Sep 2019

    Posted 09 Oct Link to this post

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

  2. Neli
    Admin
    Neli avatar
    190 posts

    Posted 14 Oct Link to this post

    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

    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. James
    James avatar
    2 posts
    Member since:
    Sep 2019

    Posted 14 Oct in reply to Neli Link to this post

    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

  4. Neli
    Admin
    Neli avatar
    190 posts

    Posted 17 Oct Link to this post

    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

    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