SSRS Tip: Referencing Values from Calculated Fields on a Report

Every so often we need to build a report with a number of complicated calculations that we need to perform at the detail, group or summary level in the report. Maybe we need to calculate some aggregates, then do calculations on the aggregates. Like creating percentage markers for sums of numbers, from multiple groups.

This can result in complex expressions being displayed on the report surface. And then when you need to change some of the calculations, but not all of them, then maintenance gets messy.

We can simplify this setup – and also make our report debugging and maintenance easier all around – by doing two things:

First, we give every calculated field a Name. To do this you’ve got two options:

1. Right-click the field on the report surface, choose Text Box Properties, and then set the Name field to something useful like “TotalCalculatedMinutes,” or whatever fits.
2. Left-click the field on the report surface, then navigate in the Properties pane on the right side of the designer (don’t see it? Choose View > Properties from the menu, or hit F4 to open it up) and find the General > Name field. You can set it there as well.

By doing this, we can give calculated aggregate fields (of almost any complexity) a single name. Perhaps we calculate total therapy minutes in a group footer: 

And then we calculate the sub-divisions of those therapy minutes with several other aggregate fields:

Each of those text boxes gets a unique name, like: 

And so on…

Now we can reference the calculated values of fields in other expressions, by using the following style of reference: 

When we go to calculate the percentages of the total, for each sub-division of the therapy minutes, we can use an expression like:

Well now hold on, you say! What’s this business with the SWITCH() function? Where did that come from? As it happens, this is a VB for Applications function which is available in SSRS.

SWITCH takes each pair of parameters, evaluates the first expression in the pair, and if TRUE, returns the second part of the pair. You can produce the same effect with nested IIF() statements, but SWITCH is easier to read once you get complex.

It is not documented, but I suspect that SWITCH takes up to 255 pairs of parameters, and will throw a runtime error if to try and feed it 256 pairs…

The problem with calculating a percentage, is that if either the numerator or the divisor is zero, we may be unexpected results on the report surface. Error messages may display, or “NaN” tags. We want to trap those and return a Nothing (NULL) result instead.

The SWITCH lets us check the numerator (TotalConcurrentMinutes in this example), and return nothing if it is zero. Same for the denominator (TotalCalculatedMinutes). This lets us trap the zero-related errors and step around them.

Then we run into an issue with SWITCH — it does not, by default, have an ELSE option. But SWITCH processes in the order of the parameter pairs, so our last parameter pair can act as an ELSE, if we have the expression always return TRUE. The 1=1 accomplishes that … 

Extra Credit

When you display your percentages, construct a custom number format string to display a “-“ (dash) when the results of the calculation are Nothing.

And…

As ever, if you need help with Meditech DR index creation, reporting, extract or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!