Defining and Qualifying a New Member Example

On the ROWS axis, show the Actual Sales for the states {WA, OR, CA} as a percentage of the sales in the rest of the USA (except Hawaii and Alaska) for the following: [All] Products in the Applications Division, [All] Versions of GreatOffice, and Database 96. On the COLUMNS axis, show all quarters and all months for 1997.

The dataset defined above is retrieved by the following MDX statement:

WITH MEMBER
   USA.[North West Ratio] AS SUM({WA, [OR], CA}, Sales) /
   SUM(EXCEPT(USA.CHILDREN, {Hawaii, Alaska}), Sales)
SELECT HIERARCHIZE({[1997], [1997].CHILDREN, DESCENDANTS([1997].
                   Months)}) ON ROWS,
                   {[Applications Division] , GreatOffice.CHILDREN,
                   [Database 96]} ON COLUMNS
FROM SalesCube
WHERE ([North West Ratio], Actual)

The WITH MEMBER clause defines a new member called North West Ratio. This member is obtained by taking the percentage of the sum of Sales for the states OR, WA, and CA and dividing this by the sales for all states in the USA except Alaska and Hawaii. By qualifying the name of this member with USA, the data source object recognizes that this should be on the Geography dimension.