SSRS Page break every 1000 rows on group

Question

I have the following table of data from an MDX query that resembles the following:

Account | Location | Type | Person | Amount
ABC | XYZ | AA | Tom | 10
ABC | XYZ | AA | Dick | 20
ABC | XYZ | AA | Harry | 30
ABC | XYZ | BB | Jane | 50
ABC | XYZ | BB | Ash | 100
DEF | SDQ | ZA | Bob | 20
DEF | SDQ | ZA | Kate | 10
DEF | LAO | PA | Olivia | 200
DEF | LAO | PA | Hugh | 120

And I need to add the Amount column for each Account, Location, and Type. If I was using SQL I would perform a query on this data as follows:

Select Account, Location, Type, Sum(Amount) as SumAmmount
From Table
Group By Account, Location, Type

but due to the way we store the data I need to roll-up this data using SSRS. To do that I created a tablix, created a parent group (Which I have called "RollUp") of the default detail group which grouped on Account, Location, and Type and then deleted the detail group so when running the report I get:

Account | Location | Type | Amount
ABC | XYZ | AA | 60
ABC | XYZ | BB | 150
DEF | SDQ | ZA | 30
DEF | LAO | PA | 320

What I need to do now is create a page break so that when I export this SSRS report to excel there are only 1000 rows on each sheet, but I am having trouble writing the expression to split this every 1000 rows. Because I have removed the details group I cannot use the typical expression I would use to page break on a specific row of a dataset (e.g. Group Expression = Floor(RowNumber(NOTHING) / 1000) )

I have tried a few different things like writing some custom code and some running value expressions but haven't been able to figure it out yet.

Palps Palps · Accepted Answer · 2014-08-04T23:48:40

I did figure out how to do this.

First I created the following custom code in the report definition:

Dim GroupingDummy = "GroupDummy"

Dim RowNumberToReturn = -1

Function PopulateRowNumber(GroupString As String) As Integer
    If (GroupString <> GroupingDummy ) Then
        GroupingDummy = GroupString 
        RowNumberToReturn = RowNumberToReturn + 1
    End If
    Return RowNumberToReturn
End Function

Keeping in mind the grouping I applied to the dataset used the fields Account, Location, and Type, I added a calculated field to my dataset with the name RowNumberCalc and the expression:

=Code.RowNumberToReturn(Fields!Account.Value + Fields!Location.Value + Fields!Type.Value)

Now I could easily create the group that would create a page break at 1000 rows with the expression :

=Floor(Fields!RowNumberCalc.Value / 1000)

SSRS Page break every 1000 rows on group

1 Answers