My question has two parts. The below situation would be equivalent to my real
world scenario.

1. In the FoodMart 2000 database, I would like to calculate sales/square
foot (of store size).
2. I only want to calculate this value for some dimensions (ie I don't want
sales/square foot for each product or for each gender or the household
income. For those dimensions, I would only want the measure to be calculated
if All Gender and All Household Income and All Product were selected.) I do
want to know it for each store, city, region, country and at the all stores
level. I also want to know it for each time period from year to month.

Thanks, Matt

Will a named set with the units to be calculated work.
Then use the named set to create your results formula.

Joe

"MattFloyd" wrote:

Are you answering the second part of the question? The problem with the first
part is that store size is a member property and a dimension in the Foodmart
2000 Sales cube.

"jam96-BuffaloJoe" wrote:

If you subscribe to SQL Server Magazine, which I wholeheartedly recommend,
one of Russ Whitney's best articles is from the August 2003 issue titled:
"Member-Properties Boot Camp"
http://www.winnetmag.com/Article/Art...384/39384.html
He outlines 10 ways to effectively use member properties. It would be
extremely useful here.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"MattFloyd" <MattFloyd (AT) discussions (DOT) microsoft.com> wrote

You can try this Foodmart Sales MDX query, which defines calculated
measures for [StoreSqft] and [SalesPerSqft]:

Member [Measures].[StoreSqft] as
'Sum(Descendants([Store].CurrentMember, [Store].[Store Name]),
Val([Store].Properties("Store Sqft")))'

Member [Measures].[SalesPerSqft] as
'iif([Gender].CurrentMember is [Gender].[All Gender]
and [Product].CurrentMember is [Product].[All Products],
[Measures].[Store Sales]/[Measures].[StoreSqft], NULL)',
FORMAT_STRING = 'Currency'

Select
{[Measures].[Store Sales], [Measures].[StoreSqft],
[Measures].[SalesPerSqft]} on columns,
Descendants([Store].[All Stores].[USA].[OR],
[Store].[Store State], SELF_AND_AFTER) on rows
from Sales

- Deepak

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