MDX Question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

MDX Question

Hello, I have three dimension: Date : Year/Month/Week Ending
Location: Region/District/Location
Item: Group/Division/Style/Item
For a given Week Ending, I need to be able to calculate how many items are at a specific location vs the total items in all locations (Location OH / Total OH). For instance: I have 10 widgets in Location 1; 20 widgets in Location 2; 20 widgets in Location 3. I want to be able to say as a calculated member that 20% of widgets are Location 1; 40% are in Location 2 and 40% are in Location 3. In order to do this, I need the ability to sum all item numbers across the date. Any ideas how to get the total count of items across all locations; w/o maintaining the information in the fact tables? Basically, does someone know the MDX syntax for this calculated member? Below is a stab I’ve made. I know the part that returns the Comp Location works correctly but I am having troubles spanning it across all Locations for that day. —————————————————————-
MEMBER [Measures].[Week Total] AS
‘SUM([Item].CurrentMember, ([Measures].[Comp Location Qty], OpeningPeriod([Week Ending], [Date].CurrentMember))’
—————————————————————- Thanks in advance, Jeff
Try this WITH
MEMBER [Measures].[Week Total] AS
‘SUM([Item].CurrentMember, ([Measures].[Comp Location Qty], OpeningPeriod([Week Ending], [Date].CurrentMember))’
SELECT [LOCATION].[LOCATION].MEMBERS ON COLUMNS,
[MEASRUES].MEMBERS ON ROWS
FROM MY_CUBE

]]>