MDX Formula to get YTD Value in Script Logic

[former_member187113]

Hi,

I'm at my wits end trying to figure this one out via trial and error, the manuals, or the forums. (It's more than probable my MDX skills are not up to it.)

SAP BPC 10.0 Microsoft (10.0.17.0), SQL Server 2012 (or 2008),

Financial Model, Periodic Time Dimension.

Model Dimensions: Account, Category, Department, DataSrc, LegalEntity, RptCurrency, Time.

I want to put the YTD value for the IncomeStatement (a rollup) into a base account for any one month. I am hard-coding Dimension Members for now

The Script Logic is below. (I am using Capex001, Capex002, Capex003 as the destination. It could be any base member, really). Also NETP_AND_L has a Formula in the Member Sheet of ([ACCOUNT].[INCOMESTATEMENT],[MEASURES].[YTD])

The issue I am having is with the line in red. Is the syntax correct?

*CALCULATE_DIFFERENCE=0

*XDIM_MEMBERSET ACCOUNT = <ALL>

*XDIM_MEMBERSET CATEGORY = ACTUAL

*XDIM_MEMBERSET DEPARTMENT = <ALL>

*XDIM_MEMBERSET GRPDATASRC = <ALL>

*XDIM_MEMBERSET LEGALENTITY = GRM01,GRM02,GRM03

*XDIM_MEMBERSET RPTCURRENCY = ZAR

*XDIM_MEMBERSET TIME= 2014.AUG

*CALC_DUMMY_ORG ACCOUNT = PARENTH1

*WHEN ACCOUNT

*IS "#INCOMESTATEMENT"

  *REC(FACTOR = 1, Account=Capex001)

  *REC (EXPRESSION = ([ACCOUNT].[INCOMESTATEMENT],[MEASURES].[YTD]), ACCOUNT = "Capex002")

  *REC (EXPRESSION = [ACCOUNT].[NETP_AND_L], Account = "Capex003")

*ENDWHEN

*COMMIT

And this is a subset of the results.

ACTUAL,ZAR,2014.AUG,CAPEX001,GRM03001,INPUT,GRM03,-243.1300000000

ACTUAL,ZAR,2014.AUG,CAPEX002,GRM03001,INPUT,GRM03,0

ACTUAL,ZAR,2014.AUG,CAPEX003,GRM03001,INPUT,GRM03,0

ACTUAL,ZAR,2014.AUG,CAPEX001,GRM01005,INPUT,GRM01,-30779.8400000000

ACTUAL,ZAR,2014.AUG,CAPEX002,GRM01005,INPUT,GRM01,0

ACTUAL,ZAR,2014.AUG,CAPEX003,GRM01005,INPUT,GRM01,0

So the Periodic IncomeStatement is pulling through, but not the YTD. Which means the MDX I am specifying is probably incorrect, or something.

An ideas?

Kind Regards

Nick

[Former Member · Accepted solution]

Hi Nick, are you trying to write into "ytd" measure? what is your client requirement? a report? let me know, regards, daniel

[former_member187113 · Accepted solution]

Hi Daniel,

Thanks for coming back to me.

I eventually figured it out. It's Script Logic 101, but it's something I've never done before - direct MDX in my scripts.

I created a base account, NETP_AND_L_YTD_ZAR

So, in the LGF, I had the scoping, then

[ACCOUNT].[#NETP_AND_L_YTD_ZAR] = ([ACCOUNT].[INCOMESTATEMENT],[MEASURES].[YTD],[RPTCURRENCY].[ZAR])

and then *COMMIT

So using [ACCOUNT].[#NETP_AND_L_YTD_ZAR] is the answer. No *REC.

Regards

Nick