Chapter-20-Using CSS Stylesheet in SSRS

The SSRS developer uses the fonts, colors, and layout that you specify in the report definition in Report Designer. These styles are created inline. SSRS developer often require Cascading Style Sheets to enhance the look and feel of SQL Server reports. Microsoft SSRS uses default cascading style sheets (i.e. HtmlViewer.css ) files that help the SSRS developer to define the styles in their reports. It is possible but not recommended to modify the default style sheet. The developer can use their own style sheet but it requires Configuration of the reporting services to use user defined or custom style sheet.

Download the Source Code for Chapter-20-Using CSS Stylesheet in SSRS



The custom cascading style sheet (.css) file must be valid and it must be located in the ‘Styles’ folder at following default location –

<drive>:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer\Styles.

e.g.

“C:\Program Files\Microsoft SQL Server2012\MSRS11.MSSQLSERVER2012\Reporting Services\ReportServer\Styles“.
Anothor method is their which will do the same thing but by storing the element & values of CSS stylesheet in database and providing the option to choose CSS Stylesheet Properties at runtime. This is very advantageous as one can change the style of Reporting at Runtime by only modifying the parameter value. This option provides the flexibility of changing the look and feel of report customized at user level.

Prerequsite
1.Create Datasource connected with SQL Server 2012 sample Database Northwind “NorthwindDataSource”. (as expianed in SSRS Tutorial “Chapter-1-Creating a basic SSRS Report”)
2.Create a Report Server Project “BasicSSRSChapter20” using Visual Studio 2010 or higher Version (as expianed in SSRS Tutorial “Chapter-1-Creating a basic SSRS Report”). You can use the basic SSRS Report using the query.

SELECT  TOP (20) CustomerID, CompanyName, ContactName, ContactTitle, Address FROM  Customers

Step-1 Open  SQL Server Enterprise Manager, click the ‘‘New Query’’ button to execute the following code using the database “Northwind” to Create table TableCssStyle” as shown in the image

CREATE TABLE [dbo].[TableCssStyle](
[stysln] [int] NULL,
[Css_control_name] [varchar](100) NULL,
[Css_control_Value] [varchar](25) NULL
)

Table CSS Style
Table CSS Style

Run the following script to insert the values in the table “TableCssStyle”. These are actually two styles which will be called later.





INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N'REPORT_FOOTER_FONT_NAME', N'Verdana')

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’REPORT_FOOTER_FONT_SIZE’, N’8pt’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1,N’REPORT_FOOTER_FONT_WIGHT’, N’Default’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1,N’REPORT_FOOTER_TEXT_COLOR’, N’Black’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1,N’REPORT_HEADING_FONT_NAME’, N’Verdana’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1,N’REPORT_HEADING_FONT_SIZE’, N’16pt’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’REPORT_HEADING_FONT_WEIGHT’, N’Bold’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’REPORT_HEADING_TEXT_COLOR’, N’DarkRed’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_DETAIL_BG_COLOR’, N’White’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_DETAIL_FONT_NAME’, N’Verdana’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_DETAIL_FONT_SIZE’, N’12pt’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_DETAIL_FONT_WEIGHT’, N’Default’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_DETAIL_TEXT_COLOR’, N’DarkBlue’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_HEADER_BG_COLOR’, N’#4e0000′)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_HEADER_FONT_NAME’, N’Verdana’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_HEADER_FONT_SIZE’, N’14pt’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_HEADER_FONT_WEIGHT’, N’Bold’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (1, N’TABLE_HEADER_TEXT_COLOR’, N’White’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’REPORT_FOOTER_FONT_NAME’, N’Arial Narrow’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’REPORT_FOOTER_FONT_SIZE’, N’9pt’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’REPORT_FOOTER_FONT_WIGHT’, N’Bold’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’REPORT_FOOTER_TEXT_COLOR’, N’Red’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’REPORT_HEADING_FONT_NAME’, N’Verdana’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’REPORT_HEADING_FONT_SIZE’, N’16pt’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’REPORT_HEADING_FONT_WEIGHT’, N’Bold’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_DETAIL_BG_COLOR’, N’White’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_DETAIL_FONT_NAME’, N’Book Antiqua’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_DETAIL_FONT_SIZE’, N’10pt’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_DETAIL_FONT_WEIGHT’, N’Default’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_DETAIL_TEXT_COLOR’, N’Indi’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_HEADER_BG_COLOR’, N’#4e0000′)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_HEADER_FONT_NAME’, N’Book Antiqua’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_HEADER_FONT_SIZE’, N’12pt’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_HEADER_FONT_WEIGHT’, N’Bold’)

INSERT [dbo].[TableCssStyle] ([stysln], [Css_control_name], [Css_control_Value]) VALUES (2, N’TABLE_HEADER_TEXT_COLOR’, N’White’)

 

Now run the following sql script to create procedure to transpose the row to column of table “TableCssStyle” as shown in the image.

Create procedure [dbo].[ReturnCSSValue]
@slyslnpro int
AS
begin
--Creation Date: 01/02/2017
--Created by: texalab.com
--Purpose: This Procedure convert the rows of table TableCssStyle to Transpose columns as required in SSRS Report using CSS Stylesheet.
DECLARE @slysln int
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
--set @slysln=1--Setting the value of the stylesheet to be used --1 for Diffrent and 2- for Diffrent style
set @slysln=@slyslnpro
select @cols = STUFF((SELECT ',' + QUOTENAME([Css_control_name])
from [TableCssStyle]
where [stysln]=@slysln group by [Css_control_name], [stysln]
order by [stysln]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = N’SELECT ‘ + @cols + N’ from
(
select css_control_Value, [Css_control_name]
from [TableCssStyle] WHERE [stysln]=’ + STR(@slysln) + ‘
) x
pivot
(
max(css_control_Value)
for [Css_control_name] in (‘ + @cols + N’)
) p ‘
print @query
exec sp_executesql @query;
end
–exec ReturnCSSValue 2

Create Procedure Window
Create Procedure Window

As the procedure is using dynamic sql , the report Data set will create parameter but not columns under the Dataset,  to overcome this, execute procedure given in the last of the procedure “exec ReturnCSSValue 1”. And copy the sql scripts coming in the message tab as shown in the image.

copy the sql scripts coming in the message tab as shown in the image
Copy the SQL Scripts coming in the message tab as shown in the image

Paste the same to our procedure “ReturnCSSValue” before “end” statement and comment “print @query” and “exec sp_executesql @query” lines and now alter the procedure as shown in the image , this will a query with static sql which will allow to create Dataset as desired at SSRS.

Select Query for report parameters
Select Query for report parameters

Step2: Now moved to the BasicSSRSChapter20 Project and open the report “Chapter-20-Using CSS Stylesheet in SSRS“ as shown in the image. Open data set properties of the report.

Open data set properties of the report.
Data set properties of the report.

Now give the name of the new Data Set as “DSCSS_Style” and click on ok button. Now the screen will look something like.

DSCSS Style
DSCSS Style

Now your dataset “DSCSS_Style” is created with a parameter @slyslnpro which will be used for choosing the stylesheet at run time.

Step-3: Now open procedure “ReturnCSSValue” in sql server and delete the static select statement from the procedure and uncomment “exec sp_executesql @query” statement and and alter the procedure.

SELECT [REPORT_FOOTER_FONT_NAME],[REPORT_FOOTER_FONT_SIZE],[REPORT_FOOTER_FONT_WIGHT],[REPORT_FOOTER_TEXT_COLOR],[REPORT_HEADING_FONT_NAME],[REPORT_HEADING_FONT_SIZE],[REPORT_HEADING_FONT_WEIGHT],[TABLE_DETAIL_BG_COLOR],[TABLE_DETAIL_FONT_NAME],[TABLE_DETAIL_FONT_SIZE],[TABLE_DETAIL_FONT_WEIGHT],[TABLE_DETAIL_TEXT_COLOR],[TABLE_HEADER_BG_COLOR],[TABLE_HEADER_FONT_NAME],[TABLE_HEADER_FONT_SIZE],[TABLE_HEADER_FONT_WEIGHT],[TABLE_HEADER_TEXT_COLOR] from
(
select css_control_Value, [Css_control_name]
from [TableCssStyle] WHERE [stysln]= 2
) x
pivot
(
max(css_control_Value)
for [Css_control_name] in ([REPORT_FOOTER_FONT_NAME],[REPORT_FOOTER_FONT_SIZE],[REPORT_FOOTER_FONT_WIGHT],[REPORT_FOOTER_TEXT_COLOR],[REPORT_HEADING_FONT_NAME],[REPORT_HEADING_FONT_SIZE],[REPORT_HEADING_FONT_WEIGHT],[TABLE_DETAIL_BG_COLOR],[TABLE_DETAIL_FONT_NAME],[TABLE_DETAIL_FONT_SIZE],[TABLE_DETAIL_FONT_WEIGHT],[TABLE_DETAIL_TEXT_COLOR],[TABLE_HEADER_BG_COLOR],[TABLE_HEADER_FONT_NAME],[TABLE_HEADER_FONT_SIZE],[TABLE_HEADER_FONT_WEIGHT],[TABLE_HEADER_TEXT_COLOR])
) p

Step-4: Now open the report in design view and select the textbox with lable “Chapter-20-Using Stylesheet in SSRS“ and click on properties to open the properties dialouge box as shown in the image and click on expression.

click on properties to open the properties dialouge box
Click on properties to open the properties dialogue box

Now click on the Dataset Category and then in Item select “DSCSS_Style” you will be able to see all the style. Now double click on the appropriate value i.e “REPORT_HEADING_TEXT_COLOR” as shown for text-color and click ok.

Change the appropriate value
Change the appropriate value

Similarly set the other expression for Heading of the Report such as

REPORT_HEADING_FONT_NAME,REPORT_HEADING_FONT_SIZE,REPORT_HEADING_FONT_WEIGHT

Similary we can set the expression to complete TABLE HEADER and TABLE DETAIL Row of the table in the design view . Some of the values for TABLE HEADER are.

TABLE_HEADER_FONT_NAME, TABLE_HEADER_BG_COLOR, TABLE_HEADER_FONT_SIZE,TABLE_HEADER_FONT_WEIGHT,TABLE_HEADER_TEXT_COLOR

Now preview the report and pass the parameter : 1 and note down the look and feel of the report.

Preview Report
Preview Report

Again preview the report and pass the parameter: 2 and see the difference.

Preview the report and pass the parameter: 2
Preview the report and pass the parameter: 2

 

Previous Chapters

Chapter-1: Creating a basic Tabular SSRS Report using Report Wizard
Chapter-2 : Report Deployment on SQL Server 2012 Reporting Server
Chapter-3:Opening/Calling the deployed SQL Server Reporting Server report from Asp.net
Chapter-4 Creating SSRS Matrix Report / Cross Tab Report

Chapter-5 Creating a Sub Report in SSRS
Chapter-6: Creating a Drill Down Report in SSRS
Chapter-7: Working with expressions and custom code in SSRS
Chapter-8-SSRS (Reporting Services) Working with Calculated fields
Chapter-9-Sorting of Column and Custom Paging in SSRS

Chapter-10 : Creating and Deploying Web Services for SSRS
Chapter-11 : Creating SSRS Report by consuming Web Service Data Source
Chapter-12 : Working with Data Bars in SSRS
Chapter-13-Displaying Color in Data Bar Based on Condition
Chapter-14- Displaying Data Labels along with Data Bars in SSRS
Chapter-15-Displaying Indicators in SSRS
Chapter-16-Creating SSRS Linked Report
Chapter-17 Adding or Creating Drop Down Parameter List in SSRS
Chapter-18 Creating Group above or stepped report in SSRS

Chapter-19-Creating Multi Value Parameter Report in SSRS

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

fourteen − 3 =