-
MDX Query
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|