Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Building a Report from the Ground Up - Part 7: Using Expressions in your Report

  • 1 December 2009
  • Author: DustinRyan
  • Number of views: 5928
  • 0 Comments

Part 7: Using Expressions in your Report

Now that you know the basics of creating a report to display some data within a table or matrix data region, it’s time to apply some expressions to your report and increase your report’s readability for your end users. An expression can be added to just about any aspect of your report, so you can get pretty creative with your expressions and the way you use them. I’ll just take you through a few examples of the way expressions can be used so that you can understand the basic idea of using an expression. For these examples, we’ll be using the report we created in Part 6, which also utilizes the AdventureWorks database.

The first expression we’ll look at is an IIF function applied to the font color of a field. In IIF function simply returns one of two values depending on whether a condition is met. In our Regional Sales report, right-click the SaleYTD field and select Text Box properties.

 Part7_1

Then click Font, and click the Expression button next to color.

 Part7_2

Let’s add an expression that says if the Sales Person’s Year to Date Sales are less that the SalesLastYear field, then change the font color to red. If the Sales Person’s Year to Date Sales are more than SalesLastYear, then change the font color to red. To do this, use an IF statement, as seen here.

=iif(Fields!SalesYTD.Value"Red","Green")

Also, add a similar expression to conditionally Bold the font if SalesYTD is less than SalesLastYear. Click the expression button next to Bold in the Style Options.

=iif(Fields!SalesYTD.Value"Bold","Normal")

Another very common expression used to conditionally format a report is one used to alternate row colors. To do this, click the details row to select it.

 Part7_3

 In the Properties pane on the right, find the Background Color property, click the drop down arrow, and click Expressions. Use the following expression to alternate row colors and make your data easier to read:

=Iif(RowNumber(Nothing) Mod 2, "LightGrey", "White")

Now your rows will alternate between light grey and white.

 Part7_4

We can also use expressions to change the actual data itself. I’m going to use multiple IIF functions to change the values. Instead of displaying “Northwest” as a territory, I only want to display “NW.” Right-click the Territory field and click Expression.  You can see the multiple IIF functions to account for the different values:

=iif(Fields!Name.Value="Northwest","NW",

iif(Fields!Name.Value="Northeast","NE",

iif(Fields!Name.Value="Southwest","SW",

iif(Fields!Name.Value="Central","CEN",

iif(Fields!Name.Value="Canada","CAN",

iif(Fields!Name.Value="Southeast","SE",

iif(Fields!Name.Value="UnitedKingdom","UK",

iif(Fields!Name.Value="France","FRA",

iif(Fields!Name.Value="Germany","GER","AUS")))))))))

And now only “NW” is displayed with the Territory is “Northwest.”

 Part7_5

You could also perform the same function of the previous IIF function by using a Switch function. A Switch function must include an even number of arguments.

=switch(Fields!Name.Value="Germany", "GER",

Fields!Name.Value="France","FRA",

Fields!Name.Value="Northwest","NW",

Fields!Name.Value="Northeast","NE",

Fields!Name.Value="Southwest","SW",

Fields!Name.Value="Southeast","SE",

Fields!Name.Value="Australia","AUS",

Fields!Name.Value="UnitedKingdom","UK",

Fields!Name.Value="Canada","CAN",

Fields!Name.Value="Central","CEN")

Let me add something. It’s usually best to prepare the data as best as possible on the back side in the SQL, rather than on the front end of report processing. If you use tons of expressions to change the way data is displayed in your report, it could potentially bog down your report at run time, so beware when using the 500 line expression you spent all day working on.

You can even use expressions with images. I’ve added images of flags for each Territory in my report. After adding the images to your report as I showed you in Part 4, simply drag it into your design area where you would like it to appear. Select the image in your design area, find the Value property in the Property pane on the right and add an expression to it. My expression looks like this:

=switch(Parameters!Terr.Value="Germany","germanyflag",

Parameters!Terr.Value="UnitedKingdom","UKflag",

Parameters!Terr.Value="France","franceflag"………… etc.

So now when someone select Germany in the parameter drop down box, they’ll get to see a tiny German flag:

 

Part7_6

These are just a few of the ways you can use expressions within your report, but I hope I’ve given you a good starting point.

 

Print
Categories: Reporting Services
Tags:
Rate this article:
No rating
DustinRyan

DustinRyanDustinRyan

Other posts by DustinRyan

Please login or register to post comments.