Cascading Parameters based off Analysis Services

Hello,

I was trying to do cascading parameters based off my cube and I wasn't able to do this. Is it possible?

For example, I have a dimension that has Products so I first select the parameter for product type (Dairy, Frozen, Candy) and then I have another dropdown listbox that has the name of each product (Milk, Ice Cream, Lemon Drops). The second dropdown listbox should only contain the products that match what parameter was selected in the first dropdown.

When I couldn't get that to work, I went to the source system containing the Dimension tables and just did nice and easy SQL statements from there. It worked but I, for some reason that I can't explain, think this is not the proper way to do it.

Also, is there a way to have a default on the second parameter based on the first parameter selected? I would assume that default would be [All].

Thank you.

-Gumbatman

Yes it is possible. When you use the query designer, by default each parameter depends on the one before it having a value, even if the parameters are not really related. For this, you want to maintain that link. Then, in your MDX that creates the dataset for parameter #2 you can refer to parameter #1 (it will be called something like @.MyParameterName), i.e. you can use it as part of a STRTOSET or STRTOMEMBER function.|||

Sluggy,

Thank you so much for this answer. I was banging my head against the wall trying to figure out how to do this.

-Gumbatman

|||

Could you please post an example of this? My head also aches a lot

I just need the MDX syntax of the second dataset, the one of the parameter that depends on the first one selected.

Is there any way, from within SSRS to "filter" based on the previous parameter inside the MDX sentence?

Using SQL tables datasources and cascading parameters is quite straightforward, not the same with Olap cubes as datasources.

Thank you

Mike

Cascading Filters

Hi,

is there a way to define cascading filters?

Let me provide you an example:

Let's say, we have a dimension "client", a dimension "cost unit" and a dimension "product category". Each client has it's own cost units and product categories and we want to use this 3 dimensions as filters: When a client is selected in the client filter, there should only this client's cost units and product categories be available in the other two filters.

Is there a way to archive that?

Whishes,

Manfred

Hi Manfred,

Are the "cost unit" and "product category" intrinsically associated with a "client", in which case they could be modelled as attributes of "client", or only dynamically associated via fact data? In either case, cascading filtering is typically a functionality supported in fornt-end tools - but it certainly can be used with OLAP data sources. For example, Reporting Services has such support - the exact MDX expressions will depend on how the association between the parameters is modelled in the cube:

http://msdn2.microsoft.com/en-us/library/aa337169.aspx

>>

SQL Server 2005 Books Online

How to: Add Cascading Parameters to a Report

New: 17 July 2006

Cascading parameters provide a way of managing large amounts of report data. You can define a set of parameters where the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter could present a list of product categories. When the user selects a category, the second parameter is updated with a list of subcategories within the category. A third parameter could then display a list of products within the selected subcategory. The value for the product parameter could then be used to filter the report to a particular product. This process of filtering a list of parameter values based on a value from another parameter is known variously as cascading, dependent, or hierarchical parameters.

You create a separate dataset that supplies available values for each cascading parameter. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list. The order of the parameters determines the order in which the parameter queries are run. When you open the Report Properties dialog box, the parameters are listed in order. You can change the order by using the up and down arrow buttons.

...

>>

|||

Hi,

thanks for your reply. I forgot to mention that I want to use OLAP-Dimensions as filters.

For Example, if I put the ProductCategory und the ProductSubCategory-Dimension on the filter section in MS Excel (after connecting to a cube) the Drowdown containing the SubCategories is not updated after selecting a ProductCategory.

Of corse, in this case I could use a hierarchy - but this is not suitable in all cases, especially when there is one parameter that should filter the possible values of several other parameters, like in the mentioned saple.

Whishes,

Manfred

|||So, to clarify - the front-end OLAP tool you're using is an Excel pivot table - which version? I'm not sure whether you can implement cascading parameters in Excel OLAP pivot tables, by adding custom event programming.|||

Hi,

we're using 2007 Excel pivot tables. Is this a client-issue or is there a way to define filtering-behavior

on the server-side? Is ProClarity capable of that?

Regards,

Manfred

|||This would be client-side functionality - don't know a way to do this on the server (other than using a dimension hierarchy). As I mentioned, Reporting Services supports this, as do some others - maybe someone more familiar with Proclarity could comment?|||Hi,

I am facing same problem with proclarity.
can anyone suggest how to use cascading filters in proclarity?

Cascading Date for Filter

Hi,

I have a Date Dimension with these attributes:

Datekey

FullDate (eg. 1/1/2007, 1/1/2008)

Year,

Month (eg. Feb 2006, March 2006, April 2006)

If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.

Is it possible?

cherriesh

Hi,

It somewhat depends what tool you are using to query your cube, which tool/s are you using?

Matt

|||my cube is connected to ms excel 2007|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.

HTH

Matt

|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?

cherriesh

|||

Hi,

Yeah. e.g.

Year= 2006

Measure

Month

Jan 20

Feb 30

March 40

april 50

May 60

..

Year= 2007

Measure

Month

Jan 90

Feb 80

March 75

april 60

May 40

..

This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.

Year= ALL

Measure

Month

Jan 110

Feb 110

March 115

april 110

May 100

If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.

HTH

Matt

Cascading Date for Filter

Hi,

I have a Date Dimension with these attributes:

Datekey

FullDate (eg. 1/1/2007, 1/1/2008)

Year,

Month (eg. Feb 2006, March 2006, April 2006)

If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.

Is it possible?

cherriesh

Hi,

It somewhat depends what tool you are using to query your cube, which tool/s are you using?

Matt

|||my cube is connected to ms excel 2007|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.

HTH

Matt

|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?

cherriesh

|||

Hi,

Yeah. e.g.

Year= 2006

Measure

Month

Jan 20

Feb 30

March 40

april 50

May 60

..

Year= 2007

Measure

Month

Jan 90

Feb 80

March 75

april 60

May 40

..

This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.

Year= ALL

Measure

Month

Jan 110

Feb 110

March 115

april 110

May 100

If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.

HTH

Matt

Cascading Date for Filter

Hi,

I have a Date Dimension with these attributes:

Datekey

FullDate (eg. 1/1/2007, 1/1/2008)

Year,

Month (eg. Feb 2006, March 2006, April 2006)

If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.

Is it possible?

cherriesh

Hi,

It somewhat depends what tool you are using to query your cube, which tool/s are you using?

Matt

|||my cube is connected to ms excel 2007|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.

HTH

Matt

|||

Hi,

I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.

So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?

cherriesh

|||

Hi,

Yeah. e.g.

Year= 2006

Measure

Month

Jan 20

Feb 30

March 40

april 50

May 60

..

Year= 2007

Measure

Month

Jan 90

Feb 80

March 75

april 60

May 40

..

This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.

Year= ALL

Measure

Month

Jan 110

Feb 110

March 115

april 110

May 100

If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.

HTH

Matt