SSRS and Excel workarounds

Any ssrs report designer that has tried to make their report Excel friendly has seen that most often... unless you make your report Excel friendly from the very start of report development... will not integrate very nicely. Hidden columns and merged cells in unexpected places are the most common obstacles you will find. Followed by frustration by both the developer, and the end user having to fix your Excel outputs.

There is a MSDN out there to "Help" with this.. but I found the article to be lacking. But it did set me on the right path but left me with about hours of working out the kinks of EXACTLY what do I have to do to fix my Excel outputs.

If you find this article you will no doubt find very shortly that a great many of ssrs report designers have issues with Excel. You will also notice that after market software developers have also noted this problem and have solutions that make your reports Excel friendly for a modest fee... Cheapest I found was 250 bones. Most expensive was 1500 for a software developing solution.

The problems that occur with Excel outputs are related to converting the ssrs layout data from Inches to Pts. I believe there is some kind of rounding issue with the conversion engine for ssrs that the conversion is not exact. Causing hidden columns and unexpected merges of the cells.

Preferably... building your report to work with Excel from the very beginning is preferable and much easier to do. Right off the bat when you create that blank report page change all the report options to the pt system. This information will help you convert a finished report to be more Excel friendly.

1 inch = 72pt

• In the report\options change the grid options to pts... margins... spacing
• Every item that you add. Textbox, img, table, etc must have its SIZE and LOCATION changed to pts.
• Some items I found CANNOT be converted to points. In that case I try and make the size of that object an inch value that does not have that many decimal places... like 1inch, 1inch or 1inch, .5inch etc.

I don't have a clue why the engine is not better in conversion... but after doing these steps you will hopefully learn to make a predictable excel output every time.

How to troubleshoot an Excel output to find the field causing the problem. This section will aid you in working out kinks in your ouput AFTER you perfomed the above steps to convert the report to pts.

• Carefuly examine the column labeling in excel and find a letter that skips... for example you would have comuns A, B, D. What happened to C?
• move the mouse gently between B and D and expand out the column and wala! There is C!
• After you expanded C out highlight that column so you select all record's C value.
• Next you have to hunt by clicking in different rows to find the box that has a clickable "C" region that is NOT merged.
• This is the rotten apple. examine that field closely in your report layout and make sure that the conversion was correct and that it lines up with another field.
• Continue the process until no hidden columns are found.