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.
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.
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?
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.
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.