SSRS Expression: Tips and Tricks

Introduction

Expressions are a very powerful feature of SSRS. They enable us to show fields at run time depending on conditions. Generally expressions are written in Visual Basic and there are many built-in functions provided. We can have also custom code for expressions. Expressions begin with an equal sign (=). In this article, I will explain some of the tips and tricks when working with expressions.

Tip 1: Display column dynamically

Using an expression we are also able to show a report column dynamically. Suppose my dataset has fields like EmployeeId, name, code, address, phone and email. I have created a report using this dataset and within the report I want to show employeeId, Code and Name by default and the basic report format looks as shown below.

Display column dynamically

In the last column, the report will show Address, phone or email depending on the parameter (“Show field” in the preceding image).

The Parameter definition is as shown in the following.

As the solution, add the following expression in the last column header.

=Parameters!dyamicfiled.Value

column header

Add the following expression in the "last column value/detail".

=Switch(Parameters!dyamicfiled.Value="Address",
Fields!Address.Value,Parameters!dyamicfiled.Value="Phone",
Fields!Phone.Value,Parameters!dyamicfiled.Value="Email",
Fields!Email.Value)

expression

Now, render the report with the parameter set to "Address" and then "Phone" and then "Email". The output with each parameter selection is as shown below.

parameter selections

Here, both the header and details fields of the last column are changed based on the parameter value selection. We can use IIF or any other conditional statement instead of switch.

Tip 2: Adding Comments in Expressions

As we know, comments are useful as self-documentation and make it easy to understand and maintain the code. SSRS allows us to provide comments as part of an expression.

To show this, I have added one text box to the report and used the following expression in it.

= "You are select show fied value : " + Parameters!dyamicfiled.Value

fied value

Output: The report output should contain the following text: "You are select show filed value:".

<parmeter value>> “

parmeter value

The following point needs to be considered before using a comment in the expression.

The Comment must implemented with a single quote.
A Comment can be added in the same line or the next line within an expression.
Comments cannot be added in the beginning or middle of the expression.
No concept of multi-line comments like C#.

Tip 3: Referring to data from another container content

Report Items allow us to reference a value from a TextBox in another TextBox in another part of the same report. The SSRS report Items collection is a good feature and could be used in various scenarios. In the following example, I have copied the text data from one text to another text box of a report.

Output:

Output

Tip 4: Referring to Contents within Textboxes

We can insert a value into a text box without using an expression, field or parameter present within the TextBox. In the following example I show that when the value of a text box can change the background color of the text box will change.

Here I am using a hard-coded value. We can also get the value from a parameter, another expression or from the field instead of this hardcoded value.

Output

hardcode value

Tip 5: Visual Basic function

SSRS allows us to use Visual Basic functions in expressions. There are many categories of functions available with SSRS.

This is a very powerful feature. Basically any valid VB function can be used in a report expression.

Tip 6: Adding a Line Break in SSRS Expressions

Developers use multiple text boxes to show multiline data otherwise a single TextBox can be used with just a line break. The VbCRLf (Visual Basic Carriage Return Line Feed) value can be used to add a line break in expressions. To show the VbCRLf function, I have added one text box to the report to provide the following expression in the TextBox.

="This is my Test " & VbCrLf & "Execution Time: " & Globals!ExecutionTime

Output: On Preview the report "This is my test" and "Report Execution Time" are on two different lines as shown below.

Expressions are a very useful feature of SSRS and they can be used to address many complex problems and reporting requirements.

1 Comment

Join the conversation! Your thoughts help the community grow.

James Babcock3y ago
Can you do multi step commands in expressions? Like: =Fields!VNDITNUM.Value = lTrim(rTrim(Fiellds!VNDITNUM.Value)) ; iif (Len(Fields!VNDITNUM.Value) < 30, Fields!VNDITNUM.Value, left(Fields!VNDITNUM.Value, 27) + ",,." with out the assignment, the trims have to be repeated 3x becoming a readability/maint issue Thanks! Jimbus