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.

2nd 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

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

   

1_SQL_Server_CSS_kind_of_Approach_for_formatting_Reports_in_SSRS

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

2_SQL_Server_CSS_kind_of_Approach_for_formatting_Reports_in_SSRS

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 TwitterFollow me on FaceBook

   

4 Comments on “SQL Server: CSS kind of Approach for formatting Reports in SSRS”

  1. Hi Rakesh,

    Good one. I have tried this and it worked 🙂 Yes, it will be useful when applying the same styling to a set of reports. We can just define this ones and the developers can use this to have a uniform formatting to the reports.

    Thanks,

    Amit Karkhanis

  2. hi , when i map the dynamic sp into RDLC ,i got the error incorrect syntax near ‘!’. fields are not listing in the dataset.

    Can u help me to get the result .

    Regards

    Elaya

  3. I have tried to get this going and it works great for everything except for charts, For a chart you want to list an array of colors {“#0F5FA9”, “#1AA755”, “#DD7E2D”, “#E9DE3A”, “#915F39”, “#8361A8”, “#86AED3”} and have them used as needed. I have yet to get this to work without hardcode the colors within the code section of SSRS.

    Do you know if it is possible to do?

Leave a Reply

Your email address will not be published.