How are you?

If a store sold an item or more, I would like that cell to
be 1. I can do this with the following MDX.

IIF([Measures].[Quantity]>0,1,NULL)

The problem with this is that it will not aggregate up the
hierarchy. Does anybody know how to do this?

Thanks for your interest and help.
Terence
tclauer (AT) badcock (DOT) com

If you mean you want to aggregate the number of stores
that sold items, then this query (you can run it in
Foodmart) might help:

with member measures.items as 'sum(descendants
(store.currentmember,[store name]),iif( [Unit Sales]
{ items } on columns,
descendants(store.usa,[store name],self_and_before) on
rows
from sales

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

Brian,
I copied and pasted your example MDX in to Foodmart. It wouldn't pass
the MDX test.

What I am trying to say is, if a store sold a specfic item one or more
times, than I want it to show "1". If it didn't sale this item, than
NULL. I can get it to do this with the MDX:

IIF([Measures].[Quantity]>0,1,NULL)

So if I drill down on STORES some will have a 1 and others will be
blank. But if I just click on STORES, it still says 1. STORES is the
top and needs to sum all the stores below it that have a 1.

Can you assist.

Thanks a mil.

TC at WSB

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sorry I didn't convert this to FoodMart, hopefully this can still help.
There might be simpler 1 step approaches, but here is what I did to get
store counts for Stores with Sales.

First I created a new calculated measure named "Sales U GT 0" to test
whether sales were greater than zero:
[Measures].[Sales Units Tot]>0
This returns a 1 if true and a zero if false. Then I changed the visible
property to FALSE so the users don't see it.

Next I created another calculated member named "Store Count (Sls)" (we have
another one for Inventory):
SUM(
Descendants(
[Locations].CurrentMember,[Locations].[Store]),
[Measures].[Sales U GT 0])

"T Clauer" <tclauer (AT) badcock (DOT) com> wrote

This is how I did it.

FREQUENCY STORE SALES

IIF(SUM(DESCENDANTS([Organization].[Org Type].
[Store],1,SELF),
[Measures].[Total Qty Sold]>.999)=0,NULL,SUM(DESCENDANTS
([Organization].[Org Type].[Store],
1,SELF),[Measures].[Total Qty Sold]>.999))

FREQUENCY STORE INVENTORY

IIF(SUM(DESCENDANTS([Organization].[Org Type].
[Store],1,SELF),
[Measures].[On Hand Quantity]>.999)=0,NULL,SUM(DESCENDANTS
([Organization].[Org Type].[Store],
1,SELF),[Measures].[On Hand Quantity]>.999))

Thanks for everybody's input.

TC