I need to add a couple of postscripts to last week’s post.
I was talking about resolving an issue with the SSRS default Excel renderer, which shows up with collapsed groups, by either:
- exporting using the Word renderer first and the going into Excel from there,
or
- (what I prefer) exporting to XML and writing your own Excel with XSLT.
Postscript 1
The actual report has a toggle very much like the one I showed in my example: an inner group needs to collapse and expand. It turns out, believe it or not, that if I move the toggle to the outermost group, the Excel rendition is much better behaved.
You still have the outline behavior but you can sort and filter when you format the table in Excel, despite the outlining.
So, this was kind of interesting, and good news, especially given what I’m about to tell you below: if I eliminated the outer groups (in the sample report I posted, this would be the group on Continent), and simply treated them as a value column in the toggled group (in the sample report, this would be Country), users can go straight to Excel. We can use “Hide Duplicates” and other formatting tricks to give a similar layout result as the outer group was doing for me.
It won’t always work, for example, you may want totals on that outer group. But it’s a nice plus that outlining doesn’t automatically spell analytical doom for SSRS’s default Excel renderer. Try it yourself, because it’s a little difficult to believe, but it actually works fine.
Postscript 2
In the actual report, there is a lot of code, accumulating values both across and down, before the subtotals are shown for each toggled group. Never mind why; just take my word for it that these are not “natural” aggregates that are easy to do in SQL when you create the dataset, or by invoking a standard SUM() formula expression in the RDL; there’s just too much business logic determining which items may and may not be included in each total (again, both across and down).
Well, it turns out… that with this actual report, the XML renderer just doesn’t cut it.
The subtotals that show for the Details collection, as generated by my code function, are wrong in the XML renderer, although they are right in all the other ones. And the issue has nothing to do with visibility; I can un-hide all the detail rows, remove the toggle, and I still see the same wrong values in the XML.
It also doesn’t seem to matter where I do the calculations. and accumulate the arrays in the code. I originally had them in the first textbox that required them, invoking a function that RETURNs “” and concatenating this function with the “real” expression for the textbox. This is something I often do.
When I saw the problem, I moved the accumulating function to a fake group filter, which always returned true. And I tried group variables, and I tried different positions in the actual text box exprssions. Nothing doing. No matter what I tried, all the other renderers showed the correct values but this function wasn’t working.
So, in this case, it’s not a question of sequence of evaluation being different in the XML renderer, it’s as if there’s an optimization in the XML renderer that prevents the function from being invoked at all.
Once more, with feeling:
If there’s a renderer required for a particular report to succeed, don’t forget to test that renderer separately. Usually the differences are limited to formatting (with pagination being the typical, major issue), but occasionally, as you see here, even the actual values may differ.
IMHO, this shouldn’t be. Physical formatting details between renditions are bound to differ, and I’ll defend to the death the necessity for this when folks whine about it and expect anything else. But values should not differ… or at least with no explicable reason.
If I can get it down to simple steps to repro, I’ll post it here. And you know I’ll post it as a bug with MS.