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.
First we need to create some tables to store the style definition.
CREATETABLE SSRS_ReportStyle
(ReportStyleId INT IDENTITY (1, 1),
StyleName VARCHAR (80))
CREATE TABLE SSRS_Element
(ElementId INT IDENTITY (1, 1),
ElementName VARCHAR (80),
ElementDefaultValue VARCHAR (80))
CREATE TABLE SSRS_ReportStyleElements
(ReportStyleId INT,
ElementId INT,
ElementValue VARCHAR (80))
Add some style definition data to the tables:
-- SSRS_Report Style
SET IDENTITY_INSERT [SSRS_ReportStyle]
ON
INSERT INTO [SSRS_ReportStyle]
(ReportStyleId
,StyleName
)
VALUES (1
, 'Blue Yellow')
INSERT INTO [SSRS_ReportStyle]
(ReportStyleId
,StyleName
)
VALUES (2
, 'Red')
SET IDENTITY_INSERT [SSRS_ReportStyle]
OFF
-- Insert SSRS_Elements like for instance font colors and background colors. Each element will
-- have a default value which will be used in case this element is not defined in the style.
SET IDENTITY_INSERT [SSRS_Element] ON
INSERT INTO [SSRS_Element]([ElementId],[ElementName],[ElementDefaultValue] )
VALUES (1,'TABLE_HEADER_BG', 'WHITE')
INSERT INTO [SSRS_Element] ([ElementId],[ElementName],[ElementDefaultValue] )
VALUES (2,'TABLE_HEADER_TEXT','BLACK')
INSERT INTO [SSRS_Element] ([ElementId],[ElementName],[ElementDefaultValue])
VALUES (3,'TABLE_DETAIL_BG','WHITE')
INSERT INTO [SSRS_Element] ([ElementId],[ElementName],[ElementDefaultValue])
VALUES (4,'TABLE_DETAIL_TEXT','BLACK')
-- SSRS_Report Style Elements, for every style element we can define the color, size etc.
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 1, 1, 'BLUE' )
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 1, 2, 'WHITE' )
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 1, 3, 'YELLOW')
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 1, 4, 'BLUE')
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 2, 1, 'RED' )
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
VALUES ( 2, 2, 'YELLOW' )
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.
CREATE PROCEDURE [dbo].[SP_SSRS_ReportStyle]
(@ReportStyleId AS int) AS
(SELECT [TABLE_HEADER_BG],[TABLE_HEADER_TEXT],
[TABLE_DETAIL_BG], [TABLE_DETAIL_TEXT]
FROM
(SELECT e.[ElementName] , ISNULL(ElementValue,ElementDefaultValue) AS Value
FROM [SSRS_Element] e WITH (NOLOCK)
LEFT JOIN [SSRS_ReportStyleElements] rse WITH (NOLOCK)
ON rse.[ElementId] = e.[ElementId] AND
rse.[ReportStyleId] = @ReportStyleId
) AS ReportStyleElements
PIVOT (MIN(Value)
FOR ElementName IN
([TABLE_HEADER_BG],[TABLE_HEADER_TEXT],
[TABLE_DETAIL_BG], [TABLE_DETAIL_TEXT])) AS [Elements]
)
If you want to use more style elements, you need to update the stored procedure to retrieve the added elements.
Now all preparations are done to implement the styles in your reports.
- Open your report in BIDS.
- Add dataset SSRSStyles to the report using Stored Procedure: SP_SSRS_ReportStyle
- Create a parameter: @ReportStyleId with datatype Integer, allow null value, visible. Now the report can use the ReportStyle parameter.
- Configure the background color of the header in your report to use the result set of the dataset. Do the same for the color of the font in the header.
- Do the same for the detail rows in your report.
The report is ready to use. See the example for ReportStyle1
Example with ReportStyle 2
Enjoy it to implement styles to your SSRS reports.
