I am trying to create a calculated member in my SAS OLAP cube using some conditional logic. In summary I need the member to abide by the following logic. If the prof product is ALLPP then return the amount associated with TC, if not look to see if the prof product id rolls up to TC. If it does, return the amount associated with the current member. If not, return 0. Below is a quick summary of the prof product dimension. Parent Child #root ALLPP ALLPP TC ALLPP NP TC RC TC CC What I need to do is exclude NP from the member being returned. If there is a better way to accomplish this, I am certainly open to suggestions. Here is the code I have so far. &Cube_Name and &CM return the MDX path for where my test member should sit. Thank you in advance! /*146*/ DEFINE MEMBER "[&Cube_Name.].&CM..[Provision_Stat_Test]" AS
" (iif(([Prof_Product].CurrentMember = [Prof_Product].[All Profitability Products].[ALLPP]),
([Account].[All Account].[All_Accounts].[IS_Accounts].[NetInc].[PreTaxInc_TE].[NetIntInc_NP_TE].[Provision].[Provision_PM].[Prov_byCalc],[Prof_Product].[All Profitability Products].[ALLPP].[TC]),
(iif((Ancestor([Prof_Product].CurrentMember,[Prof_Product].[Lev02_Prof_Product]) = [Prof_Product].[All Profitability Products].[ALLPP].[TC]),
([Account].[All Account].[All_Accounts].[IS_Accounts].[NetInc].[PreTaxInc_TE].[NetIntInc_NP_TE].[Provision].[Provision_PM].[Prov_byCalc],[Prof_Product].CurrentMember),
0))
* (-1))
, SOLVE_ORDER=5";
... View more