Today’s title comes from Harry Nilsson, who once, quite sensibly, wrote
A point in every direction is the same as no point at all.
That’s sometimes how I feel about various SSRS features. They get introduced, they look groovy… hey! You could do anything with this!
Then after a closer look you find there isn’t anything you really want to do with them.
Variable cloudiness
A couple of months ago, I wrote a Just-So Formatting story about why group variables just didn’t cut it for Ramdas’ requirement.
I said I couldn’t find a real reason to use group variables, so far. I also said that I wasn’t sure of the solution I provided in other versions of SSRS than I was testing.
In both statements, regarding group variables as well as my solution-not-using-group-variables, my critical area of concern is the sequence of events. This sequence includes evaluation of variables and other types of actions such as triggering expressions and custom code, during the run of a report. Figuring out the order in which everything happens is critical to, well, getting it to happen.
Anyway… last week I came upon a just-so formatting requirement of my own, that could not have been resolved without group variables! Wow!
In the interests of equal time, and since this was completely new to me and might be to you, I thought I’d tell you about it.
There may be a way to delay or change evaluation of variables in SSRS with their scope, as there is in SSIS — but this just gives you more permutations to test. It doesn’t resolve anything.
FWIW the “DeferVariableEvaluation” option that is new in 2008 R2 appears to variables on the Report scope, and doesn’t really do much for the problem at hand.
Scoped variables, such as the group-level variable I’m going to use here, are evaluated once per group. The significance question is: when within the sequence of the other things the group evaluates and processes?
Even if the documentation was clearer about this — and it’s unusually spartan — you would still have to test, test, test, using the context of your own code to find out the truth.
Our “simple” requirement
A report has a list control that groups on students. Within each student, we wish to display a head shot identifying the student or, if none is available, a stock image.
The image’s value expression calls a function. While that fact is not strictly relevant, the expression looks something like this:
=Code.GetStudentPicture(
Parameters!ImageFileSkeleton.Value, Fields!StudentID.Value)
… and the function in question looks something like this:
Function GetStudentPicture(tFileSkel As String, tStudentID As String ) As String
Dim testFile As String = String.Format(tFileSkel, tStudentID.Trim())
If System.IO.File.Exists(testFile) Then
Return “file://” & testFile
Else
Return“file://” & String.Format(tFileSkel,“DefaultStudent”)
End If
End Function
How much more generic could the problem be? What could possibly go wrong here ?
Oh, wait. That’s right.
The images are stored in the file system; never mind where. Once I remembered to set up an Execution Account in SSRS, with appropriate rights to this folder (ugh), I figured my job here was done.
Well.. I’m simplifying a bit. Because of the File.Exists test, an Execution Account with appropriate rights isn’t really enough. You have to set code in RDLs to be trusted. However, that part of the requirement isn’t generic, and you probably wouldn’t even put your images in a shared folder. You’d put them in a database or access them through a URL. Right?
Next, I added a subreport into the list control, to handle most of the information for the current student, and everything was still fine. The image and header information “sat” above this subreport, like a frame.
Then I added another subreport, this time adjacent to the image. Uh-oh.
Our pesky little friend Sequence O. Execution didn’t like that change. Suddenly my function wasn’t receiving any value at all for the Fields!StudentID.Value, which, as far as I knew, was perfectly in scope at the time.
Please don’t ask me to explain this. (It took me tons of time even to understand why it was broken. I did check for any overlap, but there didn’t seem to be anything like that going on.)
Moving the relative positions of the image and the subreport was not an option.
Group variable to the rescue!
Creating a group variable to store the value of Fields!StudentID.Value ensured that the string was in scope when I called my function. All I had to do was change the expression for the image to :
=Code.GetStudentPicture(
Parameters!ImageFileSkeleton.Value, Variables!ThisStudent.Value)
… and life was good.
This annoyance wasn’t specific to the image control; it turned out that every dataset field I was evaluating within that specific block in the layout had the same problem. I just stored them all to variables on the “way in”, and substituted the variables for direct evaluation of the fields, in each case.
And that’s how LSN found out the point of group variables.
I’m definitely filing this solution away for future reference, because it’s not the first time that subreports have snuck up and bit me like this.
Miracles don’t cease there. This week, I found out that even the completely irritating Word renderer in SSRS 2008 can sometimes have a point. In Part Deux of this little mini-series, I’ll tell you about that.
Cloudy with more than a chance of meatheads
I have a small announcement to make regarding the status of this blog:
Comments are now totally closed.
The noise-to-signal ratio of the comments, and the ton of work it took to clean them up, finally got the better of me. Not to mention the unpleasant sticker shock when the spam traffic ate Spacefold.com out of house and bandwidth this month.
Please email me if you have questions or need help, I’m always happy to hear from you.