Google Track

Sunday, March 25, 2012

Basic Calculations in MDX

// Connect to 2005 Adventure Works

// Gross margin using 1-a/b
WITH
  MEMBER [Gross Margin] AS
       1
    -
        [Measures].[Internet Total Product Cost]
      /
        [Measures].[Internet Sales Amount]
    , FORMAT_STRING = 'Percent'
  MEMBER [Max Gross Margin] AS
    Max
    (
      (
        [Customer].[Customer Geography].[Customer]
       ,[Product].[Product Categories].[Subcategory]
       ,[Date].[Calendar].[Calendar Year]
      )
     ,[Measures].[Gross Margin]
    )
SELECT
  [Measures].[Max Gross Margin] ON 0
FROM [Adventure Works];

// Gross margin using (b-a)/b
WITH
  MEMBER [Gross Margin] AS
      (
        [Measures].[Internet Sales Amount]
      -
        [Measures].[Internet Total Product Cost]
      )
    /
      [Measures].[Internet Sales Amount]
    , FORMAT_STRING = 'Percent'
  MEMBER [Max Gross Margin] AS
    Max
    (
      (
        [Customer].[Customer Geography].[Customer]
       ,[Product].[Product Categories].[Subcategory]
       ,[Date].[Calendar].[Calendar Year]
      )
     ,[Measures].[Gross Margin]
    )
SELECT
  [Measures].[Max Gross Margin] ON 0
FROM [Adventure Works];

No comments: