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?