BPC 7.5 Periodic and QTD issue on YTD Application

[former_member680709]

All,

Our BPC 7.5 SP 6 YTD BPC application has an issue when running EVDRE reports using Periodic or QTD measures.  YTD EVDRE reports produce correct results.  The Periodic and QTD measures work just fine as long as there is data in every period but as soon as no data is found in a subsequent period the system can not do the calculation.   It does not know how to take 'nothing' and then subtract a prior period value.  It somehow needs to treat the non existance of a record as 0 but it does not do that.  For example if the YTD May balance was 100 and YTD June was 500, if you request the periodic value for June it will take 500 - 100 and give you a 400 result which is correct.  In my second example, let's say YTD May balance was 100 but the balance went to 0 in June.  BPC does not load 0 amount so there is no record in June.  If you run a report requesting the June periodic value you will get no result as it can not handle the situation where there is no record in June.

A while ago I worked with SAP on this and was advised to install OSS note 1405993 which FIXED that problem.  Great news until at a later date our periodic reports stopped finishing and would give the message 'EVDRE encountered an error reading data from the web server'.  It would retry in 30 seconds and error over and over never finishing.  I again reported this to SAP who gave me more efficient Periodic and QTD formulas via note 1505778.  I installed the more efficient formulas and the EVDREs now finished but it brought back the original problem where Periodic/QTD reports would produce incorrect results if there was not data in all periods.  I again reported this to SAP without success.  I have been advised to do the calculation myself in the front end (e.g.EXCEL) which just seems unaccepable.

I have found another thread http://scn.sap.com/thread/3203570 <http://scn.sap.com/thread/3203570 where this same problem occurs in BPC 10.0 - very sad to hear that!  In that thread people have talked about using 'custom' formulas and say they work.  The formulas I have in place are the ones found in note 1505778 to calculate periodic and QTD.  Yes, I created the formulas for the Periodic and QTD measures but I did it based on direction from SAP.  Is that what everyone refers to as a 'custom' measure?  Does anyone have other formulas where they somehow tell the system how to handle the issue if there is no data found in a subsequent period when trying to run periodic or QTD reports on a YTD system?  If so I would be most grateful if you could share them or any other details you have on the subject.  Thank you.

Thank you,

Vicki Shrontz

[former_member186338]

Hi Vicki,

It's slightly incorrect to name PERIODIC measure result in YTD cube as wrong. BPC assumes record with zero and no record as equivalents. If you have no record in some month then the negative YTD amount of the previous month will be shown as PERIODIC result:

Vadim

[former_member680709]

Hi Vadim,

I appreciate your reply and I agree with the results you show.  However, our system does not produce these same results.  Here is my example.  Row 19 is the problem.  There is a YTD June balance but no YTD July balance.  The periodic amount in column P produces nothing when it should produce a positive 749.00.  All other periodic calculations work properly. Row 20 has no June YTD balance but does have July YTD balance.  Row 21 shows both months having a YTD balance.

BPC is not treating the non existance of a record as a 0 in our system as demonstrated by row 19 above.

Thank you for any advice you can offer.

Vicki Shrontz

[former_member186338]

Hi Vicki,

I will try to reproduce the same in my system tomorrow. Can you provide the report for P0XQ in the same format as my previous post?

And by the way, BPC 7.5 SP 6 is terribly old! Please upgrade to the recent SP, may be the issue is solved!

Vadim

[former_member680709]

Hi Vadim,

We do realize that BPC 7.5 SP6 is very old but there are multiple reasons why we have not installed more current SPs or moved to BPC 10.0.  However, as I reported in my original post, I have found a thread where people using 10.0 have reported the same issue.

I ran the report again using a more similar layout to what you gave me.  I provided you with both P0XQ which has the issue and P0DL which has values in all periods so does not have the issue.  I have also given you the QTD as well as periodic.  Both have the same problem.

Thank you for any help you can give.

Vicki Shrontz

[former_member186338]

Hi Vicki,

I have reproduced the same figures in my BPC 7.5 SP13 system:

No issues

Vadim

[former_member680709]

Hi Vadim,

I really appreciate your help on this.  I have to say that I'm not surprised with your test reaults.  I am just at a loss to figure out what is going on for us.  As mentioned in my original post, we had this problem and installed oss note 1405993 which fixed it but when the EVDRE reports stopped finishing SAP advised us to install the more efficient Periodic and QTD formulas via note 1505778.  This broke the calculation again.  I have verified via SNOTE that 1405993 is still installed.  Would you be willing to share the Periodic and QTD formulas you are using in your system for me to compare to ours?

I am very stumped on how to proceed from this point.  I realize you are on a more current service pack but for whatever reason my gut tells me there is something else going on because it did work and only broke again when the new formulas were installed.  The problem is without the new formulas I can't get the EVDRE's to even finish.  There is no best option here.

Again, I really appreciate your test help and advice.

Vicki

[former_member186338]

Easy, just remember that my TIME dimension name is PERIODS and my ACCOUNT dimension name is INACCT:

For YTD cube (in my sample) we have 3 measures:

YTD:

MEMBER [MEASURES].[YTD] AS 'IIF(([%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIODS%].[LEVEL02])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIODS%].[LEVEL02])))' SOLVE_ORDER=3

PERIODIC:

MEMBER [MEASURES].[YTD] AS 'IIF(([%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIODS%].[LEVEL02])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIODS%].[LEVEL02])))' SOLVE_ORDER=3

MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR  [%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1"  OR [%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%PERIODS%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

QTD:

MEMBER [MEASURES].[YTD] AS 'IIF(([%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIODS%].[LEVEL02])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%PERIODS%].[LEVEL02])))' SOLVE_ORDER=3

MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR  [%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1"  OR [%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%PERIODS%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

MEMBER [MEASURES].[QTD] AS 'IIF(([%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%INACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP"),IIF([%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="QUARTER",[MEASURES].[PERIODIC],IIF([%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="MONTH" OR [%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="WEEK" OR [%PERIODS%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="DAY",SUM(PERIODSTODATE([%PERIODS%].CURRENTMEMBER.PARENT.LEVEL, [%PERIODS%].CURRENTMEMBER),[MEASURES].[PERIODIC]),NULL)),[MEASURES].[YTD])' SOLVE_ORDER = 3

B.R. Vadim

[former_member186338]

P.S. And I am not sure that the issues like this can be solved by applying some specific notes. It's better to upgrade to some recent SP!

Vadim