Search code examples
sql-server-2008-r2ssasmdx

Changing the value of one column of MDX Query dynamically based on the value of another column

I'm working on an MDX Query. I want the value of one of the columns to change dynamically based on the value of another column. So the query I have written is this :-

WITH 
MEMBER  [Measures].[FName]  AS [Loyalty Cards].[Grouped    Cards].CurrentMember.Properties("Full Name")
MEMBER  [Measures].[RegShop]AS [Loyalty Cards].[Grouped Cards].CurrentMember.Properties("Reg Shop")
MEMBER  [Measures].[Date Of First Bet] AS [Loyalty Cards].[Grouped Cards].CurrentMember.Properties("First Use Date")
MEMBER  [Measures].[MostUsedShop] AS [Loyalty Cards].[Grouped Cards].CurrentMember.Properties("Most Used Shop")
MEMBER  [Measures].[Givex_mem_Num]AS [Loyalty Cards].[Grouped Cards].CurrentMember.Properties("KeyCardNumbers")   

SELECT {
 [Measures].[Givex_mem_Num]
 ,[Measures].[FName]
 ,[Measures].[RegShop]
 ,[Measures].[Date Of First Bet]
 ,[Measures].[MostUsedShop]
 ,[Measures].[Slip Count]
 ,[Measures].[Slip Stake]
 ,[Measures].[SlipGrossWin]
 ,[Measures].[Average Stake Per Slip]
 }ON COLUMNS,
 {
   NONEMPTY([Loyalty Cards].[Grouped Cards].[KeyCardNumbers], [Measures].[Total     Turnover])
 }
  ON ROWS
FROM [RetailCube]
WHERE 
  [Time Slice].[Timeslice].[TimesliceID].&[LIVE2DT]

The sample output I get from the above is: sample output

Now I want the Date Of First Bet Column to change based on the value of Givex_mem_num (especially for the first row, where it is = -99). Can anyone help me out with this? I have tried several approaches.

Till latest, I have modified the first part of my query so:

WITH 
  MEMBER  [Measures].[FName]  AS [Loyalty Cards].[Grouped    Cards].CurrentMember.Properties("Full Name")
  MEMBER  [Measures].[RegShop]AS [Loyalty Cards].[Grouped Cards].CurrentMember.Properties("Reg Shop")
  MEMBER  [Measures].[NullVal] AS NULL
  MEMBER  [Measures].[Date Of First Bet] AS [Loyalty Cards].[Grouped Cards].CurrentMember.Properties("First Use Date")
  MEMBER  [Measures].[MostUsedShop] AS [Loyalty Cards].[Grouped Cards].CurrentMember.Properties("Most Used Shop")
  MEMBER  [Measures].[Givex_mem_Num] AS (
   IIF( [Loyalty Cards].[Grouped Cards].CurrentMember.Properties("KeyCardNumbers")=-99,[Measures].[Date Of First Bet] ='A',[Measures].[Date Of First Bet])
  )         

SELECT {
 [Measures].[Givex_mem_Num]
,[Measures].[FName]
,[Measures].[RegShop]
,[Measures].[Date Of First Bet]
,[Measures].[MostUsedShop]
,[Measures].[Slip Count]
,[Measures].[Slip Stake]
,[Measures].[SlipGrossWin]
,[Measures].[Average Stake Per Slip]
}ON COLUMNS,

But this is changing the Givex_mem_num value to that of the Date of First Bet whihc is nto desired. Please comment if you need more details

Solution

  • You define the value of a single member in the WITH MEMBER clause, you cannot change other members there. You should add another member to the WITH clause like

    MEMBER [Measures].[Calc Date Of First Bet] AS
       IIf([Measures].[Givex_mem_Num] = -99, 'A', [Measures].[Date Of First Bet])

    and then use that member instead of [Measures].[Date Of First Bet] in the list of measures for the columns. Of course, you can rename it like you want, it just should be different from all existing measure names.