Inconsistency in updating value of EVGTS/EVSND in multiple Applications

[Former Member]

Dear all:

Situation

Because of the complexity of my financial reports, instead of using EVDRE or modified Dynamic Template, I requested the users to provide traditional Excel Workbooks (with Excel formulas) and embedded EV functions (primarily EVGTS and EVSND) to the Workbooks.

However, from time to time my EVGTS and EVSND wouldn't retrieve correct information

Data

The worbook consist_s of:_

Finance Application

Balance Sheet

P&L

Expenses

Sales Application

Sales Results

Inventory Flows

For all the EVGTS and EVSND functions, I filled in all dimension members so it properly retrieves data regardless which application it is under the Current View

Sometimes my EVGTS would show #RFR after numerous Expand or Refresh attempts. My EVSND usually would show "0" when it failed to retrieve the values. However, if I opened a new workbook and use EVSND and EVGTS with the same parameters, the correct values were displayed.

Question

Is this because the size of my report is too huge? Each worksheet contains average of 400-500 EVGTS and some EVSND and I have 5 worksheets in this Workbook.

[Former Member · Accepted solution]

Hi,

We were also facing similar issue because of using EVGTS and EVSND.(around 20)

Try designing your IS with two EVDRE() instead of using EVSND.

Also this problem occurs if you are using lots of Macro codes.

Ankita

[Former Member]

Use multiple EVDRE's and if not feasible have at least one EVDRE per application and use the PageKeyRanges as reference in your EVGTS/EVSND rather than "hard coding" all the dimensions in the EVGTS/EVSND.

[Former Member]

Ankita / Meznet:

Unfortunately, designing EVDRE will not be practical. For instance, After the expansion all the formulas residing in local worksheet cells are distorted. Also, one of the major reports has 12 months Balance Sheet appending each other (Don't ask me why, but that's the way some genius set up the report), and EVDRE didn't work properly at all...

I realized that even for dynamic template it uses EVGTS and EVSND, so I think my approach is suitable for the business processes that we have now. However, there must be something like Cache Overflow that can be refreshed to make EVGTS and EVSND work properly....

Thanks! Have a great long weekend!

Sincerely,

Brian

[Former Member]

Hi Brian,

I am facing exactly the same problem and really must make it work. I have a schedule in 7.0MS mixing one EvDRE with some EvSND/EvGTS. It was working ok until at some point it started to show zeros (only the EvSND/EvGTS, EvDRE is doing fine). I have tripled checked and the data is there. The format of the schedule is so complex that I cannot substitute it all with EvDREs.

Did you find a solution or workaround please?

Regards,

Rafael

[Former Member]

Rafael:

Have you checked that you have included all the members for your EVSND / EVGTS? functions? I would include all members for those two functions. That solves the problem for "most" of my reports except one, where it still shows zero.

I haven't done so but I think I will optimize my application and reindex and see if it helps...

Brian

[Former Member]

Guys:

Good News! I have found the solution to the EVGTS / EVSND function problem!

After working through with the SAP Support, we learned that from time to time some cell(s) of Excel stay "corrupted" for unknown reason. For those of you who set up the Rows and Columns Keys with cell-linking, when these cells are corrupted, even though they appear the right value (in Excel) for the key to be used in your EVGTS / EVSND, BPC would not be able to pass on those parameters to the functions consistently. That is the reason many of us will have EVGTS displayed zero when there exist values.

EXAMPLE

For instance, I have a EVGTS function = EVGTS("FINANCE",B1,2008.12), where B1 is the key I want to use for Category.

Let's say for cell B1, I have the formula B1 = A1. And A1 has the value "ACTUAL". my EVGTS("FINANCE",B1,2008.12) should give me the value with ACTUAL at Dec 2008 and the rest of the Current View.

If cell B1 is corrupted, EVGTS will return 0.

SOLUTION

The hotfix is to manually type in the key instead of using cell-linking. Or you can try to recreate everything from scratch in a "new" worksheet/workbook. However, sometimes it is not just the worksheet but the entire workbook that goes corrupted. Please note that COPY AND PASTE your settings will bring the corrupted cell(s) to the new worksheet so it will not solve the problem.

This is the solution to my problem, where I discovered that the cell that refers to the "scale" of EVGTS was corrupted. After I manually typed in the scale value, the report displayed the correct amount.

Hope this helps!

Brian