MDX script: MdxScript

CollapseAll image

Collapse image MDX script properties


 Property   Value 
 Parent cube   Adventure Works 
 Description    
 Default script   True 

Collapse image Calculations


Name  Type  Description 
[Measures].[Discount Percentage] Member  
[Measures].[Internet Ratio to All Products] Member  
[Measures].[Internet Ratio to Parent Product] Member  
[Measures].[Reseller Ratio to All Products] Member  
[Measures].[Reseller Ratio to Parent Product] Member  
[Measures].[Ratio to All Products] Member  
[Measures].[Ratio to Parent Product] Member  
[Measures].[Growth in Customer Base] Member  
Long Lead Products Set  
Core Product Group Set  
Large Resellers Set  
High Discount Promotions Set  
[Measures].[Reseller Average Sales Amount] Member  
[Measures].[Reseller Average Unit Price] Member  
[Measures].[Internet Average Unit Price] Member  
[Measures].[Internet Average Sales Amount] Member  
[Measures].[Internet Gross Profit] Member  
[Measures].[Internet Gross Profit Margin] Member  
[Measures].[Reseller Gross Profit] Member  
[Measures].[Reseller Gross Profit Margin] Member  
[Measures].[Gross Profit] Member  
[Measures].[Gross Profit Margin] Member  
[Measures].[Average Unit Price] Member  
[Measures].[Average Sales Amount] Member  
Total: 24 calculation(s)

Collapse image Script view


/*-- Aggregate leaf data -----------------------------------------------*/
Calculate;         


/*-----------------------------------------------------------------------------
 | Internet Sales Calculations |
-----------------------------------------------------------------------------*/


Create Member CurrentCube.[Measures].[Internet Gross Profit]

 As [Measures].[Internet Sales Amount] 
    - 
    [Measures].[Internet Total Product Cost],
 
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = 
 { 
   [Internet Sales Amount],
   [Internet Total Product Cost] 
 };                             


Create Member CurrentCube.[Measures].[Internet Gross Profit Margin]

 As ( 
      [Measures].[Internet Sales Amount] 
      - 
      [Measures].[Internet Total Product Cost] 
    ) 
    /
    [Measures].[Internet Sales Amount],
 
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales', 
Non_Empty_Behavior = 
 { 
   [Internet Sales Amount],
   [Internet Total Product Cost] 
 };                           


Create Member CurrentCube.[Measures].[Internet Average Unit Price]

 As [Measures].[Internet Unit Price] 
    /
    [Measures].[Internet Transaction Count],
 
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales', 
Non_Empty_Behavior = [Internet Unit Price];       


Create Member CurrentCube.[Measures].[Internet Average Sales Amount]

 As [Measures].[Internet Sales Amount] 
    /
    [Measures].[Internet Order Count],
 
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales', 
Non_Empty_Behavior = [Internet Sales Amount];       


Create Member CurrentCube.[Measures].[Internet Ratio to All Products]

 As [Measures].[Internet Sales Amount]
    /
    ( 
      Root( [Product] ),
      [Measures].[Internet Sales Amount] 
    ),

Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Sales Amount];                             
                        

Create Member CurrentCube.[Measures].[Internet Ratio to Parent Product]

 As Case

        When [Product].[Product Categories].CurrentMember.Level.Ordinal 
             = 0
        Then 1

        Else [Measures].[Internet Sales Amount]
             /
             ( [Product].[Product Categories].CurrentMember.Parent,
               [Measures].[Internet Sales Amount] )

    End,
 
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales';        


Create Member CurrentCube.[Measures].[Growth in Customer Base]

 As Case

        When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0
        Then "NA"

        When IsEmpty
             (  
               ( 
                 [Date].[Fiscal].CurrentMember.PrevMember, 
                 [Measures].[Customer Count] 
               ) 
             ) 
        Then Null

        Else ( 
               ( [Date].[Fiscal].CurrentMember, [Measures].[Customer Count] ) 
               -
               ( [Date].[Fiscal].PrevMember, [Measures].[Customer Count] ) 
             ) 
             /
             ( [Date].[Fiscal].PrevMember,[Measures].[Customer Count] )

    End,
 
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales';           


/*-----------------------------------------------------------------------------
 | Reseller Sales Calculations |
-----------------------------------------------------------------------------*/           


Create Member CurrentCube.[Measures].[Reseller Gross Profit]

 As [Measures].[Reseller Sales Amount] 
    - 
    [Measures].[Reseller Total Product Cost],
 
Format_String = "Currency",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior = 
 { 
   [Reseller Sales Amount],
   [Reseller Total Product Cost] 
 };                             


Create Member CurrentCube.[Measures].[Reseller Gross Profit Margin]

 As ( 
      [Measures].[Reseller Sales Amount] 
      - 
      [Measures].[Reseller Total Product Cost] 
    ) 
    /
    [Measures].[Reseller Sales Amount],
 
Format_String = "Percent",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior = 
 { 
   [Reseller Sales Amount],
   [Reseller Total Product Cost] 
 };                           


Create Member CurrentCube.[Measures].[Reseller Average Unit Price]

 As [Measures].[Reseller Unit Price] 
    /
    [Measures].[Reseller Transaction Count],
 
Format_String = "Currency",
Associated_Measure_Group = 'Reseller Sales', 
Non_Empty_Behavior = [Reseller Unit Price];      


Create Member CurrentCube.[Measures].[Reseller Average Sales Amount]

 As [Measures].[Reseller Sales Amount] 
    /
    [Measures].[Reseller Order Count],
 
Format_String = "Currency", 
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior = [Reseller Sales Amount];   


Create Member CurrentCube.[Measures].[Reseller Ratio to All Products]

 As [Measures].[Reseller Sales Amount]
    /
    ( 
      Root( [Product] ),
      [Measures].[Reseller Sales Amount] 
    ),

Format_String = "Percent",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior = [Reseller Sales Amount];                         


Create Member CurrentCube.[Measures].[Reseller Ratio to Parent Product]

 As Case

        When [Product].[Product Categories].CurrentMember.Level.Ordinal 
             = 0
        Then 1

        Else [Measures].[Reseller Sales Amount]
             /
             ( [Product].[Product Categories].CurrentMember.Parent,
               [Measures].[Reseller Sales Amount] )

    End,
 
Format_String = "Percent",
Associated_Measure_Group = 'Reseller Sales';                                  
 
   
Create Member CurrentCube.[Measures].[Discount Percentage]

 As [Measures].[Discount Amount] 
    / 
    [Measures].[Reseller Sales Amount],

Format_String = "Percent",
Associated_Measure_Group = 'Reseller Sales', 
Non_Empty_Behavior = [Discount Amount];      


/*-----------------------------------------------------------------------------
 | Sales Summary Calculations |
-----------------------------------------------------------------------------*/


Create Member CurrentCube.[Measures].[Average Unit Price]

 As [Measures].[Unit Price] 
    /
    [Measures].[Transaction Count],
 
Format_String = "Currency",
Associated_Measure_Group = 'Sales Summary',  
Non_Empty_Behavior = [Unit Price];       


Create Member CurrentCube.[Measures].[Average Sales Amount]

 As [Measures].[Sales Amount] 
    /
    [Measures].[Order Count],
 
Format_String = "Currency",
Associated_Measure_Group = 'Sales Summary',  
Non_Empty_Behavior = [Sales Amount];      


Create Set CurrentCube.[Long Lead Products]

 As Exists
    (
       [Product].[Model Name].[Model Name].Members,
       [Product].[Days to Manufacture].&[4]
    ) 
,
Display_Folder = 'Sets';       


Create Set CurrentCube.[Core Product Group]

As  DrillDownMember
    (
      [Product].[Product Categories].[Category].Members, 
      [Product].[Product Categories].[Category].[Bikes]
    ) 
,
Display_Folder = 'Sets';           


Create Set CurrentCube.[Large Resellers]

 As Exists
    (
       [Reseller].[Reseller].[Reseller].Members,
       [Reseller].[Number of Employees].[Number of Employees].[81 - 100]
    ) 
,
Display_Folder = 'Sets';       


Create Set CurrentCube.[High Discount Promotions]

As  Exists
    (
       [Promotion].[Promotion].[Promotion].Members,
       Filter
       (
          [Promotion].[Discount Percent].[Discount Percent].Members,
          [Promotion].[Discount Percent].CurrentMember.MemberValue >= .30
       )
    ) 
,
Display_Folder = 'Sets';   


Create Dynamic Set CurrentCube.[Top 25 Selling Products]

 As TopCount
    (
       [Product].[Product].[Product].Members,
       25,
       [Measures].[Sales Amount]
    ) 
,
Display_Folder = 'Sets';    


Create Dynamic Set CurrentCube.[Top 50 Customers]

 As TopCount
    (
       [Customer].[Customer].[Customer].Members,
       50,
       [Measures].[Internet Sales Amount]
    ) 
,
Display_Folder = 'Sets';    


Create Dynamic Set CurrentCube.[Negative Margin Products] 

 As Filter
    (
       [Product].[Subcategory].[Subcategory], 
       [Measures].[Gross Profit Margin] < 0
    )
,
Display_Folder = 'Sets';  


Create Member CurrentCube.[Measures].[Gross Profit]

 As [Measures].[Sales Amount] 
    - 
    [Measures].[Total Product Cost],

Format_String = "Currency",
Associated_Measure_Group = 'Sales Summary',  
Non_Empty_Behavior = 
 { 
   [Measures].[Sales Amount],
   [Measures].[Total Product Cost] 
 };                 


Create Member CurrentCube.[Measures].[Gross Profit Margin]

 As ( 
      [Measures].[Sales Amount] 
      - 
      [Measures].[Total Product Cost] 
    ) 
    /
    [Measures].[Sales Amount],

Format_String = "Percent",
Associated_Measure_Group = 'Sales Summary',  
Non_Empty_Behavior = 
 { 
   [Measures].[Sales Amount],
   [Measures].[Total Product Cost] 
 };        


Create Member CurrentCube.[Measures].[Ratio to All Products]

 As [Measures].[Sales Amount]
    /
    ( 
      Root( [Product] ),
      [Measures].[Sales Amount] 
    ),

Format_String = "Percent",
Associated_Measure_Group = 'Sales Summary', 
Non_Empty_Behavior = { [Sales Amount] };                          


Create Member CurrentCube.[Measures].[Ratio to Parent Product]

 As Case

        When [Product].[Product Categories].CurrentMember.Level.Ordinal 
             = 0
        Then 1

        Else [Measures].[Sales Amount]
             /
             ( [Product].[Product Categories].CurrentMember.Parent,
               [Measures].[Sales Amount] )

    End,
 
Format_String = "Percent",
Associated_Measure_Group = 'Sales Summary';    


/*-----------------------------------------------------------------------------
 | Sales Quota Allocation |
-----------------------------------------------------------------------------*/


/*-- Allocate equally to quarters in H2 FY 2013 ------------------------*/ 

 Scope 
 ( 
    [Date].[Fiscal Year].&[2013],
    [Date].[Fiscal].[Fiscal Quarter].Members,
    [Measures].[Sales Amount Quota]
 );     
   
   This = ParallelPeriod                             
          ( 
             [Date].[Fiscal].[Fiscal Year], 1,
             [Date].[Fiscal].CurrentMember 
          ) * 1.35;  
 

/*-- Allocate equally to months in FY 2011 -----------------------------*/

  Scope 
  ( 
     [Date].[Fiscal Year].&[2011],
     [Date].[Fiscal].[Month].Members 
  );     

    This = [Date].[Fiscal].CurrentMember.Parent / 3;     

  End Scope;     


/*-- Weighted allocation to remaining months ---------------------------*/


  // Pin quarterly values prior to assigning weights for months
  // This is done in order to avoid overwriting the quarterly values
  // once weights are entered for monthly values.
  Freeze 
  ( 
     [Date].[Fiscal].[Fiscal Quarter].Members, 
     [Measures].[Sales Amount Quota] 
  );     

  // Scope on month level in FY 2012 and onwards
  Scope 
  ( 
     [Date].[Fiscal Year].&[2012] : Null,
     [Date].[Fiscal].[Month].Members
  );     

   // Compute weights based on reseller sales ratio in previous year
   This = 
   ( 
     ParallelPeriod         // Fetch reseller sales amount in previous year                     
     ( 
        [Date].[Fiscal].[Fiscal Year], 1,
        [Date].[Fiscal].CurrentMember 
     ),
     [Measures].[Reseller Sales Amount] 
   )
   /
   ( 
     ParallelPeriod         // Divide monthly value by quarterly value to obtain ratio
     ( 
        [Date].[Fiscal].[Fiscal Year], 1,
        [Date].[Fiscal].CurrentMember.Parent 
     ),
     [Measures].[Reseller Sales Amount] 
   );     

   // Allocate quarterly values to months according to weight
   This = [Measures].CurrentMember * [Date].[Fiscal].Parent;  
  
  End Scope;     
End Scope; 

Collapse image See also


About this documentation