October 30, 2008 at 10:12 am
Hello. Please help. I am new to MDX.
I can output the table below using
SELECT
NON EMPTY
{[Measures].[Cost of advertising],
{[Measures].[Quantity sold],
[Measures].[Revenue]
} ON COLUMNS,
NON EMPTY
{([Products].[Product name].[Product name].ALLMEMBERS *
[Customer Groups].[Group Name].[Group Name].ALLMEMBERS )
} ON ROWS
FROM [MySalesDW]
============================================================================
Product.............Bought.By........Cost.of.advertising.....Quantity.Sold.......Revenue
Hammer..............Carpenters..........10............................2.......................4
........................Plumbers.............10............................5.................,.....10
........................Masons...............10............................1.......................2
Screw.Driver.......Carpenters............8............................3.......................9
........................Plumbers...............8............................5......................15
........................Masons.................8............................4......................12
Chisel................Carpenters...........12............................2.......................2
........................Plumbers..............12............................6......................6
.........................Masons...............12............................2......................2
============================================================================
But I need an output like the table below. What query should I use? Please help. Many thanks.
==================================================================================
Product.................Cost of advertising..........Quantity sold on Carpenters.........Revenue on Plumbers
Hammer............................10............................. 2........................................10
Screw Driver.......................8...............................3........................................15
Chisel................................12............................. 2........................................6
==================================================================================
October 31, 2008 at 6:38 am
You are going to need to create a couple of additional calculated measures to get the particular slices of data you are looking for.
WITH MEMBER Measures.[Quantity sold - Carpenters] as ([Customer Groups].[Group Name].&[Carpenters], [Measures].[Quantity sold])
MEMBER Measures.[Revenue - Plumbers] as ([Customer Groups].[Group Name].&[Plumbers], [Measures].[Revenue])
SELECT
NON EMPTY
{[Measures].[Cost of advertising],
{[Measures].[Quantity sold - Carpenters],
[Measures].[Revenue - Plumbers]
} ON COLUMNS,
NON EMPTY
{[Products].[Product name].[Product name].ALLMEMBERS
} ON ROWS
FROM [MySalesDW]
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
October 31, 2008 at 8:06 am
Thanks a lot Dan. Just what I needed. Cheers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply