Is the below possible in Analysis services? How do I write MDX for this

Lets say Hierarchy is as below :-
Drugs
Brand (e.g Bayer, GV,Generic)
DrugName (e.g Asprin,PainReliever etc)

Measure = Unit Sales
Now asprin could be present across brands.
I want to see total asprin sales across brands. or regardless of brand
Is it possible to represent above query as MDX ?
Regards,
Akshay

WITH MEMBER MEASURES.[Asprin sales] as
'SUM({[product].[drugname].MEMBERS},
(IIF([PRODUCT].Currentmember.name = "Asprin",MEASURES.[unit
sales], 0)))'
SELECT MEASURES.ALLMEMBERS ON COLUMNS from sales

not that the comparison of "IIF" is case sensitive.

I am assuming the name of the dimension of drugs is "product"

Please let me now if this worked or not.

Thanks,

Asim Naveed

--
Posted via http://dbforums.com

SUM(Filter(DrugName.MEMBERS, Drugs.CurrentMember.Name = "Aspirin"),
Measures.[Unit Sales])

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================
"akshayk" <akshay349 (AT) hotmail (DOT) com> wrote