usin set functions in case statement

[Sep2013]

Hi,

i have a requirement where i am using a dimensional model.
I have a product attribute called product group.
I want to use that attribute to filter the products accordingly.
I have 1 prompt for product group and second cascaded prompt for products.
Now, since i do not have levels in hierarcy i am using set function as below for second prompt


case
?Parameter1?
when 'Internet' then
set ([ADSL - Bundle Proxy (LSS)],[ADSL - Bundle proxy (Off-net)])
when 'Hardware' then 'Ab' End

Now, this works fine, but if i add one more "When" in case statement inclusing set function, it doesnt work.
can anyone help me in this.

[MFGF]

Hi,

Assuming Group is an attribute of each product member on your Products level, why not just do the following:

filter([Your Products level], [Your Group attribute] = ?Parameter1?)

No need for a case statement :)

(This would be a calculated item in the query driving your Product prompt)

Cheers!

MF.

[Sep2013]

But there is no concept of levels in hierarchy, and there are about 100 products. So can i drag and drop all the products in the formula?

[Sep2013]

That worked! But now the problem is that it is not necessary that all products will lie at a specific level, it may be level 4,5,6. So the step 1., doesnt work as it will fetch me only the "Level5" memebers. is there any way i can fetch the leaf memebers of a hierarchy?

Steps:
1.   Fetch the leaf members using the following function:
descendants ([Total Without Interest],3): The [Total Without Interest] is 'level 2' and this expression gets all the members in 'level 5' independent of the 'level 3' and  'level 4'.

2.   Then use the following function:
filter([Data Item2],[Product_Group]=?Parm1?): This expression filters the members from step 1, according to the "Product_Group" attribute (which is equal to the group selected in the "Product Group" prompt.

[MFGF]

Hi,

I'm not quite clear on your requirement here, sorry. What levels does the hierarchy have? What rules do you need to be able to retrieve the desired members?

MF.