Expressions can be written on certain fields based on user requirement for example if we see the Products table of Northwind database and would like to show all records but color the “UnitInStock” field having UnitInStock <=20 should come in red color and more than 20 with green color. We are going to design the report as shown below-
Step-1: Designed a tabular report quickly as it was given in Chapter-1 with the following query. Write click on the blank space below report designer and choose “Report Properties”
select ProductID,ProductName,QuantityPerUnit,UnitPrice,UnitsInStock from Products
A report properties window will open as
Navigate to the code tab for writing the custom code given below.
Public Shared Function SetColor(ByVal UnitInStock As Integer) As String
If UnitsInStock <= 20 Then
We have declare a function by the name SetColor which accepts one integer variable, i.e UnitsInStock. We set the initial value of the Setcolor to “Transparent”. If the value of UnitInStock is coming upto 20 then it will set row color to ”Red” otherwise “Green”. Choose the UnitInStock field and right click to open the TextBox menu from which we will choose the TextBox Properties as shown in image.
Step-2: In the TextBox Properties window that appears, click on the Fill option and click the fx Button.
Enter the following expression in the Expression Window
Click OK button and repeat the same for the QuantityPerUnit, UnitPrice column. And run the report. It will look like the image given below.
Step-4: So let us add two more functions (SetBoldFontWeight & SetItalicFontWeight ) in the Custom Code as done earlier .
Public Shared Function SetBoldFontWeight(ByVal UnitInStock As Integer) As String
If UnitInStock <= 20 Then
Public Shared Function SetItalicFontWeight(ByVal UnitInStock As Integer) As String
If UnitInStock <=20 Then
The Custom Code area will look as following screen.
Next in the UnitsInStock column right click to bring the TextBox properties and from the Font option, choose the fn for Bold and type the following code as shown in the image.
We will put the following code fx marked with red color circle and run the report. Similarly we can do for Italic setting for column “Product Name” and run the report. Please call the following code for it.
Please Notice the “Units in Stock” column changed to Bold and “Product Name” Changed to Italic font as per the function in code window.
Step5: “Setting Alternate Row Color. Paste the following code in the Code window of the report.
Public Shared Function SetAlternateRowColor(ByVal ProductId As Integer) As String
If ProductId Mod 2 = 0 Then
In this code, we are setting the color for even rows as “LightGrey” and other rows as default “white”. Write the below expression in fill tab of Product Name column as shown in the screen given below.
And Click OK and run the report. The report will look like the screen given below. This complete chapter-7
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