This is the 3rd part in a series designed to help you build a style-based SQL Reporting Services (SRS) reporting system using a DB, an .RDL template and SRS (similar to CSS or cascading stylesheet like functionality)
In this post, I’ll actually deliver the following:
- A “style” database called BDNA_Reporting
This will contain a default set of style properties that will give you a starting point for your own style.
- A default report template
This will be an .RDL file which can be opened using Report Builder 3+ (SQL 2008 R2+) and then immediately saved as the basis of a new tablix (basically a table) style report.
- A sample report built off the template.
SETTING IT ALL UP
In order to make it the most universally importable, I’ll just attach some TSQL scripts here that will create the BDNA_REPORTING database (the style DB) and populate it with any tables, functions, stored procedures and the 500+ sample values (table rows) that I’ve put together to make this all look good.
- SQL 2008 R2
(honestly, I wouldn’t be surprised if this worked with earlier versions, but it was written with 2008 R2+ in mind so I make no promises for earlier versions)
- Ability to connect to said SQL server and something like SQL Management Studio that will let you connect to SQL and execute TSQL code
- Permissions to CREATE/ALTER tables, functions and stored procedures and insert rows into those tables.
- Connect to your SQL server using SQL Management Studio
- Execute the following TSQL script to create the BDNA_Reporting database:
(NOTE: Open the script in SQL Management Studio and hit CTRL+ALT+M first to enter the DB and Txlog folders first so it knows where to put the DB and Txlog)
- Execute the following TSQL script to create all the tables, stored procedures and other SQL objects that will support BDNA_Reporting:
- On your SQL Reporting Services (SRS) instance, from the web interface, create a new folder called BDNA
- Inside of the BDNA folder, create a hidden folder called DataSources
*you will need to click DETAILS VIEW to see the new hidden folder you just created
- Inside of the DataSources folder, create a New Data Source called BDNA_Reporting and use credentials most appropriate for your environment that would have the ability to connect and query the BDNA_Reporting database (hint, I don’t think it would be good to grant everyone the ability to directly connect and query this DB, but rather an execution account or some reporting user saved on the reporting server)
- Now, back in the BDNA folder, let’s upload the report template from this file BDNA Report Template v1.0 (note: download it first, then rename it from .TXT to .RDL)
- Let’s verify the datasource of the template is pointing to the new shared BDNA_Reporting datasource we created in step 6. Click MANAGE on the new template
- Click on DATA SOURCES
- Verify that “A shared data source” is selected and actually points to the datasource from step 6
If you need to, you could also click BROWSE and browse directly to the datasource from step 6
You may also click APPLY to the datasource if there were any changes made that need applying
- From here, just click on the template name to execute it and make sure all the parts are working. If they are, you should see the template render since technically, it’s a report.
How it should look if it’s all working:
There you go!!! You have a working BDNA_Reporting system which will render all your reports the exact same way, like they were all using the same cascading stylesheet (CSS) template!
HOW TO USE THE TEMPLATE
“OK, now what?” you may ask. Well, the first thing I’ve got to say is DON’T EVER SAVE A REPORT OVER THE TOP OF YOUR REPORT TEMPLATE!!! I’ve done this a few times early on and it’s so frustrating. You open the template in report builder, add a bunch of stuff to make a nice pretty report and instinctively hit the SAVE button. Now you don’t have the default template, you’ve got a finished report and NO template. Always keep an extra copy of the template lying around, or I guess now you can refer back to this post to get another copy. Alright, I’ve said it. Now, onto the template…
So, the gist of the template you’ve got here is you can open it with Report Builder and immediately save it with a new report’s name. That report will have all the same properties and style expressions in it as the template so you don’t have to muck with the styling everytime you make a new report, PLUS they’ll all get changed when you modify the style DB (more about that in a minute).
- So, with Report Builder (3.0 or higher) point to http://yourSRSserver/ReportServer and find the template in the BDNA folder that we created and open it
- Now, once the template is open, save it with a new name so you don’t accidentally overwrite it!
- Take a look at this graphic, I do assume some understanding of SRS and Report Builder, so I’m not going to define all the components of the report builder screen, but here are a few highlights from the template.
- Once you have a newly edited/saved report, you can run it and see how it looks. If you follow this process of opening the template and saving as new report, all your reports should look and behave just the same!
This should be a lot for you to play with for now…but in the next part of this series, I’ll define all the objects on the template and show you how to edit the BDNA_Reporting db to change the look and feel of every report built off that template and then we can all start building a bunch of reports together and start sharing reports amongst each other and as long as you’ve followed the same steps as above, the reports will work on everyone’s systems.
#2 – John Nelson