Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

don't return all members

Status
Not open for further replies.

Crowley16

Technical User
Jan 21, 2004
6,931
GB
I've got a mdx query that when run, is returning the all members of each hierarchy level.
how can I filter the "All" members out?

p.s.
The code is adapted from auto generated code, so any pointers for making it better would be appreciated as well

Code:
SELECT 
	{
		[Measures].[Actual Sales]
		, [Measures].[Like For Like Sales]
	} DIMENSION PROPERTIES PARENT_UNIQUE_NAME 
ON COLUMNS 
	, CrossJoin
	(
		Hierarchize
		(
			DrilldownMember
			(
				DrilldownMember
				(
					DrilldownLevel([Store].[Store By Company].[All])
					, {
						[Store].[Store By Company].[Operation Types].&[Book's and Music Stores]
						, [Store].[Store By Company].[Operation Types].&[Standard Stores]
					}
				)
				, [Store].[Store By Company].[Regions]				
			)
		), 
		{
			[Product].[Product By Source].[Source].&[ALL OTHER]
			, [Product].[Product By Source].[Source].&[ACCESSORIES]
			, [Product].[Product By Source].[Source].&[CHRISTMAS CARDS]
			, [Product].[Product By Source].[All].[Hanging Goods Total]
			, [Product].[Product By Source].[Source].&[PROMOTIONAL]
		}
	) 
ON ROWS  
FROM 
	(
		SELECT (
			{[Store].[Store By Company].[Operation Types].&[Standard Stores]
			, [Store].[Store By Company].[Operation Types].&[Book's and Music Stores]}
		) ON COLUMNS  
		FROM [DailySales_PlanTypeII]
	) 
WHERE 
	[Date].[Date By Week].[Cal Week].&[26]&[6]&[2010] 
CELL PROPERTIES VALUE

--------------------
Procrastinate Now!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top