The intent of this article is to demonstrate the possible applications of expressions in SQL Server Reporting Services (SSRS) reports. You should already have a basic working knowledge of SSRS before continuing.
Expressions in SSRS
Expressions are basically little snippets of VB.NET code that return a value. The snippet can calculate a return value based on other values stored in parameters, datasets, or combinations thereof. You can also make use of several built-in values and functions to perform more complex calculations.
For the purposes of this article, we will use a default installation of SQL Server 2005 and Reporting Services. We will start with a simple report that retrieves data from the AdventureWorks sample database and then make various modifications to it in order to demonstrate the various possibilities.
Here is the query that the report is based on:
- SELECT
- Person.StateProvince.Name,
- COUNT(Person.StateProvince.StateProvinceCode) AS StateCount
- FROM
- Sales.Customer
- INNER JOIN Sales.CustomerAddress ON Sales.Customer.CustomerID = Sales.CustomerAddress.CustomerID
- INNER JOIN Person.Address ON Sales.CustomerAddress.AddressID = Person.Address.AddressID
- INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID
- AND Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID
- GROUP BY
- Person.StateProvince.Name
- ORDER BY
- Person.StateProvince.Name,
- StateCount
Here is the report layout
And here's a portion of what it looks like in Report Manager
For the first example of an expression, we'll change the colors of those count values that are below a certain threshold. In this case, we want the color of the text to red for those rows with a count less than 25. To do this, we need to navigate the report's layout in Visual Studio, click on the table cell that renders the count field, and select Expression from the Color property dropdown menu:
The "Edit Expression" dialog appears:
The expression is currently set to the default color value "Black". We need to change this to a VB.NET snippet that returns one of two strings -- "Red" for values less than 25, or "Black" for other values. Here is the expression that will do that:
=IIf(Fields!StateCount.Value < 25, "Red", "Black")
It uses the IIf() function to determine the name of the color to be used, based on the value of the StateCount field in the dataset. After redeploying the report, it now looks like this:
But what if you want to have more than two colors? For example, you may also want to show all values over 1000 with a color other than red or black (in this case, we'll use blue). You have some options here. One would be to use nested IIf() calls, as in: =IIf(Fields!StateCount.Value < 25, "Red", IIf(Fields!StateCount.Value > 1000, "Blue", "Black")) The function is evaluated as follows: if the count is < 25, then "Red" is returned, otherwise the second IIf() is evaluated and it checks if the count is > 1000. If so, it returns "Blue", otherwise it returns "Black", and your report looks like this:
But what if you want even more colors? Well, you can continue to nest IIf() statements but your expression code will quickly become more difficult to read and maintain. An alternative (assuming you can modify the report query) would be to add another column to your select query that returns the desired color strings as in:
- SELECT
- Person.StateProvince.Name, COUNT(Person.StateProvince.StateProvinceCode) AS StateCount,
- CountColor = CASE
- WHEN COUNT(Person.StateProvince.StateProvinceCode) < 25
- THEN 'Red'
- WHEN COUNT(Person.StateProvince.StateProvinceCode) < 100
- THEN 'OliveDrab'
- WHEN COUNT(Person.StateProvince.StateProvinceCode) < 500
- THEN 'Teal'
- WHEN COUNT(Person.StateProvince.StateProvinceCode) < 1000
- THEN 'Blue'
- ELSE 'Black'
- END
You can then remove the IIf() statements altogether and change the Color property expression to:
=Fields!CountColor.Value
And the report now looks like this:
These examples only scratch the surface of the potential that expressions offer in generating dynamic report content. Take a moment to browse through the Properties list for a table cell (or any other object in the Toolbox). Most properties give you the option of entering an expression in addition to any predefined values. This means that you can manipulate any of them programmatically, just as easily as in the color example shown here. Here are some additional examples of what expressions can do for you:
- Hide columns in tables, or hide groups in tables and matrices by using an expression to set the Hidden property of the column or group.
- Create entirely new columns by using an expression to return a value based on existing values in the recordset. For example, lets say you have inherited a report that shows 3 columns labelled A, B and C, using a stored procedure to generate the data. The end user wants a fourth column to be added that shows the average value from A, B and C. You could add that new column to the select statement in the stored procedure, but what if you aren't allowed to modify the procedure? Instead, you can add that 4th column to the report, and set its value to an expression that returns (A+B+C)/3. You've added the desired column and you've done it without modifying the stored procedure.
- Make use of built-in functions, such as Globals.ExecutionTime (to return the time & date that the report was executed) and Globals.PageNumber (to determine the current page number) to add meaningful tracking information to your report's headers & footers.
- Perform your own aggregation and grouping using the built-in functions such as Sum(), Avg(), etc. These functions allow you to add aggregation to established reports where (as in #2 above) you may not be able to modify the queries that generate the reports' data.
- Manipulate the data values using VB.NET string functions such as InStr, Replace, FormatNumber, etc. to get the data to appear exactly how you want it to.
Try experimenting with the various functions that appear in the Edit Expression dialog. Remember that by changing the output of your report, you're not changing the underlying data in your database, you're only changing how it looks when rendered in the report.