TechSpoken

"Any ideas?" is the most frequently-asked question in technical forums. My answer is: yes.

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.

parameter for the conditional page break test report

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.

Ryan's original page break scheme

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. 

LSN's revision of grouping for the sample report

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 http://spacefold.com/lisa/post/2007/09/22/MaxHeightAvailable-Rules.aspx 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.

Comments (42) -

  • 4396510

    4/27/2009 8:03:06 PM |

    Page break is not recommended.But you can go with dynamic page breaks.Its very useful.Thanks for this wonderfull article

  • nintendo ds r4

    6/30/2009 10:22:14 PM |

    Hi,

    Nice article....I have been saying for a long time that most people don't really need dynamic pages. Take this very page for example; the only dynamic piece here is user comments. If there is no expecation from the user to see changes reflected immediately, then why both reconstructing the whole thing?

  • club penguin cheats

    7/4/2009 8:33:34 PM |

    I am happy that Dynamic Page Breaks in SSRS is going to be updates.

  • Shankar.C

    9/26/2009 9:39:44 PM |

    Hi,

    Im having problem in setting page break based on grouping,ie i need to set page break for every 20 rows, when im having single groping its working fine , how to set page break for 3 grouping ... need some idea towards this issue....
                                             regards
                                             Shankar

  • >L<

    9/26/2009 10:21:47 PM |

    Yes,this definitely can be done.  And is probably simpler than the thing solved in this post.  But, as usual, needs a specific resolution for a specific requirement.  

    OK, there are three groupings and you want to break every 20 detail lines? How do group header and footer lines appear in this report?  Do they vary in number (visibility) or are they fixed? etc.

    Details please.

  • Shankar.C

    9/27/2009 1:40:55 AM |

    Hi,
      Thanks for reply. In my report for every group no of details wil vary. also one grouping is inside another grouping.


    like

      Month
         Customer
            Product
                here detais of product comes....

                                               regards,
                                               Shankar.C

  • >L<

    9/27/2009 2:05:51 AM |

    Of course one group is "inside" another. I didn't ask about that, nor about the number of details per group.  I asked about group header and footer lines and whether (using visibility expressions) their number varies as well?  When you count "every twenty" is it specifically 20 detail lines or 20 lines of any type, including these headers and footers?

    >L<

  • Shankar.C

    9/27/2009 2:40:17 AM |

    im having 20 lines of any type....

  • Shankar.C

    9/27/2009 2:52:59 AM |

    That 20 rows should include  group header and footer also...

  • >L<

    9/27/2009 9:04:41 AM |

    Hi again...
    >> should include group header and footer

    I'll give this a try, but I think I may still need the full answer to my question: does the number of group header and footer lines vary (using visibility or any other reason) from break to break?

  • >L<

    9/27/2009 10:40:48 AM |

    BTW -- I know I have done this with multiple groups before and explained it in various contexts.  You're not giving me a lot of incentive to do it again, because I don't know what is special about your case.

    I don't even know for sure what version of RS you're using? (If it's 2008 I can use report variables, if 2005, only code variables, etc, and the evaluation at runtime seems to proceed in different orders in someo cases?)

    At this point I have spent a little time trying to dummy up a report, given the skimpy information I have.  In doing this, I can see there are still many possibilities and requirements unaccounted for.  You can either make me up a sample report with the MySQL database script I commonly use around here, or use AdventureWorks, or give me much more complete information than you have.  

    Or, hey, if you want, you can decide LSN was just afraid of the challenge and walk away grumpily <g>.  

    Honestly, I don't think that's it.  I just don't like wasting my time (especially when nobody's paying me to do it <g>).  If it's worth my time, it's worth yours to give me the basis with which to work.  It's heartbreaking for me to work up one of these walkthrough's and then hear the objection "but you didn't work to my case".  Give me your case.

    >L<

  • Shankar.C

    9/28/2009 12:16:06 AM |

    Hi,
      
        I didnt lik to waste ur time.Sorry for disturbance,In my case some times the visibility of the groups may vary.... but the no of group headers and footers wont vary in that report.Im having nearly 30 reports having single groups to upto 4 groups.
    Any way thanks for ur efforts and replies.

  • Shankar.C

    12/13/2009 3:41:01 AM |

    Hi Lisa,

    Once again Shankar.C after few months on the same issue(page break on every 20 rows when having more than 2 groups)... i didnt arrived at the solution yet...can u giv me some valuable idea....

    regds,
    Shankar.C

  • Spywear Removal

    12/16/2009 5:44:43 AM |

    Gratitude for the tips in dynamic page breaks in SSRS. Helps a lot. Thanks. Bookmarked this blog for future references.

  • dentist hungary

    12/21/2009 4:50:21 PM |

    Informative info on SRSS. Thank you for your work. Bookmarked it.

  • work in canada for foreigners

    12/30/2009 10:18:29 AM |

    Its just recent that I learned about page breaks. Would enhance my skills in 2010. I will devote more time to be excellent. Thanks. Kudos to web enthusiasts!

  • philadelphia tummy tuck surgery

    1/17/2010 1:17:14 PM |

    Wow, detailed. Love it. Thanks for sharing Ryan's problem as I had the same one, and your explanation helped.

    Thanks for sharing.

  • Uitvaartverzekeringen

    1/30/2010 3:06:51 AM |

    I had some problems with my Crystal Reports software. After reading this article i found the solution to these problems.

  • Karthik

    2/7/2010 11:31:23 PM |

    Hi Lisa,

    Thanks for the superb article. I have a requirement something similar to this and I am working on it. I am facing an "Cannot find the file RConditionalPageBreak.rdl" error when I am trying to open the solution or the project.

    Could you please share the working file/let me know how i can eliminate the error?

    Regards,
    Karthik

  • >L<

    2/7/2010 11:54:42 PM |

    Hi Karthik,

    That RDL is in the zip.  It's in the Ryan Original\AdventureWorks subdir in the zip.  Can you check again please?  Thanks for the strokes!

  • Karthik

    2/8/2010 12:10:19 AM |

    Hi Lisa,

    i am able to see the folder directories and all the files within but I get an error when I try to open the'ConditionalPageBreak.rdl' file through BIDS. I am able to succesfully load the AdventureWorks.sln file though.

    The exact error message being - Could not find the file 'C:\Karthik\SSRS Task\PageBreak\ryan original\adventureworks\ConditionalPageBreak.rdl' file.


  • >L<

    2/8/2010 12:24:48 AM |

    Hi Karthik,

    There is no "ConditionalPageBreak.rdl".  There is a "RConditionalPageBreak.rdl" (look at the first letter).  You can either rename the RDL or change the name of the RDL in the Ryan Original project.  Believe me, it's Ryan's RDL, and all the other files, including the sln, just as he gave them to me <s>.   I just renamed his RDL while working to make sure I didn't get confused -- I created so many different versions of the thing.

  • Karthik

    2/8/2010 12:47:29 AM |

    Hi Lisa,

    Thanks a lot. I don't know how I missed that. Smile Well, I checked the file and its working fine. It is now I realize my requirements are slightly different.

    My actual task is to display totals of each group at the end of page breaks which are further grouped on count of 1000. I don't know how to proceed with this issue. I have also queried the same at MSDN forums at the following links but haven't been getting any responses at all.

    social.technet.microsoft.com/.../b3ea0253-bb5a-463d-82e8-4e9d1d9eed3a

    Could you please provide any tips/suggestions on the way to deal with this task?

    Thanks for your time again.

  • >L<

    2/8/2010 11:31:48 AM |

    Hi Karthik,

    >>at the end of page breaks which are further grouped on count of 1000

    I am not sure what "further" means in this context.  But if you draw me a diagram or something and email it to me (see the contact page), I'll try to help.

  • Karthik

    2/9/2010 1:09:38 AM |

    Hi Lisa,

    First off thanks a lot. As suggested, I have sent one document through Contact Us explaining about the issue i am facing. When I clicked on Send button the page got refreshed but I haven't seen any confirmation message if its been successful or not and all the other textboxes values remained same. I then had resent with the attachment. Again, the same thing followed.

    So, if successful you might be getting 2 mails. if not do let me know, i will try to explain it bit more clearly through text.

    Thank you,

  • >L<

    2/9/2010 1:21:24 AM |

    Hmm.... what's "Contact Us"?  Just to make sure, I'm talking about http://spacefold.com/lisa/contact.aspx, is that where you were?

    Did you fill out your name/email/subject with some content in the editbox, as opposed to just putting in the attachment?  It seems to work okay, but it is required to fill out the form.  

  • Karthik

    2/9/2010 1:26:24 AM |

    Err, that was a typo. But, yes that's the same page from where I tried sending by filling all the mandatory fields with subject/mail/name and attachment. Anyways, I did send the response to other mail I received from you.

    Thank you.

  • starterslening koopsubsidie

    3/23/2010 7:05:13 AM |

    The footer rows in the exact same group were set to repeat on every new page. I wanted the total orders at the end of the full report. That was wrong. here I learned that footer rows in the same groups are just set NOT to repeat on every new page.

  • Leadership Styles

    5/12/2010 12:31:22 PM |

    page break for every 20 rows, when im having single groping its working fine , how to set page break for 3 grouping ... need some idea towards this issue....

  • Ramdas

    7/27/2010 1:26:05 PM |

    Hi,
    Thanks for the very detaild report. The article explains the scenario and solutions in great detail. I have a question, i have report that has 3 groups on the rows (Group1,group2,group3). I want to have alternate row colors across the report, any ideas...

    Thank you

  • Ceiling Fan

    10/26/2010 5:25:42 PM |

    Thanks for the very detaild report..  Had a similar issue I was working on.  Sometimes SQL can be tricky, but just break it up into smaller problems helps.  Thanks for sharing the solution.

  • >L<

    11/26/2010 12:19:03 PM |

    Shane: what happens when you ask your clients *why* they don't like your ideas Wink?

  • >L<

    5/14/2011 12:06:44 PM |


    this is not exactly I am looking for but your post here has inspired me to deal with one of my problems
    Well, if there is anything I can do to help, pose the question and I'll try.

  • iRohan

    5/18/2011 10:26:35 PM |

    I am new in SSRS . I want to create a Business Document for Business Transaction which repeat all its Data on Every Page . How can i do this task in SSRS. Please tell me Steps for to do this task.

  • LSN

    5/20/2011 2:21:14 PM |

    iRohan,

    If you are a real person, you can write to me with a sample data set and some more detail on what you need to do, and I'll help.

    >L<

  • Ayurveda Music

    5/29/2011 9:36:25 AM |

    I used this SSRS but I have the difficulty of fixing the bugs. Can i ask for help?

  • Anfänger Gitarre

    6/4/2011 11:06:06 AM |

    I see how pagebreaks can give you a hard time, but did you ever have to fight with encodings? God, they're a REAL pain in the butt, I'm telling you..

    Especially if you're using different programs of which you only know partially what encoding they're utilising... -_-

  • Stal Narzędziowa

    6/12/2011 10:46:05 AM |

    Eveyrthing working as expected, simple copy and paste and it's working Smile)

  • φωτοβολταικα

    4/9/2012 6:14:08 AM |

    Thanks for the code! Very comprehensive article!

Pingbacks and trackbacks (1)+

Comments are closed