SAS OLAP Calculated Member Infinite Recursion Error

HorneC — Thu, 02 May 2019 19:54:31 GMT

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

Re: SAS OLAP Calculated Member Infinite Recursion Error

ChrisNZ — Fri, 03 May 2019 05:42:41 GMT

Have you seen this?

http://support.sas.com/kb/45/787.html

Re: SAS OLAP Calculated Member Infinite Recursion Error

HorneC — Fri, 03 May 2019 11:42:26 GMT

I did not see that. I am not sure if that is causing my problem as I am running my code in SAS EG, but I will check with my admin. Thank you!!

topic Re: SAS OLAP Calculated Member Infinite Recursion Error in SAS Programming

SAS OLAP Calculated Member Infinite Recursion Error

Re: SAS OLAP Calculated Member Infinite Recursion Error

Re: SAS OLAP Calculated Member Infinite Recursion Error