Subtotals by Group
Hi all - Strange and hopefully easy problem to solve: I have a report with 4 groups. The first I added as a parent to the detail group, the second as a child to the first group, the 3rd as a child to the 2nd, and the 4th as a top-level parent. For the first 3 groups I manually entered subtotal calculations based on the group (e.g. =sum(Field1.value, "GROUP1") as the "Add Total" didn't work out correctly. Anyway, I did the same subtotal calc for the 4th (top level) group, but when rendered, it is showing the values under its child group calc, instead of at the end of the report (where it should be, as the top level group). Any ideas? Bonediggler
October 12th, 2012 5:28pm

Hi Bonediggler, Based on your description, I try to reproduce the issue in my test environment. However it works well. To add totals for a group, we can click Add Total on the shortcut menu for the group in the Grouping pane. For example, when we select a row group and click Add Total, a new row outside the current group is added to the data region, and then a default total is added for numeric field in the row. If the row group has parent group named ParentGroup, we can get the same result by using the following expression:=SUM(Fields!numeric_field.Value,ParentGroup). (Note: the Tablix cell which displays the above value should be in the scope of ParentGroup group.) When we add totals in a Tabilx data region for the entire data region, we can click Add Total on the outmost group in the Grouping pane or use the following expression:=SUM(Fields!numeric_field.Value,DataSet). For more information about add total to a group or a Tablix data region, please see: http://msdn.microsoft.com/en-us/library/bb934417(v=sql.100).aspx The following screen shot shows the design structure and preview of my test report, please take as reference. If the issue persists, please share a screen shot of your report structure, so we can help to work out this issue. Regards, Fanny LiuFanny Liu TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
October 15th, 2012 12:52am

Hi Bonediggler, Based on your description, I try to reproduce the issue in my test environment. However it works well. To add totals for a group, we can click Add Total on the shortcut menu for the group in the Grouping pane. For example, when we select a row group and click Add Total, a new row outside the current group is added to the data region, and then a default total is added for numeric field in the row. If the row group has parent group named ParentGroup, we can get the same result by using the following expression:=SUM(Fields!numeric_field.Value,ParentGroup). (Note: the Tablix cell which displays the above value should be in the scope of ParentGroup group.) When we add totals in a Tabilx data region for the entire data region, we can click Add Total on the outmost group in the Grouping pane or use the following expression:=SUM(Fields!numeric_field.Value,DataSet). For more information about add total to a group or a Tablix data region, please see: http://msdn.microsoft.com/en-us/library/bb934417(v=sql.100).aspx The following screen shot shows the design structure and preview of my test report, please take as reference. If the issue persists, please share a screen shot of your report structure, so we can help to work out this issue. Regards, Fanny LiuFanny Liu TechNet Community Support
October 15th, 2012 12:52am

Hi Bonediggler, Based on your description, I try to reproduce the issue in my test environment. However it works well. To add totals for a group, we can click Add Total on the shortcut menu for the group in the Grouping pane. For example, when we select a row group and click Add Total, a new row outside the current group is added to the data region, and then a default total is added for numeric field in the row. If the row group has parent group named ParentGroup, we can get the same result by using the following expression:=SUM(Fields!numeric_field.Value,ParentGroup). (Note: the Tablix cell which displays the above value should be in the scope of ParentGroup group.) When we add totals in a Tabilx data region for the entire data region, we can click Add Total on the outmost group in the Grouping pane or use the following expression:=SUM(Fields!numeric_field.Value,DataSet). For more information about add total to a group or a Tablix data region, please see: http://msdn.microsoft.com/en-us/library/bb934417(v=sql.100).aspx The following screen shot shows the design structure and preview of my test report, please take as reference. If the issue persists, please share a screen shot of your report structure, so we can help to work out this issue. Regards, Fanny LiuFanny Liu TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
October 15th, 2012 1:10am

Hi Fanny- Using "Add Total" worked...thank you. That wasn't the case before - not sure what I was doing wrong.Bonediggler
October 15th, 2012 3:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics