Expressions in SQL Server 2005 reporting services

The intent of this article is to demonstrate the possible applications of expressions in SQL Server 2005 Reporting Services (SSRS) reports. You should already have a basic working knowledge of SSRS before continuing.

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: 

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.