If you’re like me, you’ve had a love/hate relationship with SQL reporting services (SSRS). On one hand, it has the potential for way more functionality than what the legacy ASP reports had, on the other hand you can’t use cascading stylesheets (CSS) to set the look/feel and there aren’t dynamic-width columns. The lack of stylesheets was especially troubling to me because it means you have to edit every single .RDL file to change something simple like border colors or cell colors.
If you know me, you know that isn’t gonna fly so back when we first started rolling out SCCM 2007 with it’s lovely SRS reporting point, I set out to find a way to make SSRS have a dynamically changeable “style”. I came across some articles that talked about using custom code or custom assemblies and I thought that’s just not good enough for me. Either it wasn’t flexible enough, or it wasn’t attainable because I’m not in the development group and can’t have access to the dev tools necessary to build the assemblies on my work machines. So, I found a way to use the built in “Expression” capability to pull settings from a data source so you can just edit a row in a table to change the style.
So, what I really wanted to show you were some BDNA Normalize for CM reports that I’m working on now that Normalize for CM is out, but I feel it’s important to quickly step back and make sure we’re all working with the same tools. I want to first provide a system complete with sample datasources, templates and styling so that you can take it back to your work, use the reports, tweak them, create your own based on the templates and impress your boss and coworkers. So, I’m starting a series where I’ll explain how to build a “Style” based SSRS reporting system using the expressions built into the report properties and then start providing sample report packs you can install which will all point to a style datasource. In the end you’ll have a complete system you can use to sample the Normalize for CM data, your SCCM data or honestly, any other data in your enterprise that SSRS can report on. And in a year, when your marketing department decides they want to change from Seafoam Green to Minced Meat Orange, you need only update your style DB with the new values and every single SSRS report based on this system will render with the new values.
I’ve set this up and it’s been running for something like 4 years now, so it’s been tried and tested at a grand scale (350,000+ clients) in a fortune 20 financial company. There’s just never ever been a problem with it, so I feel confident that I can release this for public consumption now. In this series we’ll explain the process of making Stylesheet-like/CSS-like SSRS reports, we’ll develop some SQL queries for much coveted SCCM & Normalize CM reports, we’ll release sample code and .RDL files and we’ll see what we can do about releasing regular reporting packs complete with an automated install routine so you won’t have to find yourself building your own template with 500 different expression-based properties like I did.
So, stay tuned, I’m committed to releasing a new article and sample report packs every 10 days or less for the forseable future.
And, if there’s some piece of information you find especially difficult to query for, or if there’s some report you wish someone would write, drop me a note in the comments below and I’ll add it to the stack.
#2 – John Nelson
