Search This Blog

Thursday, October 16, 2014

SSRS report rendered in EXCELOPENXML then something went missing

Recently I had been requested by a client to look into one of their SSRS reports issues. They had one report with a text box that contained some padding from the left side. All seems to be OK, report showed indented text values both in RPL and PDF rendering extensions; however when the report was exported to Excel those indentions were lost.

I removed the Left Padding information and substituted it with hard coded extra spaces in text box expression. This tweak didn't solve the problem; now my Report server didn't show those extra spaces (i.e. Excel Exported version of the report didn't show them either).

Then I removed extra spaces from the SSRS text box expression and added those spaces into a data set that sourced that text box. Now Report Server showed all the indented text boxes, however Excel exported report didn't show those extra spaces again; what ?!

Let’s be crazy, and then I removed all extra spaces from data query and built my text box expression using a conditional check for Globals!RenderFormat.Name="EXCELOPENXML" to only add extra spaces for my text box when a report gets exported to Excel; and this… solved the problem: all RPL, PDF and EXCELOPENXML versions of the reports showed me the left padded text box as it was intended.

Then I noticed another text box attribute (LeftIndent) that I didn’t try to adjust.  After adjusting the LeftIndent  value all my rendering extensions worked perfectly, so I no longer needed to use the conditional check for Excel rendering extension.

It’s funny that I’ve dedicated the whole blog post to EXCELOPENXML rendering  extension which I didn’t need to check at all; but … now I know how to solve this problem differently :-)


Wednesday, October 15, 2014

SSIS Pivot makes you stronger

At the recent Toronto SQL Server User group meeting Michal J Swart (@MJSwart) gave a good presentation based on 10 of his blog posts. There was one (Forget About PIVOT, You Don’t Need It) that got my attention; first, I remembered that I had read a while ago and my second thought was that I could add two cents to it.

Very early in my career I got fascinated by the MS Access TRANSFORM statement, then in 2005 version of the SQL Server the PIVOT/UNPIVOT operators came out and that made me even more excited in the way that now we can forget about all custom T-SQL code and write a simple statement to turn a data set anyway we want to.

I agree with Michael, that all the available reporting tools are much better for preparing a final data set for business users. However I've used PIVOT many times and I like using it in a middle data layer when one data set gets transformed (or in Excel terms, 'transposed') to another form and then moved further along the way.

The SSIS Pivot with UI gave some additional flexibility to transform input data set, however I still prefer the T-SQL method and I can explain you why.

Here is my sourcing query that pulls data the AdventureWorksDW2014 database that I want to transform:

























In SSIS I can do the same thing and push the sourcing query through the Pivot task:


















However when I execute it will fail due unique key constraint violation of my Pivot Key of the Pivot task:


















So I had to change the sourcing query and added aggregation function to it:












and then it worked:

















So, with a few hiccups I was able to adopt SSIS Pivot task, but I still prefer writing the Pivot T-SQL statement manually, more control, more flexibility :-)

Lessons learned:
1) Sourcing data set for SSIS Pivot task needs to be pre-aggregated.
2) If you don't like Pivot output columns (based on Pivot Key and Pivot Value) you can always rename them in the Advanced Editor.

Working with SSIS Pivot task didn't destroy my attitude but made me stronger :-)

Happy data adventures!