Keep It Simple and Fast refers to my focus during development of applications. In my opinion, applications should be very simple to use and do not have dozens of options. Every application should perform very fast, even in high transactions volume or high multi user environments.

Friday, November 4, 2011

Within SQL Server Reporting Services (SSRS), you have a lot of ways to style your reports. You can style your report in the way you want by using colors for text and background, fonts, font sizes etc... For instance:

However, these styles are 'hard coded' in the report. You can see this in the RDL file of the report.

When the report is rendered as a HTML page it is not possible to allow Cascading Style Sheets (CSS) to be used. It is even not possible to enable a style to be configured by the user. This is a missing feature in SQL 2008 R2. SQL 2012 (codename Denali) will also not support style sheets. Style sheets can be very useful. For instance:

Assume you have applied your corporate branding to all your reports. Now the marketing department decide to change the corporate branding. You need manually change all your reports.

As an account you have build a report for your customer X in his corporate branding. This reports can't be re-used for customer Y. You need to build a second version of the report in the corporate branding of company Y.

In this blogpost I will describe what you can do to apply styles to your reports.

The style definition will not be stored in the report but in the database. By doing this, you can easily add new styles without the need to change your report. The report will make use of a Style parameter called @ReportStyeId. This parameter will retrieve the Style definition and will apply it to the report.

Now, we have the style definition stored in the database. It's time to create a dataset to retrieve the style which can be used by the report Style parameter @ReportStyleId. The resultset of the dataset is 1 record with a column for every used style element. In this example I have 4 style elements. 1) Background color header 2) Background color details 3) Color header font 4) Color detail text font. In the report definition we will use expressions for every style configuration. This expression will use a column from the style dataset. To create a dataset with one record we need to use the PIVOT T-SQL syntax. Unfortunately the PIVOT T-SQL syntax is not supported as query text. Therefor a stored procedure with the PIVOT statement will be created. The dataset will use this stored procedure with a styleID parameter.

1 comment:

If you don't plan on editing the styles with some sort of GUI, there's a simpler way to do this. You can create a shared dataset that just has the data in it itself. It requires you to specify a Data source (can just use the same one you're already using for report data) but in the Query box you can just put something like this:select '15pt' as MainFontSize, 'Arial' as MainFontFace

As long as you have that .rsd file which all your reports are referencing, you can easily update the styling and you don't have to touch the DB.

But thanks for this idea! I was just using public variables in the Code section of the report which works for things like styling a ton of columns in a grid for a single report but still would require cut and paste to update multiple reports at once.

About Me

My name is André van de Graaf, I'm working for Exact Software in the Research team as Principal Research Engineering. I'm located in Delft, The Netherlands. In my work i have a strong focus on performance of applications. Beside performance I want to keep everything as simple as possible. What is the perfect balance between performance and functionality?