Happy Holidays!

I hope everyone has a very healthy, happy, and Merry Christmas.

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.

Object Explorer Details in 2008 SSMS

One of the things I love most in SQL 2008 SSMS is the way Microsoft redid the Object Explorer Details page. Now it shows some really useful information. You can change what columns display by right-clicking on the column bar and checking which columns you want to see.

I need to catch up

It's been quite a while since I've blogged about anything. Mostly it's because I've been busy at work; creating a new reporting interface, critical disaster at one of our data centers, busy time of year. But in reality it's because I've been too lazy. I mean, really, I've got a long commute to work (1 1/2 hours by train each way) so there's no excuse why I can't do more. If nothing else, it's a way for me to capture my thoughts/actions of the day.

So, beginning today, I (once again) vow to blog more often. And I'll try to keep this blog technical; no one really wants to read about my personal life.