Dynamic Page Breaks in SSRS: Going to Eleven

Sometimes group bands are a real PITA. 

Page breaks on group are even worse. Underneath, no matter how they’re coded in a report engine (any report engine), in some scenarios their assumptions and expectations collide with yours, or your users’.

Ryan Liljestrand wrote to ask:

I have three groups and have followed the steps in your 6/3/07 MSDN post.  This works great except for one thing… when I choose to page break on Group_3, each Group_2 and Group_1 footer appears on its own page.

I got around this in Crystal Reports by using a conditional page break and the NEXT function to see where the group I was; however, SSRS doesn’t offer a similar function.  It does offer a PREVIOUS function, but I get an error that an aggregate cannot be used in a group.

I’m going to cut out some cross-talk here, in which it turned out that Ryan’s problem wasn’t as straightforward as it may sound above.  Suffice to say, I did a number of different renditions for Ryan, each of which didn’t meet his needs in one way or another.  With one thing or another, I did 10 revisions of his original example before I got everything right.

You gotta understand, in these pro-bono advice engagements, there is a little thing called “gathering requirements” that doesn’t get done thoroughly.  There are aspects to the task that I assume the user will know how to do (or the developer will know how to extend) themselves, so I don’t fully work things out.  And then there are details of the results that the user assumes I will know are critical, just by looking at an example or report design, so s/he doesn’t express them. 

On both sides, we are probably expecting too much of each other. 

Oh, wait. It’s not only pro-bono advice engagements that have this issue. Is it?  

Early on, it appeared that the major issue was in getting the right group totals.  If you put a conditional page break group outside the “real groups”, as I did in the MSDN post to which Ryan refers above, this forces your “real groups” to break, and your group totals will only include the values inside the page break group instead of the full aggregate for the group.

I tried a lot of fancy ways around this, including (believe it or not) a subreport whose whole purpose was to supply subtotals.  That was iteration 3, and it actually worked pretty well, I thought. It took the calculation of subtotals out the current report, where they were no longer hampered by the group breaks.

Whether that solution was a brilliant idea in terms of performance I’m not going to say, considering Ryan wrote back saying the result still wasn’t right.  He wanted the headers for all three of his groups to repeat on page, but he didn’t want the subtotal footer lines to repeat except when the group was actually finished, whether it appeared on multiple pages or not.

I went nine rounds with this, and finally said “to heck with trying to get SSRS groups to do something they don’t want to do”.

When groups aren’t working… don’t use ’em.

You never really need group bands.  You can almost always figure out what logic you wished they were using, in terms of “when should a row display”, and apply it yourself, on a detail row.  That’s what I ended up doing here.

You also really don’t need the report to aggregate data based on groups.  You can always get SQL to get you the subtotals you need, and display them appropriately in your ersatz group bands (aka conditionally-appearing detail rows).  Sure the SQL has to be slightly fancier, but it’s probably going to be slightly faster too.

Before we begin

A propos of nothing…

I’m not exactly sure what report expression gave Ryan that error he mentioned, but it’s plausible. 

You can sometimes get around it by using the exact same behavior as the aggregate function would have done by replacing it with a very small amount of custom code to accumulate or calculate values. 

While this tactic wouldn’t have resolved Ryan’s layout requirements in this instance, it’s relevant to my general point in this post: sometimes you have to think like the report engine (or how you wish the report engine would think), and do the work yourself, rather than relying on the exposed functionality in the Designer.

 

Walkthrough for Rendition 10

Ryan very kindly supplied a sample report and stored procedure based on AdventureWorks at my request, so I could share all this with you. 

The three groups are Department, Manager, Employee. A parameter specifies on which group (or none) he wants to pagebreak for a given run.

Here’s his stored procedure:

USE AdventureWorks;
GO
 
— Set up data: move one manager to another department
UPDATE HumanResources.EmployeeDepartmentHistory
   SET EndDate = ‘2005-12-31’
   WHERE EmployeeID = 273
      AND DepartmentID = 3;
IF NOT EXISTS(SELECT *
   FROM HumanResources.EmployeeDepartmentHistory
   WHERE EmployeeID = 273
   AND DepartmentID = 4)
   INSERT HumanResources.EmployeeDepartmentHistory
   (EmployeeID, DepartmentID, ShiftID, StartDate, EndDate, ModifiedDate)
    VALUES (273, 4, 1, ‘2006-01-01’, NULL, ‘2006-01-01’);
IF OBJECT_ID(‘SalesOrders’) IS NOT NULL
   DROP PROCEDURE SalesOrders;

GO  

CREATE PROCEDURE SalesOrders
   AS
BEGIN

   SELECT DEPTID.DepartmentID AS DepartmentID,
      DEPT.Name AS Department, 
      MGR.EmployeeID AS ManagerID,
      SUBSTRING(MGR.LoginID, 17, LEN(MGR.LoginID)) AS Manager,
      EMP.EmployeeID AS EmployeeID,
      SUBSTRING(EMP.LoginID, 17, LEN(EMP.LoginID)) AS Employee,
      SOH.SalesOrderID AS Orders
   FROM Sales.SalesOrderHeader SOH
   LEFT JOIN HumanResources.Employee EMP
      ON EMP.EmployeeID = SOH.SalesPersonID
   LEFT JOIN HumanResources.Employee MGR
      ON MGR.EmployeeID = EMP.ManagerID
   LEFT JOIN HumanResources.EmployeeDepartmentHistory DEPTID
      ON DEPTID.EmployeeID = MGR.EmployeeID
         AND DEPTID.EndDate IS NULL
   LEFT JOIN HumanResources.Department DEPT
      ON DEPT.DepartmentID = DEPTID.DepartmentID
   WHERE SOH.SalesPersonID IS NOT NULL
      AND SOH.SalesOrderID BETWEEN 50000 AND 59999
   ORDER BY Department,
      Manager,
      Employee; 
END — SalesOrders

GO
 
–The result looks like this:

DepartmentID	Department	ManagerID	Manager	EmployeeID	Employee	Orders
4	Marketing	273	brian3	284	amy0	55274
4	Marketing	273	brian3	284	amy0	50244
…
4	Marketing	273	brian3	284	amy0	59064
4	Marketing	273	brian3	268	stephen0	55298
…
4	Marketing	273	brian3	288	syed0	59045
3	Sales	284	amy0	285	jae0	55243
…

 

The first thing I noticed about Ryan’s report is that the inner group on Employees wasn’t actually a standard group at all. He was hiding the details rows, using the counterintuitively named Visibility property, and using a footer line with CountDistinct(Fields!Orders.Value) in it.  In other words, he only showed a count per employee, not individual orders, in his report.  The effect is similar to a REPORT FORM … SUMMARY command, for those of you who have used the VFP Report system at some point.

Instead of using the Visibility property and adding Group_3 for Employee, Ryan could have used a detail-level group, selected footer, and taken out the detail row entirely.  As it turned out, that part didn’t really matter, but it’s worth mentioning that you really don’t need the report to evaluate Visibility on every details row if you just want to tell it “no details”.  This is one of the cases in which detail grouping really makes sense, even though it’s a weird concept.  

Report result sets are often severely denormalized.  I knew exactly what I wanted in terms of nested totals, all I had to do was get them in the right rows so that they were easily accessed.  The fact that they would also appear on rows where they were not needed was not a big concern.  (Sure, it’s a waste.  So is a subreport.)  So here’s my revision (try not to faint, or even to get over-involved, the details are specific to the example):

USE [AdventureWorks]
GO

/****** Object: StoredProcedure [dbo].[SalesOrders] Script Date: 03/08/2009 10:07:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SalesOrders]
AS
BEGIN

   ;WITH CountCompare AS (
      SELECT DEPT.Name AS Department,
         MGR.LoginID As Manager,
         EMP.LoginID AS Employee,
         Row_Number() OVER ( PARTITION BY DEPT.Name, MGR.LoginID, EMP.LoginID
            ORDER BY DEPT.Name,MGR.LoginID,EMP.LoginID) AS OrderRowForEmployee,
         Row_Number() OVER ( PARTITION BY DEPT.Name, MGR.LoginID
            ORDER BY DEPT.Name,MGR.LoginID,EMP.LoginID) AS EmployeeRowForManager,
         Row_Number() OVER ( PARTITION BY DEPT.Name
            ORDER BY DEPT.Name,MGR.LoginID,EMP.LoginID) AS ManagerRowForDepartment
      FROM HumanResources.Employee EMP
      JOIN Sales.SalesOrderHeader SOH ON EMP.EmployeeID = SOH.SalesPersonID
      LEFT JOIN HumanResources.Employee MGR ON MGR.EmployeeID = EMP.ManagerID
      LEFT JOIN HumanResources.EmployeeDepartmentHistory DEPTID
         ON DEPTID.EmployeeID = MGR.EmployeeID AND DEPTID.EndDate IS NULL
      LEFT JOIN HumanResources.Department DEPT
         ON DEPT.DepartmentID = DEPTID.DepartmentID
      WHERE SOH.SalesPersonID IS NOT NULL
         AND SOH.SalesOrderID BETWEEN 50000 AND 59999
   )

   SELECT DEPTID.DepartmentID AS DepartmentID,
      DEPT.Name AS Department,
      MGR.EmployeeID AS ManagerID,
      SUBSTRING(MGR.LoginID, 17, LEN(MGR.LoginID)) AS Manager,
      EMP.EmployeeID AS EmployeeID,
      SUBSTRING(EMP.LoginID, 17, LEN(EMP.LoginID)) AS Employee,
      SOH.SalesOrderID AS Orders,
      Row_Number() OVER ( PARTITION BY DEPT.Name,MGR.LoginID,EMP.LoginID
         ORDER BY DEPT.Name,MGR.LoginID,EMP.LoginID) AS OrderRowPerEmployee,
      Row_Number() OVER ( PARTITION BY DEPT.Name, MGR.LoginID
         ORDER BY DEPT.Name,MGR.LoginID,EMP.LoginID) AS OrderRowPerManager,
      Row_Number() OVER (PARTITION BY DEPT.Name
         ORDER BY DEPT.Name,MGR.LoginID,EMP.LoginID) AS OrderRowPerDepartment,
      CountOrdersPerEmployee,
      CountOrdersPerManager, 
      CountOrdersPerDepartment
   FROM Sales.SalesOrderHeader SOH
   LEFT JOIN HumanResources.Employee EMP ON EMP.EmployeeID = SOH.SalesPersonID
   LEFT JOIN HumanResources.Employee MGR ON MGR.EmployeeID = EMP.ManagerID
   LEFT JOIN HumanResources.EmployeeDepartmentHistory DEPTID
      ON DEPTID.EmployeeID = MGR.EmployeeID AND DEPTID.EndDate IS NULL
   LEFT JOIN HumanResources.Department DEPT
      ON DEPT.DepartmentID = DEPTID.DepartmentID
   JOIN
      (SELECT Department, Manager , Employee,
                  Max(OrderRowForEmployee) As CountOrdersPerEmployee
         FROM CountCompare
        GROUP BY Department, Manager,Employee) EmpC
      ON Dept.Name = EmpC.Department AND MGR.LoginID = EmpC.Manager AND
           EMP .LoginID = EmpC.Employee
   JOIN
      (SELECT Department, Manager ,
                  Max(EmployeeRowForManager) As CountOrdersPerManager
         FROM CountCompare
         GROUP BY Department, Manager) MgrC
      ON Dept.Name = MgrC.Department AND MGR.LoginID = MgrC.Manager
   JOIN
      (SELECT Department,
                  Max(ManagerRowForDepartment) As CountOrdersPerDepartment
         FROM CountCompare
         GROUP BY Department) DeptC ON Dept.Name = DeptC.Department
         WHERE SOH.SalesPersonID IS NOT NULL AND
            SOH.SalesOrderID BETWEEN 50000 AND 59999
   ORDER BY Department, Manager, Employee ;
 

END — SalesOrders

— here are the additional columns that I’ve added to Ryan’s
— original result set (just tack them on to the end of the table
— you saw earlier):

Orders	Order Row Per Employee	Order Row Per Manager	Order Row Per Department	Count Orders Per Employee	Count Orders Per Manager	Count Orders Per Department
55274	1	1	1	21	40	40
50244	2	2	2	21	40	40
…
59064	21	21	21	21	40	40
55298	1	22	22	10	40	40
…
59045	9	40	40	9	40	40
55243	1	1	1	124	256	1107
…

Before you say anything about how you could improve the above procedure… I know you could.  And so can Ryan, especially given that Ryan doesn’t happen to need the individual orders in this case.

I wasn’t fixated on the best possible code here.  I am not overly intimate with the AdventureWorks schema, and am not invested in it.  There’s no need, because  AdventureWorks probably looks nothing like Ryan’s real data set (or yours).  Besides, maybe in real life sometimes Ryan does need the individual orders, or their equivalent, even though in the example he didn’t. 

All I’m concerned about is what you see in the final comment in the code listing above: I want to hand the right results to my report definition, so it doesn’t have to figure much out.  How you get there, in your data schema, is your business, and might be entirely different.  OK so far?

Now we have the information that our report needs to figure stuff out.  Keep in mind that what we’re doing here is probably very similar, logic-wise, to what group bands do “under the covers”; we’re just going to do it manually.

Here is Ryan’s original group setup.  Based on my recommendations in the MSDN forum post, he’s got an outer group handling the page breaks, and he has the group breaks you might expect within it.

You don’t see any bands for the outer group in the layout, because it has no visible result.  It’s just there to provide a single band on which a page break is requested.  If you recall, the conditions for the conditional page break instructions are based on a parameter in this example, so the group expression will look something like this:

=Switch(CInt(Parameters!PageBreak.Value) = 0,””,
        CInt(Parameters!PageBreak.Value) = 1,Fields!Department.Value,
        CInt(Parameters!PageBreak.Value) = 2,Fields!Manager.Value,
        CInt(Parameters!PageBreak.Value) = 3,Fields!Employee.Value)

In my version, I’m using the same parameter condition, of course, and the conditional group break expression is the same one you see above. 

As you can see, I’ve removed the Department, Manager, and Employee groups. I added a group to handle some outermost band chores, rather than using a table header and footer.  This group has a group expression of =””. I’ve used Nothing and 0 or other literals too.

This additional band is not necessarily germane to the conditional page break technique, but I often find it is convenient and makes a table more manageable.  In this case, by the time I got to Rendition 11 (if you have the stamina to keep on reading), you’ll find that I really needed it.

My headers in OuterBandGroup are set to repeat on new page as well, because we want them to.  My footer rows in the same group are not set to repeat on new page — because we actually just want the total orders at the end of the full report.  Accordingly, the Total Orders footer row (please note: not individual textboxes!) has a Hidden expression that looks like this:

=RowNumber(“SalesOrders”) <>
   CountRows(“SalesOrders”)

… a simple expression which indicates that we only want to see this row when we’re at the end of the dataset.

That was pretty easy.  Now we have to accomplish pretty much the same thing on our ersatz group headers and footers which, as you can see in the layout, are all expressed in detail rows.

Remember Ryan’s requirements?  Repeat the headers on page, but don’t repeat the footers on page.  And remember what Ryan said?  RDLs have a PREVIOUS() function.   Given these facts, how does an ersatz header line know to repeat? 

You need to tell each header line that it must be hidden unless either the page has changed or the group break value for this level has changed. 

For a Department level header line, the Hidden property expression looks likes this:

=Previous(Fields!Department.Value) = Fields!Department.Value and
    (RowNumber(“General_PageBreak”) > 1)

The row will show when you’re on the first record, and it will show whenever you’ve changed Departments, no matter what the conditional break situation.  But when you’ve set an inner group to break on page and the Department has not changed, it will not show.

The Manager row header has the same Hidden expression as you see above, except that we’re comparing Fields!Manager.Value expression to its Previous version, instead.

NB: I suppose I could have done something with Hide Duplicates, instead.  But I didn’t think of that.  There’s always more than one way to skin an RDL cat. 

The next row down is the actual “detail row”, which shows the count of orders per employee.  Its Hidden expression says “hide if we’re not on the last record for this employee”:

=Fields!OrderRowPerEmployee.Value <>
   Fields!CountOrdersPerEmployee.Value

Note that, instead of using a CountDistinct RDL function for display purposes, we’re just using the value we got from SQL to in a textbox for this line, which you see in the right side of the Hidden expression above. Similarly, the textboxes in the Manager footer row and the Department footer row have textboxes displaying fields straight from the SQL:

=Fields!CountOrdersPerManager.Value

and 

=Fields!CountOrdersPerDepartment.Value

All that’s left is the suppression of these ersatz group footer lines when we don’t want to see them. We only want them when the theoretical group has changed. As you can probably guess, the Manager footer row has a Hidden expression that looks like this:

=Fields!OrderRowPerManager.Value <> Fields!CountOrdersPerManager.Value

… and the Department footer row Hidden expression is similar.

And it’s all over but the wrangling over grow/shrink/spacing handling in your specific layout.

Rendition 11  

I really thought I was done at this point, but Ryan then added a last-minute curve of sorts:

P.S.  As a side note, I will also be including a ” continued” tag on the subsequent group headers.  I mention it now only because I don’t know how simple/difficult it is to incorporate into the changes already made…

Okay… at this point, what’s an extra hour, right?   I added a textbox into my visible group header, with the following expression in it:

=IIF( (Fields!Department.Value = Previous(Fields!Department.Value)),
      “Department “ + Fields!Department.Value,“”) +
      IIF(((Fields!Department.Value = Previous(Fields!Department.Value)) and
            (Fields!Manager.Value = Previous(Fields!Manager.Value))),” and “, “”) +
      IIf((Fields!Manager.Value = Previous(Fields!Manager.Value)),
         “Manager “ + Fields!Manager.Value,“”) +
      ” continued from the previous page.”

I added a Hidden expression on this textbox — not the row object this time, because I wanted another expression in the same row to continue to show:

=Len(Previous(Fields!Department.Value)) = 0
or ((Fields!Department.Value <>
   Previous(Fields!Department.Value)) and
    (Fields!Manager.Value <> Previous(Fields!Manager.Value)))

This textbox displays:

• Department <Sales> and Manager
  <amy0> continued from the
  previous page.
  or
• Department <Sales> continued from the
  previous page.
  or
• nothing at all
  … as appropriate.

I’ve uploaded RyanPageBreak.zip (55.13 kb) with Ryan’s original example report, my #11 solution RDL and SQL, and PDFs showing the results at each pagebreak level, so you can see what this looks like without setting it up, if you like.

Your mileage may vary, and
you should think again about
which car you should take on this trip 

If your report has text that stretches to the extend that a band may break over pages, I’m sure this is going to get even more complicated.  See https://spacefold.com/lisa/2007/09/22/MaxHeightAvailable-Rules for an example of handling that particular problem, but using both techniques together would, I’m sure, take some more work.

But there’s something that annoys me about this whole line of thinking, and it’s this: when we’re contorting ourselves to get something to work, probably there is an easier and better way.

Ryan says his users want the HTML and printed report to be exactly the same.  I hear the same thing all the time, but it’s basically nonesense.  Different renderers handle group and page breaks as well as they can, within their media, but they will never be exactly the same.  HTML pagination is largely arbitrary, based on a theoretical idea of what will fit on a user’s screen, while Print and PDF page breaks are a function of an absolute page size. An Excel renderer translates explicit page breaks into worksheet tabs in a workbook.  They’re not exactly the same; so what?

Maybe we have to let SSRS be SSRS sometimes.  Maybe we should ask users to consider using SSRS collapse/expand and document map features to focus their their attention on different summary levels of a report, rather than page breaks; these features work very naturally with groups.  Maybe this would be just as good or better at giving them the result they intended when they asked for a page break.  These features don’t translate perfectly to each different renderer, either, but so what? 

Since when was absolute consistency between renditions the real goal? Would users also expect a CSV rendered output to include red for negative numbers?  No, of course not. Neither the CSV renderer nor the XML renderer even provide the content of page header and footer bands; they literally have no concept of page breaks at all.

The world is moving on. To be both green and efficient, even the IRS promotes paperless returns, even state agencies allow submission of sophisticated and complex forms using SOAP calls. It’s time we looked closely at these expectations and figured out how to manage them realistically.