#2 SSRS Reporting - Style based approach (Part 4 - Template Objects)

This is the 4th 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)

Other articles in this series:

Part 1 - Style Based Approach

Part 2 - The ingredients and concept

Part 3 - Reporting DB delivered


In this post, I'll deliver the following:

  1. A list of all the objects in the template
  2. How to edit the BDNA_Reporting db to change the look and feel of the reports



As we've seen in the previous article, there are a lot of little objects in that report template.  Each of them serves a purpose and was included incrementally after a few years of feedback from different lines of business who told me what they wanted and didn't want to see in their reports.  Things like a report name AND a report description up at the top, execution information at the bottom (who ran it, how long it took, what folder the report is in, etc.), number of pages total in the report, the current page, spaces for upper and lower logos/images, etc.

The following image shows the report template (from part 3 which can be downloaded from HERE...rename to .RDL) with every object's name shown in red next to it.




As mentioned in previous articles, the reporting database (from part #3 HERE) contains a bunch of "property values" that correspond to the properties of the objects in the template.  If you open the database using SQL Management Studio or something similar, you'll see all the values that you can set (the values in the image below are presets I put in there to serve as an example):



Notice how the name of the properties includes the exact name of the object of the first image above PLUS the property name.  In this screen capture you can see properties like  rctExecutionInfo_BorderColor_Default, for example.  Intuitively, that value will control the default border color for the whole execution box at the bottom of the report.  Simply change that value to a valid color name or HTML color code and every report will render with that new color the next time they run.

(NOTE:  You have to edit the value AND THEN MOVE OFF THAT ROW for the change to get committed to the DB)

Obviously, you could put a front end editor around changing these values, but for me, this was quick and painless and once the values are setup, there's not much to do over the years so it wasn't worth my while making a UI for that. 


There's something you need to keep in mind, there are text values in the VALUE column but there are also VARBINARY values in the VARBINARYVALUE column.  For special values like the binary data that makes up the logo at the top or bottom of the report, you have to set the Mimetype property (a regular value field you can just type in) but you also need to somehow stream the actual PNG or JPEG or GIF or whatever image into the properties VARBINARYVALUE field.  You can't just type that by hand.


In order to facilitate streaming a file into a varbinary field, I've created a stored procedure in the BDNA_REPORTING db called "usp_ImportFileIntoVarbinaryProperty". 

For example, say you have a file called "C:\images\myLogo.png" (on the SQL server hosting this DB) then in order to import it into the VarbinaryValue field of the imgLogo1_Value property, you need to run this code:

USE [BDNA_Reporting]

EXEC    [dbo].[usp_ImportFileIntoVarbinaryProperty]
        @PropertyName = N'imgLogo1_Value',
        @IdentifyingAttribute = N'imgLogo1_Value',
        @Filename = N'c:\images\mylogo.png'

Once that runs (assuming you have rights enough to do that and SQL has rights to that file), the full binary data of that file will be in the varbinaryvalue field of that property and when the report renders, the image specified will be displayed on the report.


That's all there is to it.  Now you just have to start writing reports, editing the default values and seeing how your changes affect the rendering.

In this article we showed the objects that are in the reporting template, we showed how to edit the property values using SQL management studio as well as how to import a binary file like a logo into a varbinaryvalue field. 

In the coming articles, we're going to start writing sample reports that bring you value.  I'd like to know what reports you would find value in...drop me a line.


#2 – John Nelson

  • Created on .
Copyright © 2019 - The Twin Cities Systems Management User Group