Creating subtotals on groups

14 posts, 0 answers
  1. Steven
    Steven avatar
    84 posts
    Member since:
    Oct 2010

    Posted 16 Aug 2012 Link to this post

    I seem to be missing something in the way the grid operates when creating subtotals on groups.

    What I am looking to do is create a grid that initially does not have any groups defined and the groupable option is enabled.  Then, when a user drags a column up into the groups area, it creates the groups and provides subtotals for all the columns that I defined as having an aggregate.

    But, it appears that kendo doesn't work this way and requires me to know which columns will allow groups and aggregates before hand. Or am I missing something?

    If kendoGrid can do this, does someone have a jsfiddle example I can look at?   
  2. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 21 Aug 2012 Link to this post

    Hi Steven,

    As I understood it you would like to have a grid without any pre-defined groups and to be able to display an aggregate information in the groupFooterTemplate as soon as the user groups by drag and drop.

    If that is the case you should define the appropriate aggregate function(s) on each column. For example:
    {
        field: "foo",
        aggregates: ["sum", "average"],
        groupFooterTemplate: "Sum: #= sum # || Average: #= average #"
    }



    I hope this will help. Please let me know in case I missed something.

    Regards,
    Alexander Valchev
    the Telerik team
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  3. Kendo UI is VS 2017 Ready
  4. axwack
    axwack avatar
    37 posts
    Member since:
    Aug 2012

    Posted 01 Oct 2012 Link to this post

    What if you want to add another row in the group? Can that be done?
  5. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 04 Oct 2012 Link to this post

    Hello Vincent,

    I am afraid that your question is a bit unclear. Could you specify what do you mean?

    I modified the previous example and tried the following scenario:
    1. Group by "bar"
    2. Click "add new" and type 30 for in the first column and "A" in the second.
    3. Click "Save changes" - new record is placed inside the correct group and aggregate values are updated.
    You can test it here.

    Regards,
    Alexander Valchev
    the Telerik team
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  6. axwack
    axwack avatar
    37 posts
    Member since:
    Aug 2012

    Posted 04 Oct 2012 Link to this post

    Is it possible to still add to the grid on a grouped column without the "groupable: true" set? I have a grid that is grouped by the datasource not the grid. I found that when I add a row it adds a new row but doesn't know to group by the group field "ResidualCusip" in this case. It just adds a new row.

    orderSource: new kendo.data.DataSource({
                                                
                          transport: {
                                      read: {                                       
                                          url: "@Url.Action("Details", "Allocation")",
                                          dataType: "json",
                                          contentType: "application/json; charset=UTF-8"
                                       },
                                      create: {
                                          url: "@Url.Action("Create", "Allocation")",                                                                         
                                          type: "POST",
                                          dataType: "json",
                                          contentType: "application/json; charset=UTF-8",
                                          complete: function(result){
                                               window.location.href = "@Url.Action("Index", "Allocation")";
                                          }      
                                      },
                                      update: {
                                           url: "@Url.Action("Details", "Allocation")",
                                           dataType: "json",
                                           cache: false
                                      },
                                      parameterMap: function (data, type) {
                                           return kendo.stringify(data);
                                       }                                   
                            },
                            schema: {                        
                               model:{
                               id: "ResidualCusip",
                                   fields: {
                                       AcctCd: {type: "string", validation : {required: true}},                              
                                       SettleDate:  { type: "date", validation: {required: true }},
                                       TradeDate:  { type: "date", validation: {required: true }},
                                       TerminationDate:  {type: "date",  validation: {required: true }},
                                       EffectiveCoupon: { type: "number", validation: {required: true }},
                                       ResidualPrice:  { type: "number",validation: {required: true }},
                                       EffectiveCoupon: {type: "number",validation: {required: true}},
                                       FloaterAmount: {type: "number",validation: {required: true}},
                                       ResidualYield: {type: "number",validation: {required: true}},
                                       ResidualAmount: {type: "number",validation: {required: true}},
                                       InverseFloatPar: {type: "number",validation: {required: true}},
                                       LeverageRatio: {type: "number",validation: {required: true}},
                                       UnderlyingPar: {type: "number",validation: {required: true}},
                                       UnderlyingPrice: {type: "number",validation: {required: true}},
                                       UnderlyingYield: {type: "number",validation: {required: true}},
                                       UnderlyingCoupon: {type: "number",validation: {required: true}},
                                       Eventtype: {type: "number",validation : {required: true}},
                                       TrustCode: {type: "number",validation : {required: true}},
                                       Broker: {type: "string",validation : {required: true}},
                                       InterestOnFloat: {type: "number",validation : {required: true}},
                                       BondProceeds: {type: "number",validation : {required: true}},
                                       BMASIFMA: {type: "number",validation : {required: true}},
                                       WrapFee: {type: "number",validation : {required: true}},
                                       UpfrontFees: {type: "number",validation : {required: true}},
                                       InterestOnAssets: {type: "number",validation : {required: true}},
                                       AccruedInt: {type: "number",validation : {required: true}},
                                       RemarkFee: {validation : {required: true}},
                                       LiquidFee: {validation : {required: true}},
                                       TrustFee: {validation : {required: true}},
                                       ResidualCusip: {validation : {required: true}},
                                       FloaterCusip: {validation : {required: true}},
                                       UnderlyingCusip: {validation : {required: true}},
                                       ExecBroker: {validation : {required: true}},
                                       dirty:  {validation : {required: true}},
                                       TotalAmount: {type: "number",validation : {required: true}}
                                     }
                                 }
                             },
                              group: {
                                        field: "ResidualCusip", aggregates:
                                        [
                                         { field: "ResidualAmount", aggregate: "sum" },
                                         { field: "UnderlyingPar", aggregate: "sum" },
                                         { field: "FloaterAmount", aggregate: "sum" }
                                        ]
                              },
                              aggregate: [   { field: "ResidualAmount", aggregate: "sum" },
                                             { field: "UnderlyingPar", aggregate: "sum" },
                                             { field: "FloaterAmount", aggregate: "sum" }
                                         ]
                 }),
  7. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 09 Oct 2012 Link to this post

    Hi Vincent,

    I tried a similar scenario and got everything to work as expected. Can you please check my test page and let me know what I am missing? Can you reproduce the problem in the example and send me back a link?

    Regards,
    Alexander Valchev
    the Telerik team
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  8. axwack
    axwack avatar
    37 posts
    Member since:
    Aug 2012

    Posted 09 Oct 2012 Link to this post

    Thank you Alex.

    When I tried this, I still have to enter Bar and Foo in order to group. Is it possible to be in a grouped "area" and press Add and then the system will know that you are adding to that group section?
  9. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 09 Oct 2012 Link to this post

    Hello Vincent,

    I am afraid that what you would like to achieve is not supported.
    The newly inserted row will be added to the corresponding group after the user fills in the fields and saves the records.

    Kind regards,
    Alexander Valchev
    the Telerik team
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  10. axwack
    axwack avatar
    37 posts
    Member since:
    Aug 2012

    Posted 09 Oct 2012 Link to this post

    Is there an event that I could hook into and do the grouping on my own?
  11. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 12 Oct 2012 Link to this post

    Hi Vincent,

    You could hook up to the change event of the DataSource and set the value of the corresponding field. As an example:
    change: function(e) {
        if(e.action && e.action === "add") {
            var model = e.items[0];
            model.set("bar", "B"); // add the record to group bar: "B"
        }
    }

    Also if you want to always add the records to one particular group, you could define the defaultValue of this field in the DataSource schema.
    schema: {
        model: {
            fields: {
                foo: { type: "number" },
                bar: { type: "string", defaultValue: "A" } // always insert the record in group bar: "A"
            }
        }               
    }

    The value that is set in the DataSource change event will override the defaultValue.
    Here is a link to the updated jsFiddle: http://jsfiddle.net/valchev/3M2XR/42/

    Regards,
    Alexander Valchev
    the Telerik team
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  12. krisdoff
    krisdoff avatar
    12 posts
    Member since:
    Nov 2006

    Posted 13 Dec 2013 Link to this post

    Hi there,

    I am also hoping to be able to have a grid such that if the user groups by a column the group footers can have the same totals as the main grid footer.
    Usually I define the aggregates in the datasource as per most examples, so I was interested to see that you could define them along with the field, however the following doesn't appear to work; even if I modify your jsfiddle:

     aggregates: ["sum", "average"],
     groupFooterTemplate: "Sum: #= sum # || Average: #= average #",
    footerTemplate: "Sum: #= sum # || Average: #= average #"

    It appears the footerTemplate complains that sum has not been defined.

    Any help, perception correction appreciated ;)

  13. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 13 Dec 2013 Link to this post

    Hi Krisdoff,

    The footerTemplate displays dataSource's aggregates, e.g. the one defined in dataSource.aggregate.
    The groupFooterTemplate displays Grid group's aggregates, e.g. the one defined in columns.aggregates.

    Most probably you are receiving the error because the corresponding aggregates are missing from the dataSource.aggregate array.
    Putting the code in the jsFiddle sample seems to work as expected because the aggregates are included in the DataSource configuration:
    Am I missing something?

    Regards,
    Alexander Valchev
    Telerik
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  14. krisdoff
    krisdoff avatar
    12 posts
    Member since:
    Nov 2006

    Posted 13 Dec 2013 Link to this post

    Hi Alexander,

    Thanks that was useful. 

    I have now added aggregates both in the datasource, and along with each field and its group footer template in the columns section.

    If I allow grouping and manually group it works fine, the group footer reflects the footer totals.

    However, I need to be able to toggle the grouping programmaticaly in javascript.  I can add the group no problem, but it fails indicating the aggregates are missing.

    A little digging and it seems that when you manually drag the column to be grouped the aggregates are present:

    var groups = grid.dataSource.group();
    console.log(groups[0].aggregates); // shows all the aggregates that were defined against each column:

    [Object { field="StaffMember_Name", aggregate="count"}, Object { field="TotalPreShiftHours", aggregate="sum"}, Object { field="TotalPreShiftHours", aggregate="average"}, Object { field="TotalBreakHours", aggregate="sum"}, Object { field="TotalBreakHours", aggregate="average"}, Object { field="TotalWaitingHours", aggregate="sum"}, Object { field="TotalWaitingHours", aggregate="average"}]

    but if I programatically add:

    grid.dataSource.group([{ field: "MyGroup" }]);

    The aggregates are missing.  Do I need to copy them across somehow from the grid?

    Many thanks
  15. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 18 Dec 2013 Link to this post

    Hello Krisdoff,

    You should pass the aggregates to the group method. For example:
    grid.dataSource.group({
        field: "bar", aggregates: [
            { field: "foo", aggregate: "sum" },
            { field: "foo", aggregate: "average"}
        ]
    });


    Regards,
    Alexander Valchev
    Telerik
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
Back to Top
Kendo UI is VS 2017 Ready