MDX Usage Examples

Calculated Member Examples

Example of the WITH MEMBER statement:

WITH MEMBER [measures].[target_difference]  AS
    '[measures].[actualsalessum]-[measures].[predictedsalessum]' 
  SELECT 
  CROSSJOIN([yqm].[all yqm].[2000],
         {[measures].[actualsalessum], 
          [measures].[predictedsalessum],
          [measures].[target_difference]}) ON COLUMNS ,

{[geography].[all geography].[mexico],
 [geography].[all geography].[canada]} ON ROWS

FROM [booksnall]

Example of the WITH MEMBER statement and Format:

WITH MEMBER [measures].[target_difference]  AS
     '[measures].[actualsalessum]-[measures].[predictedsalessum]' ,   
     format_string="dollar20.2"

Example of the CREATE GLOBAL MEMBER statement:

CREATE GLOBAL MEMBER [booksnall].[measures].[percentage_increase] AS 
'([measures].[actualsalessum] - [measures].[predictedsalessum])/    
  [measures].[actualsalessum]',
  format_string="Percent8.2"

Example of the DEFINE MEMBER statement:

DEFINE MEMBER [booksnall].[Measures].[Percentage_Increase] AS 
   '([Measures].[ActualSalesSUM] - [Measures].[PredictedSalesSUM])/
     [Measures].[ActualSalesSUM]' ,
     format_string="Percent8.2"

Example of defining a member with a dimension other than Measures:

WITH MEMBER [geography].[all geography].[non usa] AS
'SUM({[geography].[all geography].[canada],[geography].
[all geography].[mexico]})' 

SELECT {CROSSJOIN({[time].[yqm].[all yqm]}, {[measures].
[actualsalessum]})} ON COLUMNS  , 
{[geography].[all geography].[u.s.a], [geography].[all geography].
[non usa]} ON ROWS  
FROM [booksnall]

Example of the DROP MEMBER statement:

DROP MEMBER [booksnall].[measures].[percentage_increase]

Top of Page