Friday, November 18, 2011

Why, SSRS, Why?

Once again, I've been digging into SSRS reporting.  Today, I thought to myself, I should had a multi-valued parameter to this report for filtering my tablix aggregations.  So, I created a data-set that was a self-join for gathering a list of Managers.  Easy enough, that'll work great for a drop-down selection.  But let's say a manager wants to only look at certain employees that they manage.  A multi-valued parameter would be great for this!  

I'd like a side of extra values in an array, please.
Add one data-set for that scenario, and re-order the parameters for proper cascading.

Then it's just a matter of adding my managed employees MVP to the tablix filter, right?  Okay, here it is, I'll just double click it and get the right syntax.

What's wrong with this picture?

So, why am I only getting one member's aggregates in my tablix?  This is some serious fail!

Oh, of course!
A few searches through MSDN for syntax and we discover that for multi-valued parameters, appending "(0)" in your expression causes SSRS to query only the first value in your array.  I'm glad that got added by default when the parameter was an explicitly typed array.  Not very conducive for learning syntax on the fly.  But now we've got one (significantly more readable) report.

No comments:

Post a Comment