Thursday, December 18, 2008

Reporting Service "features"

I've been a proponent of Microsoft's SQL Server Reporting Services (SSRS since 2005) since it was first released in beta for SQL 2000. I thought the product was a natural, combining a report writer with the database. I especially loved the ease of deploying individual reports. The first product was rough, but I figured it would improve with time. By the time SSRS 2005 came out it was a stable product, but it lacked some features, like rich textbox formatting and exporting to MS Word. SSRS 2008 added those features, so I thought all was right with the world. Silly me.

My client has been using some reports written for SSRS 2005. Recently we began converting their existing reporting application from MS Access 97 (please don't snicker too loud) to SSRS 2008. We started with small, simple reports. These worked out nicely, and developers were able to learn how to use the report designer fairly quickly. I began converting a much more complex report that had tons of Access code-behind and sub reports. Much on the report is determined by what the user wants to see. Creating the report in SSRS 2008 was time consuming, but it worked out nicely.

The "features" came out after I created the first, main report and checked the reporting project into Visual Source Safe. After that I started running into all kinds of problems, some of which are;

○ Very often, with no reason, credentials for the data sources would change from what we use to no credentials. I first noticed this after I checked the solution out of VSS. At first I thought the last person to check out the file changed it for some reason. Then I noticed that it didn't matter who last touched the file; even files that were never checked out were affected. And this also happened to files that were never checked into VSS.
○ Another annoyance is when a dataset changed from running a stored procedure to running text. This can happen if you modify the data source (see above), and also happened in SSRS 2005 if you merely opened the dataset definition to see what it was pulling.
○ Inconsistent behavior. For instance, the main report has 2 very similar sub reports. The user can choose to see sub report A, sub report B, or neither. Simple, right? Just set the sub-report visibility to display based on the user's choice. It worked fine if the user chose A or B; the correct sub report was displayed and the correct sub report was hidden. But if the user chose to hide both, then both display. I'm not going to post the code here, but suffice it to say the code was correct; it's being used on many other sub reports on the same main report. So how did I fix it? I moved the code to a report function. The bottom line is the very same code that didn't work as an expression of the visibility property of the sub report worked correctly when called as a function!

There's more, but this is enough for this post. I'll be adding to it soon.

No comments: