Several years ago, more than I want to count, the "Big New Thing" in SQL Server and a life changer, for me at least, was SQL Server Reporting Services. I recognized it immediately as a tool that filled many gaps between the data entry and data delivery components of SQL Server. Plus, it was free (disclaimers apply), so I knew straight away that SSRS and I were going to get to know each other well, and over the ensuing years we did. Perhaps a little too well.
My company adopted it entirely for all reporting requirements, and I became engrossed, developing more reports than I care to count, and writing several books on the topic. In the beginning it was a lot of fun. Sure, there were many time I'd find myself shouting at the screen in disbelief, "Huh!? What happened there?" but it was exciting to hack out solutions, at least in the early days, and rewarding when I finally got the report working and looking exactly as I wanted.
Over time, however, there seemed to be less singing, more shouting. It felt like most times I developed a new report I was having to find some new hack to get it to work just how I wanted. Sure, I always managed to get there in the end, but it was generally painful.
And then, suddenly, I changed roles and we parted ways, SSRS and I. Sure, we still conversed, occasionally, when someone else had an issue with a report they were creating, or I needed to bash out a quick chart, but that was rare, and before I met my new friend, PowerView. I had not developed a full blown SSRS report in over 2 years.
Then one day, very recently in fact, I took a new position. Faced with an important documentation project, I felt mixed emotions as it occurred to me that SSRS might be the tool for the job. So I jumped back in, back in the USSRS.
Boy this product sure had come a long way…so many new features and stellar new charts and maps. I'd been away so long I hardly knew the place! Twenty minutes into developing my new report, I was smiling broadly. I had all my data into a lovely-looking line chart and just needed to add the required filters. Add the first filter value, test it out, all good. Add the second filter value and…NO DATA AVAILABLE…
"Huh?! What happened there?"
I removed the second filter value and we were back working again. I tried loading the two filter values in one element using
IN. No go. I tried a
SPLIT function and, while that may or may not have "worked", I stopped the report after it ran for 5 minutes without returning any results. I rolled up my sleeves and, a short period of hacking later, got it to work by using
BETWEEN, but that was really only because luck was on my side; my filter values started with a "U" and a "T" and there were no other values between them.
I sat back and smiled ruefully. Much had changed, but much had stayed the same. I am going to keep at it with SSRS, because it definitely still serves a purpose for me, but this time, rather than just hacking and pressing on, I'm going to report these issues to Microsoft and hope they continue supporting and improving the tool. Heck, one day they might even incorporate Word, to provide baked-in support for rich text formatting. Now that really would make my life back in the USSRS much easier. Ring out you balalaikas!
Rodney Landrum (Guest Editor)