Forum FAQ: How do I get group total in Group Footer with PageBreak in SQL Server Reporting Services?

  • Article

Question

I want to get a total of certain group within a group footer of the Tablix, which has another group for breaking the records on certain number of records. For example,

There is a table with a amount of data and its columns include"CustomerID","CustomerType" and "Totaloutstanding". Now, run SSRS 2005 to report this table data. On this report,

1. In the report dataset, these data are ordered by "CustomerID", one CustomerType has one CustomerID, the CustomerID values like 1,2,3......

2. Insert a Table control to host these data,

3. The data on every page of this report has only less than or equal 1000 rows. To achieve this, insert a group for the detail rows with group-on expression =(RowNumber(Nothing)-1)\1000. Assuming this group is Group1.

4. The data on every page of this report need to be grouped by "CustomerType". To achieve this, insert a group under the Group1 for the detail rows with group-on expression =Fields!CustomerType.value, and tick these options "Include group header","Include group footer" and "Repeat group header". Assuming this group is Group2.

5. (?). How to display the subtotal based on CustomerType at the end of each CustomerType series? Note that one CustomerType might be span multiple pages by Group1, and one page(Group1 instance) might have multiple Group2 instance.

Answer

There are 2 main steps, one is how to calculate the subtotal based on the "CustomerType", and the other is how to hide the Group2 footer rows if they are not at the end of each CustomerType series.

1. The possible alternatives for how to calculate the subtotal based on the "CustomerType", there are 2 options you can follow:

· Use semi hard-code: In the Group2 footer cell, type this expression =Switch(Fields!Customer.Value="Person",SUM(IIF(Fields!Customer.Value="Person",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Company",SUM(IIF(Fields!Customer.Value="Company",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Electronic",SUM(IIF(Fields!Customer.Value="Electronic",Fields!Totaloutstanding.Value,0),"StatisticTbl")) . Note that "StatisticTbl" is the report dataset name, and "Person","Company" and "Electronic" are the "CustomerType" examples.

· Use the subreport to do this:Create a new report, dataset:select CustomerType,sum(Totaloutstanding) as subtotal
from StatisticTbl
where CustomerType= @cust
group by customerType report parameter:name->cust, datatype->string
report body:drag a table control and only one detail cell via deleting other cells, and put the =Fields!subtotal in this cell. Adjust this report page size to fit on one cell of the main report Group2 footer. On the main report, in the cell displaying the subtotal of "CustomerType" of Group2 footer, insert a Subreport control to reference this newly created report and set this subreport parameter "cust" point to =Fields!CustomerType.value.

2. After implementing any option of the first step, the subtotal based on the "CustomerType" will be displayed in each Group2 footer. Now, how to hide these footer rows not at the end of the "CustomerType" series? Click the handle of the Group2 footer row to select the entire row, and locate its Hidden property under Visibility note in the Properties box, then type the following expression to hide the footers not at the end of each "CustomerType":

=IIF(Runningvalue(Fields!CustomerType.Value,countdistinct," Group1 ")=1,IIF(Count(Fields!CustomerType.Value," Group2 ")=1000,true,false),false)