Create a report using Expressions in SSRS

5
1773

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.

Report Preview

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

First ScenarioColor 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.

Expression to change Color

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

Color change output

Second ScenarioPattern 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”)

Expression for pattern matching

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

Pattern matching output

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”)

India's No.1 Cashback & Coupon site

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”)

Expression to set alternate row color

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

alternate row color output

Fourth ScenarioSetting 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.

Report header property settings

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

Report header property settings output

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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.9/10 (33 votes cast)
VN:F [1.9.22_1171]
Rating: +30 (from 32 votes)
Create a report using Expressions in SSRS, 9.9 out of 10 based on 33 ratings

5 COMMENTS

    • Thanks a lot Avinash. We can create any type of report in SSRS with the use of Expressions. I have shown few examples above and will update this article with Expressions cheat sheet in which i will try to cover more. Stay tune !!! 😉

Comments are closed.