Now that I'm using MDX and hitting cubes, things have changed and I've encountered a problem with cascading parameters not updating automatically. Sure, they populate perfectly when you first make a selection. But if you make a change to the parent after the fact, the child just doesn't update.
It turns out this is by design (thanks, Microsoft). SSRS thinks it's being helpful by not refreshing if it doesn't have to. It also doesn't want you to have to re-select a parameter if you've already selected it.
This is a pain in the butt, since MDX parameters aren't pretty. Who wants to scroll through a dropdown of thousands of dates to find the right one? To allow human users to interact with the report, you need to create a date parameter as a parent parameter, then use that parent parameter to populate a (hidden) child parameter with the correct dimensional hierarchy (I'll talk about that in another post). But, if you change the parent date, the child hierarchy doesn't update, and you're screwed.
I've seen some pretty involved ways to get around this, but I've found it doesn't need to be that complex. To fix this, you just need to "invalidate" the child parameter's available values.
If we take the date example from above, follow these steps:
1. Create a new parameter for the date/time portion. We'll call that one "StartDate".
2. Go into your cube (I use the wizard), find the dimension and hierarchy you need, and drag it to the Filters pane. We'll say we're using the createdon date for this.
3. You'll see that on the far left is a column that says "Parameters". Click that check box. SSRS will automatically create a new parameter for you based on this selection when you click ok.
4. Find the new parameter. It's usually named [Dimension][Hierarchy] and removes any punctuation or spaces you may have in your naming. If the parameter is part of a date range, it may also prefix the parameter with "To" or "From". Open this bad boy up.
5. Leave the datatype as text. If you want to change the "Allow multiple values", go ahead. Leave the "Allow blank value" and "Allow null value" unchecked. MDX has a different way of dealing with these.
="[I - Web Access - Createdon].[Hierarchy].&[" + CDate(Parameters!StartDate.Value).ToString("s") + "]"
The format here is the =[Dimension].[Hierarchy].&[Value]. You may need to use =[Dimension].[Hierarchy].&[{Value}] if you get a CONSTRAINED error (for guids and the like). However, a CONSTRAINED error may also reflect a typo in the hierarchy or a bad/nonexistent value.
*A nice shortcut here is if you go into the query designer, navigate to the hierarchy you want, right click it and select copy, it gives you this address.
7. Go ahead and copy what you have here. Hit ok, then click the function button next to Label and copy this there as well. That way you can verify the value actually changes.
8. On the Default Values menu, change the radio button to "Specify values". Click Add, then the function button, and paste the same thing from the available values here. Hit ok.
9. Preview the report and verify that when you change the parent parameter, the child parameter changes.
10. Open up the automatically created parameter again and set it to "Hidden". Now the user only sees the "easy" parameter.
So why does this work? Well, aside from needing to convert a date format into a hierarchy address, the key is the available values. When you change the parent date, it invalidates the child's available values and forces the report to create a new set.
This is great. But need to select the values from a query? Do the same thing. Ensure that the selected values from the parent are the only available values for the child. If the parent changes, the child's available values are no longer valid and will be refreshed. I'll post a detailed example of this later.
This post was so helpful for me, I think I'm gonna burst into tears!! :') THANK YOU SOOOO MUCH!!!
ReplyDeleteGlad it helped!
Delete