Create a report using Expressions in SSRS

Expressions in SSRS i.e. MS SQL Server Reporting services are used frequently in creating reports which helps in controlling content and report appearance. Expressions can use built-in functions, custom code, report and group variables, and user-defined variables. In this article, we will create a report and then implement some expressions to make the appearance of our report eye catchy. Expressions in SSRS is expalined below:-

1. Helps you customize the content, eye catchy appearance, and interactivity of your report.
2. Widely used throughout a report to retrieve, calculate, display, group, sort, filter, parameterize, and format data.
3. They are written in Microsoft Visual Basic, and saved in the report definition.
4. They operates on a cell by cell basis.
5. To see the actual data from the evaluated expressions, you must preview the report.
6. An expression is represented as [fx] button and begins with an equal sign (=) followed by a combination of field identifiers, constants, functions, and operators.Example: =Sum(Fields!Sales.Value)

Let’s create a report and perform some of the simple expressions to understand the concept and functionality of Expressions in SSRS.

STEPS TO FOLLOW –

Step 1. Create a report by any of the below preferable method implemented previously in my SSRS tutorials section i.e.

 Report generation Using Report wizard.

 Report generation without using Report wizard.

 NOTE: – I am creating a report without using Report wizard. You can choose any method.

Step 2.   Now, let’s play with expressions and manipulate our created report. My report output looks like a black and white television screen. Let’s make it lively and eye catchy with the use of expressions.

 Step 3. We will take some scenarios to understand the working of expressions in a better way and implement them in our report.

First Scenario – Color Change Expression.

Problem – Under Salary column, change the color to Green if salary is greater than 20,000 else change the color to Red.

Solution– Go to the design pane and right click on the salary field. Choose Text box properties and go to the Font part. Click on [fx] button of the color section to write our color change expression. Write the expression as: –     =IIF(Fields!Salary.Value>20000,”Green”,”Red”) and click OK button.

 Preview the report to see the implementation of color change expression.

Second Scenario – Pattern Matching (LIKE statement) Expression.

Problem – Under Location column, change the color of the fields to Blue which contains “ca” else remains the color to default.

Solution – In the design pane, right click on the Location field and choose Text box properties. Go to the Font part and click on [fx] button of the color section. This will pop up Expression window where we will write our expression for pattern matching. The expression will be like this: –

=IIf(Fields!Location.Value.ToLowerInvariant().Contains(“ca”),”Blue”,”Transparent”)

Go to the preview pane and see the output of pattern matching expression.

Note: – If we will remove ToLowerInvariant() then, “ca” will not be equal to “Ca” or “cA”. To make these entire equal, we added this to the expression else we could simply use =IIf(Fields!Location.Value. Contains(“ca”),”Blue”,”Transparent”)

Third Scenario – Set Alternate Row Color Expression.

Problem – Make the even rows color to LightGrey and odd rows color to White.

Solution – In the design pane, choose the entire row from the Tablix. Go to the Properties window and click on the drop down button under the Background color option. Select Expressions and write the following in the expression window.

=IIF(RowNumber(Nothing) MOD 2,”LightGrey”,”White”)

In the preview pane, you can see the output for the alternate row color expression.

Fourth Scenario – Setting Report Header Properties.

Problem – Make the Report header professional.

Solution – It’s very easy and depends upon your creativity and innovation how you design the report. Select the header part of the report from the Tablix and go to the properties window. Now you can select and play with all the given options to make your report professional.

Output will be like this – I have made changes to the Text alignment, Vertical alignment, Background color, Font and Border style.

These scenarios are very simple to implement and helpful to strengthen yours writing expression skills. Below is the list of expressions you can use depending upon various scenarios.