YAPS on Dynamic PageBreaks: the RS 2005 SQL Sequel

I am seriously annoyed. 

In https://spacefold.com/lisa/2010/02/14/YAPS-on-Dynamic-PageBreaks-Mixing-up-the-Groups 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 https://spacefold.com/lisa/2009/03/08/Dynamic-Page-Breaks-in-SSRS-Going-to-Eleven.  

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.