In my previous post, I started a series where I’ll walk through creating a (for lack of a better term) “style based” SQL Reporting Services (SSRS) template, not unlike CSS but working within the limitations of SSRS 2008/2012. This post will serve as the next installment of that series where I’ll begin to lay out all the parts needed to actually create the style-based report template and start writing wicked looking (and perhaps more importantly, uniform looking) reports for SCCM or BDNA Normalize or any other SSRS-accessible data you have.
THE INGREDIENTS
First, here’s a list of the ingredients that will go into making our style-based template:
- A SQL server that will host the style DB with SQL Reporting Services 2008 R2 or higher installed to run the reports.
(I’m sure 2008 non-R2 and earlier will probably work, but I’m only working with 2008 R2 and higher) - The style DB on said SQL server to hold the properties and their values.
(which I will help you create and populate with properties and values if you want) - SQL Report Builder 3.0
(Again, earlier versions may work fine, and BIDS would probably work fine, but I’m only using Report Builder 3.x+ for simplicity) - A sample report template
(which we will build in this series using Report Builder 3.x) - Some data on which we’ll report to test this out. We will just use the style DB for simplicity.
THE CONCEPT
The first thing I think everyone should understand about SSRS reports, if they don’t already, is that for many report properties within report builder, you can use an expression. These expressions are like a built in bunch of “function” type statements that can go above and beyond just entering a single value. Like if you wanted a table to use the ARIAL font in a text box, you could either choose ARIAL from the FONT FAMILY property picklist like this:
IMAGE #1 – Arial font chosen manually from the picklist for the FontFamily
OR you could select EXPRESSION from that same picklist and use an expression to pull that value from a dataset like this:
IMAGE #2 – Choosing “Expression…” from the picklist instead of a font
IMAGE #3 – The Expression dialog that pops up and lets you choose values from a dataset
It’s this functionality that we’ll use in our SRS reporting template. So, let’s think about the ramifications of this for a minute…if we can pull a value from a dataset to set different properties in a report, then we could just build ourselves a table with a bunch of style properties in it which feed just about every aspect of our reports. And although it’s a little cumbersome to set every property to an expression, once we create a report template upon which every one of our reports will be built, then there’ll be little work to do when we want to make a new report. At that point it will be as simple as opening the template, adding a dataset for the data we want to pull, dragging a few columns around and saving it as a new report. All the style expressions will already be populated from the template.
NOTE: See all those values in DataSet1? In order for there to be multiple values in that list, each property needs to be in a different COLUMN in the dataset…keep that in mind for later. We’ll be creating a property table where each property is a different row. We’ll need to dynamically pivot those rows into columns to make this work.
I realize this audience has a bunch of different skillsets from the brand new to SCCM/SSRS to the Microsoft Premiere Support professionals and multi-year Microsoft MVP types, so I will go into sufficient detail here so a newbie could build something like this themselves but also get technical enough that the veterans are stimulated. So if you’re new to this and find yourself getting lost in the details, don’t worry. Just keep skimming, pick up what you can to understand the concepts, and I’ll provide downloadable examples that you can just plug right into your environment later. Also, there are going to be other articles in this series which go into more detail to explain these things better, provide real-world examples and I’ll start providing some actual reports built on this system that you can look at and use as you wish. These will show off the power and flexibility of style-based reporting. So if you aren’t getting it by the end, stay tuned and I’ll keep putting more detail into this and more examples until it’s acceptably exhausted.
PROS AND CONS
Now, I’ll be honest, there are some things that are less than ideal about it but there are also a lot of benefits. Let me quickly lay out the pros and cons that I can think of so you know what you’re getting into.
PROS
- Consistency. Once created with these style expressions, you’ll be able to update the look and feel for every report at the same time by simply editing the style DB saving you a lot of editing time or development time in scripting the changes to the reporting DB.
- Speed. All indications are that this solution is pretty darned quick, despite the fact that it’s coming from a DB external to the report. I’ve been doing this now since late 2008 and we’ve just never had a problem with the properties taking a bunch of time to load even with a bunch of people hitting the DB at the same time (I’m at a fortune 50 company with 300K+ people so even though most of them don’t use my reports, the number who do use these reports may be as high as 200 different people in a given day). This is not to say that doing it this way is faster than having the values embedded into the report. That is definitely faster because it doesn’t have to make a connection to a database to get the values, but there has been no noticeable difference between manual and using a style table.
- Different themes are possible. You may find it beneficial to have a certain look and feel for one department or sub-company and yet need a different look and feel for another department or sub-company. Or you may be a consultant who is making reports for many different customers. It’s pretty easy to copy all of the properties and then modify them to create a new copy with a new theme. Instead of creating a new report template for each customer and mucking about with the style each time, you can just clone the styles from one theme and update it with the color scheme of the new customer and be done with it. I may even be able to find some bright developers to help with a theme creator UI…
- There is a seemingly limitless flexibility to the system where nearly every single property for every single report object can have an expression value so even different text boxes on the same report can have totally different fonts and borders and background colors, etc. and even the logos at the top and bottom of the report can be switched on the fly by simply changing the values for the logos in the reporting DB.
CONS
- Because I’m using Report Builder (which in my opinion is a little easier for the lay-person to open/view/modify reports) there is not the possibility of creating a default template like you can in BIDS by putting a template RDL in the ReportProject folder which would inherit all the style info of your template. Also, even when I did try this with BIDS, none of the expression-based styles would be inherited from a template…almost as if there were special characters in it that would just get filtered out during that inheritance process. So the best we can do is create an .RDL file which we’ll just CALL a template and do a SAVE AS whenever we want to create a new report from that template. I’ve been doing it that way for years now and it works great.
- The part of this solution which also makes it so incredibly powerful and flexible is also what can make it seem pretty complicated. There are a lot of possible properties in a report when you factor in all the different text box and tablix and rectangle, etc. objects. Finding the exact property you want can take a little poking around. Of course, you don’t have to get as complex and granular as I will have in my template and style DB. We could settle on a few essential properties and just use those. I wanted this to be extremely thorough however, so I spent countless hours on this and put almost everything into it that I could.
- If there are new controls/objects you want to put on your report, like using the new matrix wizard, those objects will not inherit any of the style and you’ll have to modify those properties manually. The template I’m going to provide is for a basic tablix (table) style report. If you intend to have a lot of other style reports that need to have uniform charts or matrix objects, it may make sense to build more than one template which already has a chart or matrix in it that’s pre-formatted. It’s your call. Most of the reports I’ve ever had to make were simple table reports so I haven’t made those others.
- An RDL file with hundreds of style properties in it from a dataset will inherently be bigger than one without them. My reports are upwards of 400-500K in .RDL code (which is ultimately, just XML). I haven’t seen any real problems with this in terms of performance or bloat, that’s not really that big… I’m just pointing it out for full disclosure.
- Not every single property can have an expression. Some properties simply must be manually entered (like the page size of the report) and can’t be customized. Also, some properties which are easily copied when there’s a manually typed value there are not copied when there’s an expression value there (observe this before and after copy on a text box. On the left is with all values set. On the right is a copy of the text box on the left. It inherited some, but not all properties.)
IMAGE #4, #5 – Properties that don’t get inherited when the object is cloned
In general though, this has been WAY more good for us than the alternative of setting properties manually on each report and either editing them one-at-a-time or scripting their change. There have been a couple of changes to the base fonts of our internal branding standards since I started creating these reports. I’ve been able to easily update all our reports with a very simple DB edit and go back to the real work.
PUTTING IT TOGETHER
Alright, let’s do this then…
1) Install SQL Server 2008 R2 or higher (or acquire a working SQL instance)
You need this to hold the DB that’s going to hold the style properties. Now, I’m sure there are a bunch of different ways we could store our style properties…SQL DB, Oracle, XML, probably Excel or Access or even flat file if we used ODBC…but for most of us here at myITforum.com, we’ve got access to our SCCM servers that all have SQL running or some other SQL server or have access to SQL Express or whatever they’re calling the free version these days. So I’m going with what I think we all kinda know and that’s a SQL backend to hold the style DB. For brevity, I’m not going to go into how to setup SQL, I would think that’s something you should already know or have access to a working instance already. Bottom line, however you gotta do it, get you some SQL.
2) Create a reporting database to hold the style properties.
So, because the stuff I’ve done at my current employer is under non-disclosure, I can’t release anything that I’ve done at work including branding info or logos or templates any of that. So for the sake of example, I’m going to mock up a reporting database for some other company so you can see a fully functioning reporting system. I like so many companies in this field for the products and services they provide (like 1E for their Nomad product and awesome MMS parties and sponsorship) or the innovation they’ve come up with (Adaptiva, Eminentware/SolarWinds, 1E) so it’s difficult to settle on any one company that I want to do this favor for. But personally, I think the company BDNA has been very, very good to the myITforum/SCCM community (through their SCCM guru webcast series and their SCCM genius series and by taking action when we’ve provided feedback that their products should integrate more fully into SCCM) so I’m going to tip my hat to them and provide this reporting DB with all their branding (as seen currently on their existing website) and give it to them to use as they see fit. So from here on out in this series, I will refer to the reporting DB as a DB named “BDNA_Reporting”. Obviously, you can name yours whatever you’d like, but for this example “BDNA_Reporting” = the style/reporting db.
The reporting DB needs a few things to be useful and robust:
- A properties table
this is just a table holding all of the many hundreds of properties that could possibly exist on a report
IMAGE #6 – BDNA_Reporting.dbo.Properties table - A propertyGroup table
this is really nothing more than a table to group different properties together like they were different themes. I suppose we could have called this a Themes table, but the table above is the properties table…and we’re making a bunch of different groups of those properties, so in my mind PropertyGroups was the most intuitive so I’m sticking with it. Obviously, if you knew what you’re doing you could just change it to whatever you want.
IMAGE #7 – BDNA_Reporting.dbo.PropertyGroups table - A “usp_GetSRSProperties” stored procedure
this proc will take one simple argument (the PropertyGroup’s GroupID) and will return all of the properties in that group (theme). As I mentioned way up in the note underneath IMAGE #3, to make it work right, we need to pivot the rows in the BDNA_REPORTING db into columns. This procedure will do just that. I chose a stored procedure because SQL seems to be able to recognize more quickly if it’s run before with the same input parameters and has the query plan in cache and doesn’t need to be checked for syntax as that happens when the proc is created, there may be a little performance benefit when running a stored proc vs just using ad-hoc SQL code. More importantly perhaps is that it’s also centrally located and if the code needs to be changed, we need only change it in the one spot and all reports will magically get the updates.
This proc will get executed by each report to populate the dataset and thus the report properties.
3) INSTALL SQL SERVER 2008 R2 REPORTING SERVICES (if you didn’t already in step 1)
Aside from just the BDNA_Reporting db, you also need an SRS reporting point (which is a role in SCCM 2007 or 2012) or a standalone SRS reporting site. The installation and configuration of SRS is beyond the scope of this article, so basically, get you some SRS.
4) INSTALL SQL Report Builder VERSION 3.0 OR HIGHER
(there’s a 2008 R2 version and a 2012 version that both show “version 3.x” on the splash screen…I honestly can’t tell the difference between them and they seem to be interchangeable without compatibility issues, but I can’t prove that)
SQL Report Builder is the report authoring interface for SQL Reporting Services. To me it’s a little more friendly and polished for the lay-person than BIDS (Business Intelligence Development Studio)/Visual Studio/Report Designer so I prefer having my report writers (including myself) use report builder. This is just a matter of preference. I know people who either prefer BIDS or are required to use it, so don’t let my preference deter you from using it if you have to or want to. I just can’t promise that everything here will work there because it’s been so long since I tried it for this pupose (2008)…but I’d bet money it’ll work fine.
5) CREATE A REPORTING FOLDER IN SSRS TO WORK IN
In order to keep all of our reporting work in one logical place, I’m going to create a folder called BDNA to hold it.
IMAGE #8 – BDNA folder to hold reports and other reporting objects.
6) CREATE A DATASOURCE THAT POINTS BACK TO OUR BDNA_REPORTING DB
In this case, I first created a hidden folder under the BDNA folder called DataSources which will hold any datasources that are required for any of our reports and then I created a “BDNA_Reporting” datasource which points to the reporting DB.
IMAGE #9 – BDNA_Reporting data source in a hidden DataSources folder
IMAGE #10 – BDNA_Reporting data source details
7) CREATE A REPORT TEMPLATE FROM WHICH ALL OTHER REPORTS WILL BE BASED
Over the last 4 years that I’ve been using this method for reporting, I’ve come up with some commonalities that people in our company wanted. They wanted certain logo information (top left and another bottom right) they’ve wanted execution information (bottom, so you know who ran the report, when, for how long, etc.), they wanted to know total number of pages, they wanted the report title and a description of the report up in the header in case they print or export to PDF, and they want a table with header row and footer row to hold the data (header has column titles, footer has summary numbers or info) and obviously the data rows hold the data. The template that I’ll provide looks like this once all of the properties have been replaced with expressions instead of string values (as shown in images #2 & #3 at the top)
You’ll need to include a datasource that points to BDNA_Reporting and a dataset that executes the usp_GetSRSProperties stored procedure we mentioned above, in order to get the values to show up as a dataset you can use in your property expressions. We’ll go into more detail in the next article.
NOTE: It appears all spartan and devoid of style when looking at it in the report builder (image #11), but as you can see below (image #12) when it renders it renders the way your reporting DB dictates it should.
IMAGE #11 – “Report Template v1.0.RDL” IN REPORT BUILDER
IMAGE #12 – “Report Template v1.0.RDL” RUN BY SRS, PULLING STYLE VALUES FROM BDNA_REPORTING
Notice how different it looks when rendered. Absolutely everything about how it looks comes from the BDNA_Reporting DB, including the logo images, the orange border at the top, the fonts, the colors, the cell borders on the table…all of it. And every one of them could be changed by changing a field in a table.
Now that we’ve got a template, you could start using it like a template. For example, you could now open it with Report Builder and save it as “Sample Report.RDL” for example. Just don’t save over the top of your template!
So, this is all I’ve got time for today, but it gives you a much more detailed look at what’s needed to get a db-driven style-based SRS reporting solution up and running. In the next article of the series due out really soon, I’ll actually provide the BDNA_Reporting DB, the report template, sample values for all the properties and a sample report.
#2 – John Nelson
