This little one isn't very obvious and can cause a lot of hair pulling, so I decided to write it down here.
Basically in reporting services you can make use of reporting parameters using a combination of dataset query parameters and Member(<<string>>) functions. Hence you can define a parameter that returns a string ie: '[Organisation].[Hierarchy].&[1232]'
Sometimes, like today, I need to send down just the Ids themselves, as these get plucked directly out of a querystring, so I might have say: 1232,144,664 as my key values. I wrote a quick CLR function that I plugged into analysis services which will convert n values into a string of:
{[Organisation].[Hierarchy].&[1232],[Organisation].[Hierarchy].&[144],[Organisation].[Hierarchy].&[664]}, which then gets returned as a set and life goes on.
However, when I was defining the query parameters, @DepartmentID (and all the other parameters) needed a default value. This was annoying as this value was bound to the [Organisation] dimension, and hence it would insist on binding to the values therein.
Little did I know that you're allowed to define a query parameter that's NOT bound, although it still needs a default parameter. Unbinding from the dimension will save a headache, and let you pass parameters in as mentioned.
posted @ Wednesday, January 30, 2008 8:18 PM