8/06/2015

Some SSRS Report Writing Observations

Working in the Microsoft SQL Server Reporting Services (SSRS), there's a few workarounds I've been doing this week to make things more efficient.

For example, when you have a report with multiple lines for a given Group, it's sometimes difficult to determine which line you are looking at when the report renders.

So I've color coded each row to help identify which row I'm looking at.  Here's an example of an SSRS report with color coded rows:


So when the report renders, you can see where you're at:

And now I can see if the Suppress row feature is working properly.

It's kind of a brute force approach.

If you look at Crystal Reports for example, they explicitly let you know where you are in the hierarchy:


It really helps to identify which row you're looking at.  Also, they have the ability to open the row properties to scan the formulas while in Render mode, Select Expert:



And then view the Formulas globally and then see the underlying details:


And then a button to see if the formula is validated properly:



Lastly, the parameters in SSRS are fixed, in that if you want to rearrange the order, there's no button to click:


The way I rearranged the order of parameters was to dive into the code by selecting the "View Code" button by right clicking the report in the Solution Explorer.  Did a search for the parameter in question, then copied and pasted the contents higher in the chain:


And the other place further down:


When the report renders, the parameters appear in a new order:


You have to copy and paste in the correct location or the report basically breaks, so you may want to do a "Save" prior to messing with the raw code.  So if it breaks, which happens sometimes, you simply close out Visual Studio and re-open, and try again.

To covert a Crystal Report to SSRS, the SQL is straight forward, copy and paste into a Stored Procedure, tweak a few syntax issues, load into the SSRS report.  But then trying to convert the formulas from Crystal Report syntax to SSRS formulas, takes a long time.  As does figuring out the group visibility setting formulas.  And re-arranging the parameter order list.  These 3 items have taken up a substantial amount of time to create a report.

However, I really like some of the cool features in SSRS like multiple data sets within a report, ability to customize every detail of a report, etc.

And there you have it.  Some observations on SSRS coding.


No comments:

Post a Comment

We Interrupt this Broadcast