I am a total MDX n00b and I'm having a problem with filtering/slicing on
multiple levels within the same dimension. I have a cube with the
following dimension:

Product: ProductCategory - ProductBrand - ProductSegment - ProductType -
ItemNumber

For example, a candy bar might look like:

"Snacks" - "Snickers" - "Snack Size" - "Snickers Minis" - "12345"

In my cube, there are brands that may belong to multiple categories,
segments that may belong to multiple brands, etc. I need to be able to
pick any combination of levels in that dimension and filter/slice the
cube appropriately. For example, I may want all "Snack Size" "Snacks",
but that may include more brands than just "Snickers". Doing this for
one level of the dimension is easy, but I can't figure out how to do it
on multiple levels at once. Each of the requirements generates a
boolean AND condition. (Like a, you know, filter.)

If I don't do any filtering, the query looks like this:

SELECT
{ Measures.[OrderedQty] } ON COLUMNS,
{ DESCENDANTS(Product.ProductCategory.members) } ON ROWS,
{ InvoiceDate.&[2004].&[4].&[10] } ON PAGES
FROM [Sales]

This query works like a charm. As you can see, if I just want one
month, I've figured out how to do that.

Based on this previous [1] thread, I thought I could do something like:

WITH SET [ProductInfo]
AS ''FILTER(Product.ProductCategory.Members, Product.CurrentMember.Name
= "Snacks")''
SELECT
{ Measures.[OrderedQty], Measures.[PrimaryQty] } ON COLUMNS,
{ DESCENDANTS([ProductInfo]) } ON ROWS,
{ InvoiceDate.&[2004].&[4].&[10] } ON PAGES
FROM [Sales]

This works well and gives me only things with a category of "Snacks".
This is where I'm stuck, however. I can't figure out how to add the
filter for the segment ("Snack Size"). I'm trying to be able to add
them ad-hoc based on the selections of my users.

I've tried a few different ways of doing the filters that I thought
might get me somewhere, but no joy:

FILTER(Product.ProductCategory.Members, Product.ProductCategory.Name =
"WTR")
// a more specific way to say the same thing? returns nothing

FILTER(Product.ProductCategory.Members,
ISANCESTOR(Product.CurrentMember,Product.ProductCa tegory.&[Snacks]))
// seems the same to me, but returns nothing

Help?

[1]
http://groups-beta.google.com/group/...dad1afe1ca973e

I'm thinking that the easiest thing may be to just redimension the cube
into distinct dimensions for Category/Brand/Segment/etc. It's not how
I'd prefer to do it, but it seems to be the easiest way. However, if
anyone should want to show a way it can be done without redimensioning,
I'd really appreciate it.

-R

Rick Osborne wrote:

If AS 2005 (Yukon) is an option, then its attribute-based dimension
model should directly meet your need for flexibility. Otherwise, to
simulate this approach in AS 2000, can you define the relevant
attributes as Member Properties of a lower level, and create Virtual
Dimensions from them?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***