Using Custom Code in SSRS
While expressions allow you to use limited VB.Net to create advanced dynamic functionality in your SQL Server Reporting Services (SSRS) reports, custom code allows you to leverage much more of the power of VB.Net.
The focus of this article is not on writing VB.Net code, but on how to create and use code in SSRS. As such, this article uses an example with very simple VB.Net code that I assume you understand. Of course, the more VB.Net you know, the more you will be able to do with what you learn here.
Our example uses the territory sales report shown below to conditionally format the total sales so that it’s red if it’s below 5 million; blue if it’s below 10 million; green if it’s below 15 million; and black otherwise.
Here’s what the report will look like when we’re done:
One way to implement this is with an expression in the color property of the textbox that displays the total sales. The expression would use nested Immediate-If functions (IIF()) to decide what color to use based on the value of the total sales. Here it is:
The 1-line limitation of expressions often forces us to nest functions. Nesting can make expressions difficult to write, understand, and maintain. Our expression nests IIF() functions to implement Else-If logic that is implemented much more clearly as a block structure in code (a block structure is one that occupies more than one line of code).
To write code in a report, select the Report menu and then Report Properties…
Create code in the Code section. In the example shown below, we created a function called GetColor that receives as input the total sales in a parameter called Val2Color with a data type of Single. The function returns as output the color as a String value. You can create as many functions and sub-procedure methods as you need.
To use the function, just call it from the Code object. Here is a call to the function from the same place where we had the expression earlier—in the color property of the textbox that displays the total sales.
Don’t worry about the red squiggly. There is no error, here. SSRS is just a little confused; this won’t prevent the report from running.
Enjoy using code in SSRS!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ
Agile Methodology in Project Management
In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management
JavaScript for C# Developers – September 24, 2014
In this video Dan Wahlin demonstrate the similarities and differences between C# and JavaScript and the future of JavaScript (ES6).
Detailed Forensic Investigation of Malware Infections – April 21, 2015
In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.
Write a Comment
See what people are saying...
Share your thoughts...
is it possible to use javascript in the custom code or, at least, a dynamic hypertext link.
Thank you in advance.
Hi Peter, Thanks for writing useful article, also could you please share your inputs how to display a popup while SSRS report is taking long time to run in SharePoint integrated mode. Once user click on popup saying that “the report is taking longer time to run, please click OK if you want the report to be sent to your email once executed” and then send the report to user’s email id.
Nice bro!
Thank you for posting, this is very useful.
How to write multiple Custom codes with in one report for another fields also
like i have salary (with color),for full name (fname+lname)
Pingback: SSRS How to Display the Elapsed Processing Time of a Report