Cascading Calculated Fields (SSRS)
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.
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.
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.
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.
- Formatting Numbers [SSRS]
- Community Day 2013: Data Visualization Tips & Tricks
- T-SQL Tuesday 42: Life, Change, Don’t Panic!
- Local Install of Books Online 2012
- Hidden Collections in SSRS
- How To Tweet About SQL Server Blog Posts
- Filtering Data Without Changing Dataset [SSRS]
- SSRS Deployment: Generate The Batch Script Through SQL!
- Connecting Shapes In Word
- Automating SSRS Deployment: Download
- June 2013 (1)
- May 2013 (3)
- March 2013 (3)
- February 2013 (2)
- January 2013 (2)
- December 2012 (2)
- November 2012 (3)
- October 2012 (2)
- August 2012 (2)
- July 2012 (2)
- June 2012 (2)
- May 2012 (2)
- April 2012 (3)
- March 2012 (4)
- February 2012 (4)
- January 2012 (2)
- December 2011 (2)
- November 2011 (2)
- October 2011 (1)
- September 2011 (3)
- August 2011 (2)
- June 2011 (2)
- May 2011 (3)
- April 2011 (3)
- March 2011 (3)
- February 2011 (2)
- January 2011 (5)
- December 2010 (1)
- November 2010 (3)
- October 2010 (3)
- September 2010 (2)
- August 2010 (4)
- July 2010 (2)
- June 2010 (4)
- May 2010 (6)
- April 2010 (3)
- March 2010 (3)
- February 2010 (11)
- January 2010 (9)
- December 2009 (2)
- November 2009 (3)
- October 2009 (3)
- September 2009 (4)
- August 2009 (6)
- July 2009 (2)
- June 2009 (3)
- May 2009 (7)
- April 2009 (3)
- March 2009 (3)
- February 2009 (5)
- January 2009 (4)
- December 2008 (2)
- November 2008 (3)
- October 2008 (1)
- September 2008 (1)
- August 2008 (4)
- July 2008 (3)