One of the biggest problems I've been having with upgrading my mad reporting skills from RS 2000 to RS 2005 has been with cascading parameters in OLAP reports. WIth the new and "user friendly" connection method to Analysis Services, I couldn't use my old tricks with dynamic queries for parameters. And I never found a usefull description on how to actually do it in RS 2005. A few weeks ago, I was forced to figure it out. And in an enlightened moment, it dawned on me what to try, and I finally figured it out. Yey. So now I'm posting this for ... well... maybe someone needs it, or at least I know where to find it if I forget.
I created a stored procedure that returns three columns, and used it in a data set called MarketBrandsFromDB:
MarketBrandUniqueName
MarketBrandName
MarketBrandID
The MarketBrandUniqueName was formated to look like a cube member: [Market Brand].[Market Brand Name].&[Brand 1].
My first query created a result with all the members in one line. This stored procedure just returns a list of members, like a regular dataset.
Then I added the Market Brand dimension and Market Brand Hierarchy as a filter for the OLAP query, and marked it as a parameter. Then I went to the layout tab to make the Report Designer do its' magic and create both a parameter and a dataset, both called MarketBrandMarketBrandName. (Quite a mouthfull, one of the other guys wondered if I'd done a typo. Guess that's what user friendlyness does...)
So now I had a report parameter that refers to this data set that is just pulled directly from the cube, which isn't what I want. I wanted it to correspond to what's in the database. It turns out that the trick was to map the parameter called MarketBrandMarketBrandName to the dataset called MarketBrandsFromDB, and not the data set called MarketBrandMarketBrandName. I mapped the value field to MarketBrandUniqueName and the Label field to MarketBrandName. I used the same fields for the default values.
And there you have it. The report picks up my username, queries the sql database for which market brands I have access to, and gives me a choice of only those market brands, just as I wanted. It's all done using the GUI, so I don't have to make the other guys mess around in either mdx or the rdl file directly, which is also a good thing.
I think I couldn't find any documentation on how to do it because it's actually quite simple when you know how, so people don't bother documenting it. Or maybe it's in the documentation, I just didn't know what to look for.
My only question now is if it's safe to delete the dataset that was created for the MarketBrandMarketBrandName parameter?