MDX- CALCULATION BY ITEM()

来源：互联网发布：淘宝子账号不能登录编辑：程序博客网时间：2024/06/06 01:55

WITH MEMBER [measures].[First Year Internet Sales Amount] AS         {{[Date].[Calendar Year].&[2006],[Date].[Calendar Year].&[2007],[Date].[Calendar Year].&[2008]}            *{ [Measures].[Internet Sales Amount]}      }.Item (0) // Item(0) is used to get the first year measure   , FORMAT_STRING = "Currency"   , SOLVE_ORDER = 1MEMBER [measures].[Variance]  AS([Measures].[Internet Sales Amount] - [measures].[First Year Internet Sales Amount]), SOLVE_ORDER = 2MEMBER [Measures].[PercentageVariance] AS(CASE WHEN [measures].[First Year Internet Sales Amount] IS NULL OR ISEMPTY([measures].[First Year Internet Sales Amount]) THEN 1     WHEN [measures].[Internet Sales Amount] IS NULL THEN -1ELSE [measures].[Variance]  /[measures].[First Year Internet Sales Amount] END ),FORMAT_STRING = "Percent", SOLVE_ORDER = 3  SELECT    {  [measures].[First Year Internet Sales Amount],[Measures].[Internet Sales Amount]      , [measures].[Variance],[Measures].[PercentageVariance]    }  ON 0, [Date].[Calendar Year].children ON ROWSFROM (select {[Date].[Calendar Year].&[2006],[Date].[Calendar Year].&[2007],[Date].[Calendar Year].&[2008]} on 0 from [Adventure Works])

First Year Internet Sales Amount Internet Sales Amount Variance PercentageVariance
CY 2006 $6,530,343.53 $6,530,343.53 $0.00 0.00%
CY 2007 $6,530,343.53 $9,791,060.30 $3,260,716.77 49.93%
CY 2008 $6,530,343.53 $9,770,899.74 $3,240,556.21 49.62%