Boyan Penev on Microsoft BI

Article published in SQL Server Central on 2009/03/26

As it was previously discussed by Adam Aspin in his series Add Styles to Your Reportng Services Reports on SQLServerCentral, it is very useful to be able to change various report attributes, such as colours and borders properties, in a centralised way just like by using CSS files or Skins. Currently, Reporitng Services does not allow us to use a similar approach, so Adam showed us how to apply a “style” to a report by using custom code in Reporting Services – either by embedding the code for all attriibutes directly in the report, or by referencing an external source. However, there is another way to handle such attributes and that is by storing them in a database table. There are certain advantages in doing so, but there could be some disadvantages, as well.

I will proceed to show how to build a report utilising this apporach and then I will discuss its strengths and weaknesses.

Database Table

I would suggest to keep your database tables stroing values such as report attributes, Reporting Services environment settings (for using dynamically generated javascript) and other dynamic report properties separated by naming them in a unified way. I have used the CTL (for control) prefix in the past. For illustrative purposes I will provide a simple definition of a database table called CTLReportColours:

CTLReportColours (
HeaderColour nvarchar(10),
FooterColour nvarchar(10),
BodyTextColour nvarchar(10)
)

As actual values we can store either the Reporting Services colour name (WhiteSmoke, SteelBlue, etc), or the colour codes (#00AA00, #CCBB66, etc). I am mentioning this because I have had issues with printers in the past, which recognise Red as Pink and changing to hex colour codes fixed the problem. This should be the topic for another article, so I will drop the subject here.

Stored Procedure

We can either retrieve the values from the database table in a sotred procedure we are already using on our report, or we can create a new one, specifically built for getting report attributes. We can use the first approach if our report uses one data set only. Then we can assign all report items to that data set and simply use the Fields collection items in our expressions. However, if we have multiple data sets assigned to different report items, such as tables(ixes), we may find that we need to code the retrieval of our report attributes in many many stored procedures. To overcome this limitation, we can ceate a spearate stored procedure, which returns the set of values stored in the database table. A simple example is code like:

CREATE usp_CTL_Get_Report_Colours
AS
BEGIN
SELECT HeaderColour
, FooterColour
, BodyTextColour
FROM CTLReportColours
END

Data Set

In Reporting Services we can use usp_CTL_Get_Report_Colours to construct a data set, which will have three fields named like the column names.

Usage

To use a separate data set means that we need to be able to use its fields in some tables which already have a data set assigned to them. Since we can have only one data set per report element, we need to create separate parameters for each of the fileds in the attributes data set we have created. In our case, we need to create three parameters: HeaderColour, FooterColour and BodyTextColour. All of these should be hidden and should be populated by our data set in the outermost report only – if we have subreports, they should get their values from the parent report, so we avoid multiple execution of the usp_CTL_Get_Report_Colours stored procedure.

Conclusion

The described method for handling report attributes can be preferable because:

· We do not need to know anything about coding to create dynamic reports

· Reports are dynamic with no need to re-deploy them

· Report attributes are in a database table, which makes their management easy

· Various management screens can be built on top of the database table in .NET with relative ease

· Retireving the values through a stored procedures allows us to pass back parameters, which then can drive what colours get displayed – making the retrieval flexible if we need to display different colours for different departments or people

· Centralised and secured storage to all report attributes – environment settings, styles, etc.

The drawback is one as I see it: having to create multiple report parameters in all our reports. In my opinion it is a much smaller issue than having to manually change report properties with every change in the requirements and overall it is well worth the effort.

If you enjoyed this post, make sure you subscribe to my RSS feed!

Related posts:

1. Interactive reports – passing content back to the database
2. Speed up MDX query preparation in Reporting Services
3. Advanced javascript in Reporting Services

Boyan Penev SSRS Reporting Services