{"id":72,"date":"2010-12-12T15:57:00","date_gmt":"2010-12-12T15:57:00","guid":{"rendered":"\/lisa\/post\/2010\/12\/12\/Everything-has-a-point-Part-Deux.aspx"},"modified":"2010-12-12T15:57:00","modified_gmt":"2010-12-12T15:57:00","slug":"everything-has-a-point-part-deux","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2010\/12\/12\/everything-has-a-point-part-deux\/","title":{"rendered":"Everything has a point, Part Deux"},"content":{"rendered":"

Last week<\/a>, I told you about my recent discovery of a good reason to use group variables in SSRS. The requirement seemed simple, obvious, and could be given to you any time.<\/p>\n

This week, I’ll tell you about another standard scenario that really bugs me, and comes up pretty often, and how the Word renderer helped me resolve it.<\/p>\n

I’m calling this a “standard scenario”, not a “requirement”, for a reason.  Here’s what happened to me — and I bet it happens to you, too:<\/p>\n

I had already fulfilled the requirements, and then somebody wanted to repurpose the report by analyzing the results in Excel, and had a minor anxiety attack.<\/p>\n

You don’t recognize this scenario, you say?  I bet you will, once I illustrate it.<\/p>\n

Here’s how it was<\/h3>\n

To illustrate, let’s take the following simple query (from my standard MS-SQL implementation of the MySQL World tutorial database<\/a>):<\/p>\n

SELECT
<\/span>  RTRIM<\/span>(<\/span>City.<\/span>Name)<\/span> AS<\/span> CityName,
<\/span>  City.<\/span>Population<\/span>,
<\/span>  RTRIM<\/span>(<\/span>Country.<\/span>Name)<\/span> AS<\/span> CountryName,
<\/span>  Country.<\/span>Continent
FROM
   <\/span>SQLWorld.<\/span>dbo.<\/span>City INNER<\/span> JOIN
   <\/span>SQLWorld.<\/span>dbo.<\/span>Country ON <\/span>City.<\/span>CountryCode = <\/span>Country.<\/span>Code<\/p>\n

 … We’ll express this as a simple table, grouped on Continent, and grouped within Continent by Country.  We want to see Country populations as a total line for each Country group, and then be able to drill down into City details as needed.  What could be more obvious? <\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

Okay, okay, the real report is more complex, sure, but this is the basic idea: Look at the aggregate, then optionally drill down to the details.  That’s all.<\/p>\n

So what’s the problem?<\/h3>\n

The first time a user decides to try the “Export to Excel” feature, they find out that they can’t easily sort by country population, filter by country, et cetera, because the Excel renderer preserves this report layout as outline levels. <\/p>\n

You may not be able to see the cities because the rows are hidden, but they’re there, and they’re interfering with what looked like an “obvious” extension use case for the original report.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

The user in question was None Too Pleased.<\/p>\n

There now.  Are you sure this has never happened to you?<\/p>\n

If it were really a simple report, it might be easy to create an aggregated version of the original report and export it without the detail lines, either as a simpler RDL or even as CSV.  Do this additional query-plus-export to give this user a chance to analyze the data in Excel to his heart’s content.  But it’s not really a simple report, is it? <\/p>\n

In this case there happens to have been a lot of code and arrays that apply business logic both across and down, which create the subtotal results.  They’re not really coming from the database at all, and it would be very difficult for them to be derived from straight SQL.  This part may or may not have happened to you, and it’s not really relevant.  You have a report, it has collapse and expand set, you’ve already put a lot of work into this report, and you’re not responsible for the hash that the Excel renderer makes of it.<\/p>\n

The fact is that the Excel renderer, while not exactly as dumb as a box of rocks, only fulfills one out of many reasons why people might want Excel exported a report.  The requirement it fulfills is: give me something that looks like the fancy layout of the original report.  The requirement it does not fulfill is: give me something that will function well for additional manipulation in Excel.<\/p>\n

The designers couldn’t do both, and they chose faithful reproduction of RDL appearance.  They made a judgement call, and a reasonable one — I’ll explain why I think this, below, but I shouldn’t really have to explain and give you an alternative.  Just imagine for a minute that the designers hadn’t<\/strong> made this choice.  Now imagine what early reviewers would have said when the Excel export didn’t<\/strong> look like the RDL result.  Now imagine what the designers’ bosses would have thought of their design choice.<\/p>\n

Oh my Word, what a rendition<\/h3>\n

Guess what?  The Word renderer reproduces the collapsed layout more faithfully than Excel. If you leave everything collapsed in the Report Viewer interface when you Export to Word, you don’t get the detail.  You get a Word table that looks like this:<\/p>\n

 <\/p>\n

\"\"<\/p>\n

That doesn’t immediately solve all your problems, unfortunately.  The Word renderer may not look like it’s dumb as a box of rocks, but it really is.  Sure, this is a table, and sure tables in Word can be sorted, etc.  Just try it.<\/p>\n

 <\/p>\n

\"\"<\/p>\n

Uh-oh.  Well, NBD.  They didn’t really want simple Word table tools anyway, right?  They wanted all the nifty data-manipulation of Excel.  So here is what you do:<\/p>\n