MDX CALUCATIONS Examples---

---CALUCATIONS:

  --WITH MEMBER CALCUCATION <NAME>

    --<FORMULA>--

   

    WITH MEMBER [TEST] AS

      [MEASURES].[SALES AMOUNT],

  FORMAT_STRING ="#,##.00"

 SELECT

      {[MEASURES].[SALES AMOUNT],TEST

      } ON COLUMNS,

      NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]) ON ROWS

 FROM [ADVENTURE WORKS DW2008R2]

  

  

 CREATE MEMBER [ADVENTURE WORKS DW2008R2].[TEST] AS

   [MEASURES].[SALES AMOUNT],

    FORMAT_STRING ="#,##.00"

   

      --OFTER CALL   

    SELECT

      {[MEASURES].[SALES AMOUNT],TEST

      } ON COLUMNS,

      NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]) ON ROWS

 FROM [ADVENTURE WORKS DW2008R2]

 ---EXAMPLES

   WITH MEMBER [NAME] AS

   [DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.NAME

   SELECT

      {[MEASURES].[SALES AMOUNT],[NAME]

      } ON COLUMNS,

      NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]) ON ROWS

 FROM [ADVENTURE WORKS DW2008R2]

   WITH MEMBER [SALES] AS

  [MEASURES].[SALES AMOUNT],

  FORMAT_STRING="#,##.00"

  MEMBER [TOTAL SALES] AS

  SUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.PARENT,[MEASURES].[SALES AMOUNT]) ,

  FORMAT_STRING="#,##.00"

  MEMBER [SALES_PERSANTAGE] AS

//    ([SALES]/[TOTAL SALES])*100,

//      FORMAT_STRING="#,##.00"

 ([SALES]/[TOTAL SALES]),

 FORMAT_STRING="PERCENT"

     

     

 MEMBER [PERCENT] AS

 [MEASURES].[SALES AMOUNT]/SUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.PARENT,[MEASURES].[SALES AMOUNT]),

 FORMAT_STRING="PERCENT"

 MEMBER [DIF] AS

 SUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.PARENT,[MEASURES].[SALES AMOUNT]) -

 SUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.NEXTMEMBER,[MEASURES].[SALES AMOUNT])

   SELECT

      {[MEASURES].[SALES],[TOTAL SALES],[SALES_PERSANTAGE],[PERCENT],[DIF]

      } ON COLUMNS,

      --NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]) ON ROWS

ORDER(NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]),[SALES_PERSANTAGE]) ON ROWS

 FROM [ADVENTURE WORKS DW2008R2]

Hope It will help u.....