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.