mdx 脚本备份

/*
CALCULATE 命令控制该多维数据集中叶单元的聚合。
如果删除或修改该 CALCULATE 命令，会影响该多维数据集内的数据。
只有手动指定如何聚合该多维数据集时才编辑该命令。
*/
CALCULATE;                 
CREATE MEMBER CURRENTCUBE.[Measures].[预算完成]
 AS [Measures].[销售金额_含税]/[Measures].[预算金额],
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;                 
CREATE MEMBER CURRENTCUBE.[Measures].[客单]
 AS [Measures].[销售金额_含税]/[Measures].[客流数],
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1;                 
CREATE MEMBER CURRENTCUBE.[Measures].[总毛利]
 AS ( [Measures].[销售金额]-[Measures].[成本金额]),
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1;          
CREATE MEMBER CURRENTCUBE.[Measures].[日总毛利率]
 AS ( [Measures].[销售金额]-[Measures].[成本金额])/[Measures].[成本金额],
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;                 
CREATE MEMBER CURRENTCUBE.[Measures].[正常商品毛利]
 AS ( [Measures].[销售金额]-[Measures].[成本金额])/[Measures].[成本金额],
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;                 
CREATE MEMBER CURRENTCUBE.[Measures].[周至今预算]
 AS sum(LASTPERIODS(7,[日期].[月周日期]  .currentmember), [Measures].[预算金额] ),
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1;               
CREATE MEMBER CURRENTCUBE.[Measures].[周实际销售]
 AS sum(LASTPERIODS(7,[日期].[月周日期]  .currentmember),[Measures].[销售金额_含税]  ),
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1;               
CREATE MEMBER CURRENTCUBE.[Measures].[周预算完成]
 AS [Measures].[周实际销售] / [Measures].[周至今预算],
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;               
CREATE MEMBER CURRENTCUBE.[Measures].[周客流]
 AS sum(LASTPERIODS(7,[日期].[月周日期]  .currentmember),[Measures].[客流数]  ),
VISIBLE = 1;               
CREATE MEMBER CURRENTCUBE.[Measures].[周客单]
 AS sum(LASTPERIODS(7,[日期].[月周日期]  .currentmember),sum([Measures].[销售金额_含税]))/ sum(LASTPERIODS(7,[日期].[月周日期]  .currentmember),sum( [Measures].[客流数] )),
VISIBLE = 1;               
CREATE MEMBER CURRENTCUBE.[Measures].[周毛利]
 AS sum(LASTPERIODS(7,[日期].[月周日期]  .currentmember),[Measures].[总毛利] ) /  
sum(LASTPERIODS(7,[日期].[月周日期]  .currentmember), [Measures].[销售金额]),
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;                

CREATE MEMBER CURRENTCUBE.[Measures].[月至今预算]
 AS sum(LASTPERIODS(12,[日期].[月周日期]  .currentmember), [Measures].[预算金额] ),
VISIBLE = 1;  
CREATE MEMBER CURRENTCUBE.[Measures].[月实际销售]
 AS sum(LASTPERIODS(12,[日期].[月周日期]  .currentmember),[Measures].[销售金额_含税]  ),
VISIBLE = 1;              
CREATE MEMBER CURRENTCUBE.[Measures].[月预算完成]
 AS  sum(LASTPERIODS(12,[日期].[月周日期]  .currentmember),[Measures].[销售金额_含税]  ) / sum(LASTPERIODS(12,[日期].[月周日期]  .currentmember), [Measures].[预算金额] ),
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;           
CREATE MEMBER CURRENTCUBE.[Measures].[月客流]
 AS sum(LASTPERIODS(12,[日期].[月周日期]  .currentmember),[Measures].[客流数]  ),
VISIBLE = 1;   
CREATE MEMBER CURRENTCUBE.[Measures].[月客单]
 AS sum(LASTPERIODS(12,[日期].[月周日期]  .currentmember),sum([Measures].[销售金额_含税]))/ sum(LASTPERIODS(12,[日期].[月周日期]  .currentmember),sum( [Measures].[客流数] )),
VISIBLE = 1;  
CREATE MEMBER CURRENTCUBE.[Measures].[月毛利金额]
 AS sum(LASTPERIODS(12,[日期].[年月日].currentmember),[Measures].[总毛利] ),
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1;   

CREATE MEMBER CURRENTCUBE.[Measures].[月毛利]
 AS sum(LASTPERIODS(12, [日期].[年月日] .currentmember),[Measures].[总毛利] ) /
sum(LASTPERIODS(12,[日期].[年月日] .currentmember), [Measures].[销售金额]),
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;        

CREATE MEMBER CURRENTCUBE.[Measures].[年毛利金额]
 AS sum(LASTPERIODS(365,[日期].[月周日期]  .currentmember),[Measures].[总毛利] ),
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1;   
CREATE MEMBER CURRENTCUBE.[Measures].[年毛利]
 AS sum(LASTPERIODS(365,[日期].[月周日期]  .currentmember),[Measures].[总毛利] ) /
sum(LASTPERIODS(365,[日期].[月周日期]  .currentmember), [Measures].[销售金额]),
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;    
 

CREATE MEMBER CURRENTCUBE.[Measures].[部门日报占比]
 AS sum(descendants( [部门].[上级部门].currentmember,
 [部门].[上级部门].[Level 03]), [Measures].[销售金额_含税] )/
sum(descendants( [部门].[上级部门].currentmember,
 [部门].[上级部门].[Level 02]), [Measures].[销售金额_含税] ),
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;      
CREATE MEMBER CURRENTCUBE.[Measures].[仓库日报占比]
 AS sum(descendants(  [仓库].[上级名称].currentmember,
[仓库].[上级名称].[Level 05]  ), [Measures].[销售金额_含税] )/
sum(descendants( [部门].[上级部门].currentmember,
  [仓库].[上级名称].[Level 04]), [Measures].[销售金额_含税] ),
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;        

CREATE MEMBER CURRENTCUBE.[Measures].[可周转天数]
 AS [Measures].[库存数量]/
avg(
filter({
PARALLELPERIOD([日期].[月周日期].[周],0,[日期].[月周日期].currentmember) as [SpeCurrent],
PARALLELPERIOD([日期].[月周日期].[周],1,[日期].[月周日期].currentmember),
PARALLELPERIOD([日期].[月周日期].[周],2,[日期].[月周日期].currentmember),
PARALLELPERIOD([日期].[月周日期].[周],3,[日期].[月周日期].currentmember),
PARALLELPERIOD([日期].[月周日期].[周],4,[日期].[月周日期].currentmember),
PARALLELPERIOD([日期].[月周日期].[周],5,[日期].[月周日期].currentmember),
PARALLELPERIOD([日期].[月周日期].[周],6,[日期].[月周日期].currentmember),
PARALLELPERIOD([日期].[月周日期].[周],7,[日期].[月周日期].currentmember)
},
ancestor([日期].[月周日期].currentmember,[日期].[月周日期].[月]) is ancestor([SpeCurrent].Item(0), [日期].[月周日期].[月]).prevmember
),
[Measures].[库存数量]),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Measures].[开业至今天数]
 AS sum(LASTPERIODS(365,[日期].[月周日期]  .currentmember), descendants( [日期].[年月日].currentmember ,[日期].[年月日].[日期]).count),
VISIBLE = 1  ;      

CREATE MEMBER CURRENTCUBE.[Measures].[仓库04]
 AS sum(descendants( [仓库].[上级名称].currentmember,
[仓库].[上级名称].[Level 04]  ), [Measures].[销售金额_含税] )
 ,
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;      

CREATE MEMBER CURRENTCUBE.[Measures].[仓库05]
 AS sum(descendants( [仓库].[上级名称].currentmember,
[仓库].[上级名称].[Level 05]  ), [Measures].[销售金额_含税] )
 ,
FORMAT_STRING = "#,##0.00%;-#,##0.00%",
VISIBLE = 1;

转载于:https://www.cnblogs.com/muzifei/archive/2011/09/25/2190197.html