March 2011

When thinking about Reporting Services in combination with the word cascading, the first that jumps to mind is cascading parameters.  We all know that one parameter can have its list of values filtered by what’s selected in another parameter.

But what about calculated fields?  Are those cascading as well?  Can we refer to a calculated field in the definition of another calculated field?  Let’s find out!

For the example I’ll be using the ContosoDW sample data warehouse running on SQL Server 2008 R2, more precisely:

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

The result can be downloaded from my Skydrive through this link.

The Example

Scenario

We’ve been asked to build a report that produces a product catalogue.  The report only has one requirement: as our company is well-known for its branding, each product category has got its own color and this color should be used as background color in the report.

Report

Let’s first get some data.  Here’s a fairly simple query that retrieves all products with their related category and subcategory from the ContosoDW database:

select DPC.ProductCategoryName, DPS.ProductSubcategoryName, DP.ProductName
from dbo.DimProduct DP
inner join dbo.DimProductSubcategory DPS
    on DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey
inner join dbo.DimProductCategory DPC
    on DPC.ProductCategoryKey = DPS.ProductCategoryKey;

After building a dataset in a new report I end up with this:

Calculated Field Number One

Because this post is about calculated fields, we’re now going to apply a little dirty trick of hard-coding the category names and their corresponding color into a calculated field.  I do not recommend this for professional reports where the colors should be coming from the database so that your reports are not impacted when extra categories are added or when the marketing department decides to change their vision.

But for this example it’s perfect so let’s create a calculated field in the dataset.  That can be done by right-clicking the dataset and then selecting Add Calculated Field…

Give the field a clear name, such as ProductCategoryColor, and click the fx button to enter the following expression:

=Switch(
    Fields!ProductCategoryName.Value = "Audio", "#FFD800",
    Fields!ProductCategoryName.Value = "Cameras and camcorders ", "#FF0000",
    Fields!ProductCategoryName.Value = "Cell phones", "#00FF00",
    Fields!ProductCategoryName.Value = "Computers", "#0000FF",
    Fields!ProductCategoryName.Value = "Games and Toys", "#FF00FF",
    Fields!ProductCategoryName.Value = "Home Appliances", "#FFFF00",
    Fields!ProductCategoryName.Value = "Music, Movies and Audio Books", "#00FFFF",
    Fields!ProductCategoryName.Value = "TV and Video", "#ABCD12"
)

Funny side note: do you notice that trailing space in the “Cameras and Camcorders” category?  It’s intentional!  Apparently that record has got a trailing space stored in the ProductCategoryName field in DimProductCategory.

With the first calculated field created, add a table to your report to display the products.  Set the BackgroundColor property of the whole Details row to the newly-created calculated field.

So far so good, here’s what the rendered report currently looks like:

Calculated Field Number Two

According to the business requirements we’re done creating the report.  However, it’s Friday early afternoon and we feel like having some fun.  And this post is about “cascading” calculated fields, so we need at least two of them.  Let’s create an Easter egg!

The “fun” requirement is the following: if the product’s name starts with an A then the text color for that record should be the same as the background color, but with the Blue component set to FF.  For example, if the background color is #00FF00 (green) then the text color should become #00FFFF (cyan).

Let’s create another calculated field in our dataset, called EasterEgg (don’t make it too hard for your colleagues to fix the weirdly behaving report).  Give it the following expression:

=IIF(Left(Fields!ProductName.Value, 1) = "A",
    Left(Fields!ProductCategoryColor.Value, 5) + "FF",
    "Black")

As you can see, we’re referring to the ProductCategoryColor field, the calculated field created earlier.

Now set the Color property of the Details row to this new calculated field and Preview the report.

Guess what?

It works!

Conclusion

If you’re in a situation where you’d like to add calculated fields to an existing dataset and one of those fields should use the value of another calculated field, you can do it!  Cascading calculated fields are working fine in Reporting Services.

Have fun!

Valentino.