MDX数据计算

Use cube_test;

Scope([Scenario].[Budget], [Version].[V1], [Year].[2022],MemberSet([Period].[Q1]),MemberSet([Product].[TotalFood],-1,Leaves),MemberSet([Entity].[TotalEntity],-1,Leaves));
    Cleardata([Account].[Total_Sales],[Account].[Price]);
End Scope;

Scope([Scenario].[Budget], [Version].[V1], [Year].[2022],MemberSet([Period].[Q1]),MemberSet([Product].[TotalFood],-1,Leaves),MemberSet([Entity].[TotalEntity],-1,Leaves));
    Cleardata([Account].[Total_Sales]);
    [Account].[Total_Sales]  = [Account].[Volume]*[Account].[Price]->[Period].[BegBalance]+[Account].[Sales_Adjs];
End Scope;

Scope(
[Scenario].[Budget], 
[Version].[V1], 
[Year].[2022],
Base([Period].[TotalPeriod])
IDescendant([Entity].[Total])
);

// 根据Entity维度的ud1做判断，
// ud1等于Shanghai的Entity成员按照Amount1+Amount2计算，
// 其余Entity成员按照Amount1+Amount3计算。

IF [Entity].CurrentMember.Properties("ud1") = "Shanghai"
//等价于 IF Properties(CurrentMember([Entity]),"ud1") = "Shanghai"

THEN 
		[Account].[Amount_A] = [Account].[Amount1] + [Account].[Amount2];
ELSE
		[Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount3];
END IF;

End Scope;

Scope(
[Scenario].[Budget], 
[Version].[V1], 
[Year].[2022],
Base([Period].[TotalPeriod])
IDescendant([Entity].[Total])
);
// 根据Entity维度的父级做判断
IF [Entity].CurrentMember.Parent IS [Entity].[Region01]
THEN 
[Account].[Amount_A] = [Account].[Amount1] + [Account].[Amount2];
ELSEIF [Entity].CurrentMember.Parent IS [Entity].[Region02]
THEN
[Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount3];
ELSE
[Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount2];
END IF;

End Scope;

Scope(
[Scenario].[Budget], 
[Version].[V1], 
[Year].[2022],
Base([Period].[TotalPeriod])
IDescendant([Entity].[Total])
);
// 注意判断优先级
IF ([Entity].CurrentMember.Parent IS [Entity].[Region01] AND 
[Entity].CurrentMember.Properties("ud1") = "Shanghai")
OR
([Entity].CurrentMember.Parent IS [Entity].[Region02] AND 
[Entity].CurrentMember.Properties("ud1") = "BeiJing")
THEN 
[Account].[Amount_A] = [Account].[Amount1] + [Account].[Amount2];
ELSEIF [Entity].CurrentMember.Parent IS [Entity].[Region02]
THEN
[Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount3];
ELSE
[Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount2];
END IF;

End Scope;

Scope(
[Scenario].[Budget], 
[Version].[V1], 
[Year].[2022],
Base([Period].[TotalPeriod])
IDescendant([Entity].[Total])
);
// 注意判断优先级
IF [Entity].CurrentMember.Parent IS [Entity].[Region01] THEN
  IF [Entity].CurrentMember.Properties("ud1") = "Shanghai") THEN
 	 [Account].[Amount_A] = [Account].[Amount1] + [Account].[Amount2];
  ELSE
 	 [Account].[Amount_A] = [Account].[Amount1] + [Account].[Amount3];
	END IF;
ELSEIF [Entity].CurrentMember.Parent IS [Entity].[Region02] THEN
  IF [Entity].CurrentMember.Properties("ud1") = "BeiJing") THEN
 	 [Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount2];
  ELSE
 	 [Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount3];
	END IF;
END IF;

End Scope;

IF 
		$Scenario="Forcast1_11" 
THEN 
    Scope(
    StrToMember('Scenario',$Scenario), // Scenario维度成员从表单pov获取
    [Version].[V1], 
    [Year].[2022],
    {[Period].[2],[Period].[3],[Period].[4],[Period].[5],[Period].[6],[Period].[7],[Period].[8],[Period].[9],[Period].[10],[Period].[11],[Period].[12]},
    IDescendant([Entity].[Total])
    );
    [Account].[Total_Sales]  = [Account].[Volume]*[Account].[Price];
    End Scope;

ELSEIF 
		$Scenario="Forcast2_10"
THEN
    Scope(
    StrToMember('Scenario',$Scenario), // Scenario维度成员从表单pov获取
    [Version].[V1], 
    [Year].[2022],
    {[Period].[3],[Period].[4],[Period].[5],[Period].[6],[Period].[7],[Period].[8],[Period].[9],[Period].[10],[Period].[11],[Period].[12]},
    IDescendant([Entity].[Total])
    );
    [Account].[Total_Sales]  = [Account].[Volume]*[Account].[Price];
    End Scope;

ELSEIF 
  	$Scenario="Forcast3_9"
THEN
    Scope(
    StrToMember('Scenario',$Scenario), // Scenario维度成员从表单pov获取
    [Version].[V1], 
    [Year].[2022],
    {[Period].[4],[Period].[5],[Period].[6],[Period].[7],[Period].[8],[Period].[9],[Period].[10],[Period].[11],[Period].[12]},
    IDescendant([Entity].[Total])
    );
    [Account].[Total_Sales]  = [Account].[Volume]*[Account].[Price];
    End Scope;

...

END IF;

Scope(
[Scenario].[Budget], 
[Version].[V1], 
[Year].[2022],
// 以下是错误用法
IF 
		$Scenario="Forcast1_11" 
THEN 
  	{[Period].[2],[Period].[3],[Period].[4],[Period].[5],[Period].[6],[Period].[7],[Period].[8],[Period].[9],[Period].[10],[Period].[11],[Period].[12]}
ELSE
...
ENDIF,
IDescendant([Entity].[Total])
);
// 根据Entity维度的父级做判断
IF [Entity].CurrentMember.Parent IS [Entity].[Region01]
THEN 
[Account].[Amount_A] = [Account].[Amount1] + [Account].[Amount2]
ELSEIF [Entity].CurrentMember.Parent IS [Entity].[Region02]
THEN
[Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount3]
ELSE
[Account].[Amount_B] = [Account].[Amount1] + [Account].[Amount2]
END IF;

End Scope;

Scope(
[Scenario].[Budget], 
[Version].[V1], 
[Year].[2022],
Base([Period].[TotalPeriod])
IDescendant([Entity].[Total])
);

// 以下是错误用法
[Account].[Amount_A] = 
IF [Entity].CurrentMember.Parent IS [Entity].[Region01]
THEN 
 [Account].[Amount1] + [Account].[Amount2]
ELSE
 [Account].[Amount1] + [Account].[Amount2]
END IF;

End Scope;