Using Custom Code in SSRS

Home > Blogs > SQL Server > Using Custom Code in SSRS

Using Custom Code in SSRS

Like This Blog 6 Peter Avila
Added by December 15, 2014

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.

001-Using-Custom-Code-in-SSRS

Here’s what the report will look like when we’re done:

002-Using-Custom-Code-in-SSRS

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:

003-Using-Custom-Code-in-SSRS

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…

004-Using-Custom-Code-in-SSRS

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.

005-Using-Custom-Code-in-SSRS

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.

006-Using-Custom-Code-in-SSRS

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

Videos You May Like

A Simple Introduction to Cisco CML2

0 3877 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Creating Dynamic DNS in Network Environments

0 641 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader teaches how to create Dynamic DNS zones in Network Environments. Video Transcription: Now that we’ve installed DNS, we’ve created our DNS zones, the next step is now, how do we produce those … Continue reading Creating Dynamic DNS in Network Environments

Cable Testers and How to Use them in Network Environments

0 724 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Write a Comment

See what people are saying...

  1. Pingback: SSRS How to Display the Elapsed Processing Time of a Report

Share your thoughts...

Please fill out the comment form below to post a reply.