Chapter-7: Working with expressions and custom code in SSRS

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-

Download Full Tutorial for Chapter-7-Working-with-Expression-and-Custom-Code in PDF format



Download Source Code for using Expressions and Custom Code in SSRS Reporting

Working with expressions and custom code

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

Right click on the blank space below report designer and choose “Report Properties”
Right click on the blank space below report designer and choose “Report Properties”

A report properties window will open as

Report properties window
Report properties window

Navigate to the code tab for writing the custom code given below.

Public Shared Function SetColor(ByVal UnitInStock As Integer) As String
SetColor= “Transparent”
If UnitsInStock <= 20 Then
SetColor= “Red”
else
SetColor= “Green”
End IF
End Function

Writing the custom code
Writing the custom code

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.

Right click to open the TextBox context menu
Right click to open the TextBox context menu

Step-2: In the TextBox Properties window that appears, click on the Fill option and click the fx Button.

click on the Fill option and click the fx Button
Click on the Fill option and click the fx Button

Enter the following expression in the Expression Window

=Code.SetColor(Fields!UnitsInStock.Value)

Enter expression in the Expression Window
Enter 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.

Preview of Report
Preview of Report

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
SetBoldFontWeight= “Default”
If UnitInStock <= 20 Then
SetBoldFontWeight= “Bold”
End IF
End Function


And
Public Shared Function SetItalicFontWeight(ByVal UnitInStock As Integer) As String
SetItalicFontWeight= “Default”
If UnitInStock <=20 Then
SetItalicFontWeight= “Italic”
End IF
End Function
The Custom Code area will look as following screen.


Custom Code area
Custom Code area

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.

=Code.SetBoldFontWeight(Fields!UnitsInStock.Value)

SSRS Expression and Cusotom Code

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.

=Code.SetItalicFontWeight(Fields!UnitsInStock.Value)

Please Notice the “Units in Stock” column changed to Bold and “Product Name” Changed to Italic font as per the function in code window.

Preview

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
SetAlternateRowColor= “Default”
If ProductId Mod 2 = 0 Then
SetAlternateRowColor= “LightGrey”
End IF
End Function
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.

=Code.SetAlternateRowColor(Fields!ProductID.Value)

Click OK and run the report
Click OK and run the report

And Click OK and run the report. The report will look like the screen given below. This complete chapter-7

Preview Report
Preview Report


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

 

Leave a Reply

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

*

one × 3 =