SAS OLAP Calculated Member Infinite Recursion Error

HorneC · Posted 05-02-2019 03:54 PM

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";