I’ve recently found something new to use in SSRS. Yes, 7 years of usage and I found something that was completely new to me. And then I discovered it existed since SSRS 2008 and I just hadn’t used it (oops!). I was a bit hesitant of posting about it, until I also heard a lecture about it in the last BI User Group meeting (and I recommend you go to meetings taking place around your neck of the woods). So now I feel very comfortable in telling you about referencing calculations in different parts of your report through the Report Items Collection.
Basically each and every part of your report can be referenced by another part. Each cell you have in your report has a name. The names are usually automatically generated and are on the line of “Textbox 6” (or any other number). You can also give the cell a specific name of your liking in its properties pane under its Name attribute:
After having an item with a name, you can reference its value for its own styling. For instance you can define the color of the text in the textbox will be red if the value for the calculation is under zero or black if its zero or above:
=IIF(Me.Value > 0,”Black”,”Red”)
The same can be done for the background color of the textbox etc..
You can also reference the value for that textbox in the calculation of another textbox. For instance, I used the total of the talk time and number of calls for a certain day, to calculate the average talk time for that same day:
You can use the values in your report header and footer, and not just in other cells of that same tablix.
So, just in case it’s new to you as well, I’m suggesting that you look up the ReportItems Collection and see how else you can reference it in different parts of your report. It’s always important to use the work you did in the past to work less in the future 😉