SQLTeam.com | Weblogs | Forums

Problem With MDX


#1

Hi,

I'm trying to build a query which will retrieve values from a cube and build a month to date and year to date SSRS report based on the current date. I'm basically trying to build a report that should look something like this...

Type Customer Product Group Quantity Shipped MTD Quantity Shipped YTD

OE CompanyX Bikes 5 396

					Helmets				10 				254


	CompanyY		Repair Kits 			93				653

					T-shirts  				38 				564

					Shoes  				10 				120

I'm really struggling with this and so far, this is what I've come up with...

WITH
MEMBER [Measures].[Quantity Shipped YTD] As Sum(YTD([Date].[Dates].CurrentMember),[Measures].[Quantity Shipped])
Member [Measures].[Quantity Shipped MTD] As Sum(MTD([Date].[Dates].CurrentMember),[Measures].[Quantity Shipped])
SELECT NON EMPTY { [Measures].[Quantity Shipped MTD], [Measures].[Quantity Shipped YTD] } ON COLUMNS,
NON EMPTY { ([Customer].[Business Type].[Business Type].ALLMEMBERS * [Customer].[Customer].[Customer].ALLMEMBERS * [Customer Sales].[Summary Prod Group 1].[Summary Prod Group 1].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( -{ [Customer Sales].[Summary Prod Group 1].&[Missing Product Group] } ) ON COLUMNS
FROM ( SELECT ( { [Customer].[Customer Code].&[2.254E3], [Customer].[Customer Code].&[2.063E3], [Customer].[Customer Code].&[2.183E3], [Customer].[Customer Code].&[2.261E3], [Customer].[Customer Code].&[5.3753E4], [Customer].[Customer Code].&[1.3084E5], [Customer].[Customer Code].&[1.37058E5] } ) ON COLUMNS
FROM ( SELECT ( { [Customer].[Business Type].&[OE] } ) ON COLUMNS
FROM ( SELECT ( [Date].[Day].&[Now()]) ON COLUMNS
FROM [Sales])))) WHERE ( [Customer].[Customer Code].CurrentMember )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The query above doesn't retrieve anything though. All I get is a blank resultset. There are alot more fields that I need to pull through but I'm assuming if I can get quantity shipped to work, I can get everything else. Please help. Tx