I am seriously annoyed. 

In http://spacefold.com/lisa/post/2010/02/14/YAPS-on-Dynamic-PageBreaks-Mixing-up-the-Groups.aspx I did a lot of work I didn't need to do.  Karthik needed an RS 2005 solution and I gave him an RS 2008 solution, not having resources at hand to figure out that tables and tablixes (is that even a word?) behave entirely differently in this scenario.

As Karthik explains in his comments to that post, when he tried to use a "lower down" group, as I instructed, for the purpose of breaking on his break number, he got a page break every "outer" group (in my example, CountryCode), even though the outer group didn't specify a pagebreak on that level.  This just doesn't happen with a tablix.

So, okay.  What's a guy to do? 

Follow a girl's earlier advice; see http://spacefold.com/lisa/post/2009/03/08/Dynamic-Page-Breaks-in-SSRS-Going-to-Eleven.aspx.  

All together now:

When groups aren't working... don't use 'em.

The reason I'm annoyed is that the resolution I'm going to show you is what I expected to do for Karthik, but it didn't work in RS2008. It is much easier than what I ended up showing you, IMHO,  easier to understand , and more conceptually pure. I'll implement it for all three inner groups at once, instead of just making it work for the outermost group (CountryCode, in my example), because it's actually easier to do it that way.

So, here's what we'll do.

1. Get rid of the special mid-level group.

 Go back to the original dynamic group break, on the outside level, that Karthik had implemented.

2. Get rid of the Report-level custom code functionality.

We won't need to call a code function or keep track of the groups.  Just use Karthik's standard expression for the outermost group's dynamic page break.  While I left my rownum column in the SQL that you will see below, you can go back to RowNumber(Nothing) in the expression if you want.  In this context they should work the same way.

=Ceiling(Fields!rownum.value/Parameters!BreakOn.Value)
or
=Ceiling(RowNumber(Nothing)/Parameters!BreakOn.Value)

3. Get rid of the group footers!

Yes, you heard me.  We'll still get the sums on the group level that we want.

Instead, add three detail-level rows, below the "real" detail row, with expressions that look like this, one to a row:

=Sum(Fields!Population.Value,"table1_NameInitial")

=Sum(Fields!Population.Value,"table1_District")

=Sum(Fields!Population.Value,"table1_CountryCode")

... starting to see where we're going with this?  We're simply going to put Visibility expressions on each of these detail rows.

Please notice the proper use of scope arguments on each expression above, which is critical for either the RS2005 or RS2008 solution.

4. Make the SQL do most of the work

This time, instead of making our SQL look "back" at the previous row's values, we'll just make it look "forward" to the next row's values.  And this time, we'll include the group information for each of the three internal group levels explicitly, since it doesn't "cost" any more to do it: 

SELECT curr.*, Left(Name,1) AS NameInitial, 
  next.CountryCode AS NextCountryCode , 
  next.District AS NextDistrict, 
  next.NextNameInitial FROM
(
  SELECT TOP 100 PERCENT ROW_NUMBER() Over  
    (ORDER BY CountryCode,District,Name) AS RowNum, 
    RTRIM(Name) AS Name,CountryCode, District, Population, ID 
  FROM NewWorld.dbo.City  
    ORDER BY countrycode,district,name  
) curr
LEFT JOIN  
(
  SELECT TOP 100 PERCENT ROW_NUMBER() Over  
    (ORDER BY CountryCode,District,Name) AS RowNum,
    CountryCode , District, Left(Name,1) AS NextNameInitial
   FROM NewWorld.dbo.City  
    ORDER BY countrycode,district,name  
) next
ON  
curr.RowNum+1 = next. RowNum

5. Ensure the ersatz group footers show up... only when you want 'em

Now for those Visibility instructions on the three special detail rows.  Each one is pretty much what you would expect, a comparison of the current row's grouping value to the next row's grouping value... just remember (counter-intuitively) that you want the expression to evaluate to true when the row should be Hidden, not Visible:

=(Fields!NameInitial.Value=Fields!NextNameInitial.Value)

=(Fields!District.Value=Fields!NextDistrict.Value)

=(Fields!CountryCode.Value=Fields!NextCountryCode.Value)

The inevitable caveats 

I should point out that there's probably a way to do this without any fancy SQL at all, by comparing RunningValue with Sum in these Visibility expressions, and taking care to use the proper scope argument for each, similarly to how you see the Scope arguments being used in the displayed expressions for each textbox.  This way, the report does more of the work and the SQL does less of the work. 

The choice, and the testing, is up to you and your environment/results of performance tests, I'm not going to mess with any more variants here.

I should also point out that, if you review the comments on the previous post, I'm not convinced that this dynamic break approach is the right way to resolve Karthik's original problem, as he later explained it.  I think there are some user-communication questions, an examination of the actual requirements and what best serves them from a usability issue, and probably a very happy user-developer combination, with less work, afterwards.

But if you do need to resolve the problem as originally presented, it is solvable.

Bleh.

I don't know about you, but this feels a lot more "natural" than the stunt I pulled to make this work in RS 2008.  What you see here is honestly the first thing I tried, last time. Reading it over, I see I even alluded to that attempt in the earlier post.  I wasn't kidding; it was a nightmare, and I wasted a lot of time on it.

I still don't know why it doesn't work as expected with a tablix. Tablix groupings (including the newfangled idea of "adjacent" groups along with "parent-child" groups, which don't quite match up to my expectations of how a multi-detail band might be expected to work) are so much more complex.  I suppose there's a good reason.

Or... maybe it's a bug in RS 2008 grouping, and the next iteration of RS will change the behavior entirely. I suggest we all re-group, and re-test, then.

Karthik asks

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?

This is actually a tough one, but luckily I brought grenades.  Today's Karthik's day in TechSpoken Court...

Why is this so hard?

Let's  quickly recap the problem.  I will demonstrate Karthik's scenario using the City table in my standard SqlWorld sample db. 

SELECT id, countrycode, district, name, LEFT(name,1) AS NameInitial, population
FROM city
ORDER BYcountrycode,district,name

Karthik has three "real" groupings and aggregation levels in his report, which we will model using CountryCode, District, and the first initial of the city name (that's why the ORDER BY clause).

Typically, you put a conditional or dynamic page break instruction on an outer group.  In this case Karthik has used CEILING(RowNumber(NOTHING)/1000) as his outermost group instruction. 

When an outer group breaks, each inner group breaks too; they are "child" groups, or nested aggregates.  Karthik's problem occurs when one of his "real" groups has more than 1000 rows.  Karthik doesn't want to see his sums or other aggregates until the "real" groups are actually finished.

Since my sample doesn't really have more than 1000 rows in any one set, and to test properly, I've made the "Break on" figure configurable, with a default value of 3 rows.  I've illustrated the problem like this:

The AGO country code grouping that starts on page 2 doesn't finish on page 2.  Therefore, even though a group break is forced by the outer group every 3 detail rows, we shouldn't have totals for AGO on page 2.

The report is doing what we said, but it isn't doing what we meant.

What not to try

I first tried to solve this problem using a "remove the groups" strategy, which I've used successfully in other scenarios before.  It quickly turned into a nightmare.  I'll cut to the chase and say... don't bother.

Inner dynamic group breaks

I realized that the right thing to do was to move the dynamic break group inside the ones that might have more members and shouldn't show aggregates until they legitimately ended.  This is the right solution, but it turned out to be a little more complicated than expected.

Before I explain, the usual caveats.  In this case, there are two things to keep in mind:

1. My analysis may be better than my solution. Read what's involved, because I've done this part right. Maybe you'll think of a better way to handle what needs to be done.

2. I'm only moving one level of grouping inwards, because that's all that's necessary to show you what's involved -- even though, as you'll see, the District group could also "overflow" the test break value of three rows that I'm using in these screen shots is so small. However, if Karthik or you have more than one additional level that could have more rows than the explicit page breaks you want to create, it doesn't change the problem. Whatever solution you use -- whether mine or your improved version -- the thing-to-fix, and how it works, remain exactly the same.

The unkindest cut

We start, as explained  by moving the outermost group with the special "CEILING" expression one level inward.  You can try taking Karthik's expression and just moving it, as-is, if you want to play along.  Here's the error you'll get:

A group expression for the grouping 'Standard Page Break' uses the RowNumber function with a scope that is not valid.  When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group.

Ouch. Note: this is the error message I get in RS 2008 using the Report Designer; it might be slightly less explicit using ReportBuilder or RS 2005, but I imagine the engine rules are the same.

You can try to do the obvious thing and use the scope suggested in this error message (in my case, that would be RowNumber("table1_CountryCode")).  This may seem to work for you -- for me, it didn't have the desired results.   Maybe I should have perservered, but I think that different sort orders, on different levels, may interfere. So, instead, I thought about how I could mimic the desired behavior of RowNumber(Nothing) without actually using the forbidden syntax.

The second cut

I did a number of things with Code functions, counting rows myself, which seemed to sort-of-work.  But the behavior seemed a bit flakey, especially when rendering to HTML and flipping back and forth between pages. 

In the end, I took the more reliable route of creating my own row counter in SQL, like this: 

SELECT
Name,CountryCode, District, Population, ID,
ROW_NUMBER() ORDER BY CountryCode,District,Name)
FROM City
ORDER BY CountryCode,District,Name

-- the new group break expression is:
-- =CEILING(Fields!RowNum.Value/Parameters!BreakOn.Value)

... and that sort-of worked. I mean, it worked for most pages.

But now I had a new problem. On pages where the CountryCode (outer) group break and the pagebreak limit (Parameters!BreakOn.Value) coincided exactly, no page break occurred.  Apparently, the fact that the outer group said "no page break" countermanded the inner group's explicit page break instructions. 

This behavior was consistent in different renderers, and seemed to have nothing to do with margins, sort orders, or anything else I could tweak.  So, clearly, more effort was required.

Giving myself a break

By the time I figured out the pattern of the mysterious pages that didn't work as expected, I wanted to solve this in a very literal-minded fashion and get it over with already.  I already had a way to say "do a page breaks without unwarranted group header and footers", that was inner.  But now I needed to say "Also break at the same time as group headers and footers are warranted".  So, I realized I needed a second way to break -- outermost, where it would be the highest-level authority on whether a page break was called for.

What would be the proper group break expression for the new group, though?

I knew I wanted to to give the group these instructions: "Only break when (a) you've hit the right number of rows and (b) you've also hit the CountryCode group break". 

Again, thinking literally, I knew that the second condition could be satisfied by checking the current CountryCode against the previous row's CountryCode.  But, in using the Previous function, I ran into similar limitations in group expressions as I'd faced with the RowNumber() function:

A group expression includes the aggregate function 'Previous'.  Previous cannot be used in group expressions.

Ouch squared.

I tried a lot of ways to get around this expression, as I did earlier (because I am stubborn), by writing code to compare the values myself, but in the end I took the somewhat more controllable path of adding the value directly into my SQL query, like this:

SELECT curr.*, prev.CountryCode AS PrevCountryCode
FROM 
   (
    SELECT TOP 100 PERCENT ROW_NUMBER() Over  
      (ORDER BY CountryCode,District,Name) AS RowNum, 
    RTRIM(Name) AS Name,CountryCode, District, Population, ID  
    FROM City 
    ORDER BY countrycode,district,name
   ) curr
LEFT JOIN
   (
   SELECT TOP 100 PERCENT ROW_NUMBER() Over
     (ORDER BY CountryCode,District,Name) AS RowNum, CountryCode 
   FROM City  
   ORDER BY countrycode,district,name
   ) prev
ON curr.RowNum-1 = prev. RowNum

... and, while it may be possible to handle the resulting evaluation in a direct group break expression, I found it easiest to set up a function to do it, like this:

Ignore the Intellisense failure (annoying isn't it?) which just indicates that Intellisense isn't very... intelligent... about custom code. 

And you can ignore the ShowIssue parameter, which I only needed to show you the problem that occurs when this break behavior isn't included in my sample report.

The report script looks like this (as you can see it's very simple and literal-minded):

Dim CurrBreakNum As Integer = 0  
Dim CurrCeiling As Integer = 0  
 
Function GetBreak( _  
   ByVal showIssue As Boolean, ByVal thisCeiling As Integer , _  
   ByVal currCode As String, ByVal prevCode As String) As Integer
 
   If showIssue Then

      ' never break, we want to show the issue

   ElseIf prevCode Is Nothing OrElse Len(prevCode) = 0 Then

      ' first row, there is no previous value
      CurrBreakNum = 1

   ElseIf (CurrCeiling <> thisCeiling) AndAlso (currCode <> prevCode) Then

      ' if we've hit the special situation where
      ' a break is called for and the "outer" group
      ' breaks on the same row, we have to force 
      ' an external break...
      CurrBreakNum += 1

   End If

   CurrCeiling = thisCeiling

   Return CurrBreakNum  

End Function

See?

Simple, literal-minded, and rather brute-force-ish.  And of course I tried all kinds of ways to be clever first.

But... when you think about it, you'll see that this approach makes sense and fits "what's really going on" in the engine. And, more importantly, it works.