SQL Server: CSS kind of Approach for formatting Reports in SSRS

Till now, SSRS does not support Cascading Style Sheets (CSS) or themes & skins that are available with HTML or .Net development. The approach that I am going to describe is very helpful and I hope it will increase productivity by avoiding repetitive and laborious report element formatting that we usually do during report development. This approach may not pay well during initial development phase but this is certainly much beneficial when we need to change color scheme of reports as per changed requirement.

The backbone of this idea is to have a set of user-defined names that will be applied as property of different elements/attributes in “Properties” window.

To implement this idea, we need to have a table that will store all required elements, their properties and value. A sample table for this purpose can be created as:

CREATE TABLE [dbo].[REPORT_CSS](
    [SLNO] [tinyint] IDENTITY(1,1) NOT NULL,
    [Entity] [varchar](30) NULL,
    [BackgroundColor] [varchar](30) NULL,
    [Font] [varchar](30) NULL,
    [Size] [varchar](30) NULL,
    [FontColor] [varchar](30) NULL,
    [StyleBold] [varchar](30) NULL,
    [StyleItalic] [varchar](30) NULL,
    [StyleUnderline] [varchar](30) NULL
) ON [PRIMARY]

Here, “Entity” column refresh to reports logical entity like “Header”, “Group_1”, “Group_2” and so on.

Now, populate the table with required report entities and value for different attributes.

2^nd step is to create a stored procedure that will always return single row but should have all attributes for all elements. You can use the code below to get that done.

Create Procedure Get_Report_CSS
As
Begin
    declare @SQL varchar(max)
    select @SQL  = 'SELECT * FROM '
 
    select @SQL = @SQL + '
    (
          select 
                BackgroundColor ' + Entity + '_BackgroundColor, 
                Font ' + Entity + '_Font, 
                Size ' + Entity + '_Size, 
                FontColor ' + Entity + '_FontColor, 
                StyleBold ' + Entity + '_StyleBold, 
                StyleItalic ' + Entity + '_StyleItalic, 
                StyleUnderline ' + Entity + '_StyleUnderline
          from
                report_css
          where entity = ''' + entity + '''
    ) as [' + Entity + '] cross join'
    from report_css
 
    select @SQL = @SQL + '!'
    select @SQL = replace (@SQL, 'cross join!', '')
 
    exec (@SQL)
End

3^rd step is to have dataset in report (you need this in every report) to get data from “Get_Report_CSS”

The 4^th and last step is to write an expression to change the desired property.  Following image will help you to understand that.

That’s all.

You are all set to use CSS like functionalities in your report. Whenever you need to change color scheme or your report just change the values in REPORT_CSS table and you are done with all the cumbersome and laborious activities of changing attribute values in your reports.

Regards

Rakesh Mishra

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook