Model Ytd Report periodic error

[michele_medaglia5]

Hola BPC Friends

I don't know what happened in DEV, I have the following problem regarding my Ytd Model:

if I run a ytd report is ok, but if I change the measure to periodic I got the following error, in Quality it is ok

[former_member186338]

Sorry, but what about pressing details button for the error?

Do you really think that it's possible to help without any info about the error?

Also - all versions/sp info...

[michele_medaglia5]

it is strange because it immediately give me an error.

I have BPC 10 NW CPM 801 SP 11, bw 731 sp 16

Thanks

[former_member186338]

And details button???

[michele_medaglia5]

it says to contact the administrator

[michele_medaglia5]

the error is the following

code: UJO_READ_EXCEPTION_018

severity: error

description: MDX statement error: Syntax error: Syntax Error : ... PARENTH1].[LEVEL01])))' SOLVE_, row 378, item: 378

log id: 051Ma3NS7kQnlLmhJYd99m

DATAVALUE:Syntax error: Syntax Error : ... PARENTH1].[LEVEL01])))' SOLVE_, row 378, item: 378

MDX: WITHMEMBER [Measures].[YTD] AS 'IIF(([/CPMB/G3DFBM3PARENTH1].[2/CPMB/ACCTYPE]="INC" OR [/CPMB/G3DFBM3PARENTH1].[2/CPMB/ACCTYPE]="LEQ"),-([Measures].[/CPMB/SDATA], CLOSINGPERIOD([/CPMB/G3D094PPARENTH1].[LEVEL01])), ([Measures].[/CPMB/SDATA], CLOSINGPERIOD([/CPMB/G3D094PPARENTH1].[LEVEL01])))' SOLVE_ORDER=3##MEMBER [Measures].[PERIODIC] AS 'IIF(([/CPMB/G3DFBM3PARENTH1].[2/CPMB/ACCTYPE]="INC" OR [/CPMB/G3DFBM3PARENTH1].[2/CPMB/ACCTYPE]="EXP") AND NOT ([/CPMB/G3D094PPARENTH1].[2/CPMB/PERIOD]="TOTAL" OR[/CPMB/G3D094PPARENTH1].[2/CPMB/PERIOD]="Q1"OR [/CPMB/G3D094PPARENTH1].[2/CPMB/PERIOD]="MAY" ), [Measures].[YTD]-([Measures].[YTD],[/CPMB/G3D094PPARENTH1].LAG(1)), [Measures].[YTD])' SOLVE_ORDER=3SELECT NON EMPTY {[/CPMB/G3D094PPARENTH1].[FY2016.TOTAL/CPMB/G3D094P] ,[/CPMB/G3D094PPARENTH1].[FY2016.001] ,[/CPMB/G3D094PPARENTH1].[FY2016.002] ,[/CPMB/G3D094PPARENTH1].[FY2016.003] ,[/CPMB/G3D094PPARENTH1].[FY2016.004] ,[/CPMB/G3D094PPARENTH1].[FY2016.005] ,[/CPMB/G3D094PPARENTH1].[FY2016.006] ,[/CPMB/G3D094PPARENTH1].[FY2016.007] ,[/CPMB/G3D094PPARENTH1].[FY2016.008] ,[/CPMB/G3D094PPARENTH1].[FY2016.009] ,[/CPMB/G3D094PPARENTH1].[FY2016.010] ,[/CPMB/G3D094PPARENTH1].[FY2016.011] ,[/CPMB/G3D094PPARENTH1].[FY2016.012] } ON 0from[$/CPMB/G3MS9NK]WHERE ( [Measures].[PERIODIC] ,[/CPMB/G3DSW6W].[ACTUAL] ,[/CPMB/G3D3MOOPARENTH1].[ALL_CHANNELS/CPMB/G3D3MOO] ,[/CPMB/G3DWVYFPARENTH1].[ALL_COSTCATS/CPMB/G3DWVYF] ,[/CPMB/G3DXF9PPARENTH1].[T_COMP/CPMB/G3DXF9P] ,[/CPMB/G3DPKB4PARENTH1].[CUP_M/CPMB/G3DPKB4] ,[/CPMB/G3DSK0QPARENTH1].[F_CLO] ,[/CPMB/G3DE6QS].[G_NONE] ,[/CPMB/G3DRFWEPARENTH1].[TOTAL_INTERCO/CPMB/G3DRFWE] ,[/CPMB/G3DZ7Q5PARENTH1].[ALL_PRODGRPS/CPMB/G3DZ7Q5] ,[/CPMB/G3DMM41].[LC] ,[/CPMB/G3DFBM3PARENTH1].[TOT_OPEX/CPMB/G3DFBM3] )

V1:Syntax error: Syntax Error : ... PARENTH1].[LEVEL0

V2:1])))' SOLVE_, row 378, item: 378

V3:

V4:

[former_member186338]

As usial, please show your TIME dimension!

[michele_medaglia5]

it is the same like in Prod

[former_member186338]

Sorry, but you need to show ALL standard TIME properties and Hierarchy column! Not custom properties!

Do you know what standard properties I am talking about?

[michele_medaglia5]

yes, here you are + the most important custom BPC_START_MONTH

[michele_medaglia5]

what do you think?

[former_member186338]

What do you mean by "2017.01 has to be Jan.2017"?

You don't need to change ID's or descriptions, only PERIOD property!

[michele_medaglia5]

thanks a lot.

so I don't need anymore the custom property BPC_START_MNTH and I can change the formula ytd - periodic (at the moment is the following)

EMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])))' SOLVE_ORDER=3##MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="MAY" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

[former_member186338]

You have to use default PERIODIC formula!

With: [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN"

[michele_medaglia5]

thanks.

So before to do that should I check all the scripts logic to see if they use the property Period?

[former_member186338]

Yes! Script logic has to be checked!

May be also reports, but the probability is low...

[michele_medaglia5]

thanks Vadim

[former_member186338]

Theoretically you can also check dimension member formulas and custom measures...

[former_member186338]

PERIOD property is incorrect - First moth of the Year has to be JAN, not MAY etc...

This property is not a month name!

If you want to have real month names - create a new property!

[former_member186338]

I have already told you how to correctly use properties of TIME dimension: https://answers.sap.com/questions/47098/index.html

What is the reason to explain again?

[michele_medaglia5]

you are right but I can take this decision alone

why in QA and Prod work?

[former_member186338]

First - fix clear issues! Then we can discuss next steps.

Why other systems work - may be will stop working after next SP or something like

[michele_medaglia5]

sorry Vadim but it will take a long time to speak with the people in Finance and in IT that have the ownership of BPC

[former_member186338]

Sorry, but you have incorrectly configured system! You have to escalate the issue as soon as possible. You can have a lot of other issues and you can end with data loss after some calculations. Do you really want to continue?

[michele_medaglia5]

it is not me. the consultant company did the project some years ago and they keep supporting the application. I have just joined 6 months ago to support the Finance team.

[former_member186338]

But what do you want??? Some magic to make incorrectly configured system working correctly? I suspect other issues if consultancy company have done this particular mistake - it's a clear sign of very low qualification 😞

[michele_medaglia5]

thanks Vadim for your precious advice.

Tomorrow morning I will work in Dev to change the dimension Time.

so you say that:

2017.01 has to be Jan.2017 but because for our company is May how do I have to set up the standard properties of the dimension time?

Thanks

[former_member186338]

All other properties are correct!

You have to change only PERIOD property:

Instead of MAY - JAN

Instead of JUN - FEB

Etc...

If you need calendar month - just create a new property like CALMONTH or CALPERIOD and fill it wth MAY,JUN etc...