Zeros in reports causing a problem

[Former Member]

Hello All,

<u><b>Scenario</b></u>

We have been getting Asterisks in the "results" row in some of the reports. The obvious cause was presence of transactions with different units. On investigation i found the key figures (columns showing the asterisks in results) had a value '0' in some rows. This value had no unit assigned either. When i looked into that particular cube in the multi-provider that was resulting in these '0' s i found that these transactions had no entries for that key figure in the cube. They were blank. There were other characteristics and key figures for the same transaction that were populated.

<b>Cause:</b>

When the query is run this key figure along with others is populated for transactions which are pulled from Cube A. Transactions are also pulled from cube B for the same keys figures and characteristics but cube B has no values for this one particular key figure and all those rows in the report are replaced by a 0 without unit.

I tried changing the query properties to "Suppress Zeros" and "Do not display Zeros" but this only results in not showing the zeros. The result still has an asterisks.

I also tried changing the "Calculate result as - from 'summation' to 'not defined'" but this is effecting the query because the query has two conditions defined and using "not defined" causes the query to ignore the conditions and give the 'overall result'. SO it is necessary that we use Summation

<b>My Question</b>: Is this the case with all queries - blank transactions in the cube showing up as '0' without units and giving an '*' for the results? If not is there any way we can change the settings or define the key figures to avoid getting these zeros and leave the blank transactions as blanks in the report too and get a proper result in the result rows?

I would really appreciate if someone could please get back to me with solutions/suggestions as soon as possible. Helpful replies will be awarded points.

Thank You.

Message was edited by:

MM

[former_member970842 · Accepted solution]

How about writing a routine, and in the Info obejct definition screen u can select the Conversion- ALPHA, and start writing a routine....

[Former Member · Accepted solution]

You can restrict that particular key figure on 0infoprovider (cube A) as Cue B don't have data for this KF.

Thanks

Tripple k

[Former Member · Accepted solution]

Did Tripple's reply answer your question?

If not, have you tried using the NODIM function in query designer? Always assuming its OK not to have the dimension displayed, of course.

Patrick

[Former Member · Accepted solution]

Tripple,

That is a very good idea ..... Unfortunately i have transactions in the same cube where the key figure is populated for certain transactions with a unit and is empty (and without unit) in some other transactions in the same cube. So if i restrict the key figure to the info provider i will lose a lot of valid transactions.

Is this 'normal'....empty cells in cubes being populated with zeros - The main problem is due to these '0' transactions the overall result and few intermediate results show a '*' which the reporting team wants to get rid of.

We all (at my workplace) are beginning to think its a bug that needs to be addressed by SAP. Any thoughts on if this really is a bug?

Thank You!

[Former Member · Accepted solution]

Patrick,

NODIM is not an option since the reports MUST have the dimension displayed for these keyfigures. I suggested NODIM (which works like a charm) but is a 'no no' in this case.

Another thing i forgot to mention is - <b>Presence of the zeros is not much of a problem in the reports. It is the overall result displayed as a<b> '*' </b>that is bothering the reporting team.

</b>

Do any of you know if there is any way i can get the overall and intermediate results to display the value and not the '*'?

Any suggestions/ response will be appreciated and rewarded.

Thank You!

[Former Member · Accepted solution]

Hi,

You can make settings in SPRO to display mixed values in reporting.

Go to SPRO

>> BW customizing Implementation Guide

>> Business Information Warehouse

>> Reporting Relevant Settings

>> Presenting the numeric value in the Business Explorer

and here tick Mixed Values.

Regards

SSS

[Former Member · Accepted solution]

Bwuser,

Changing the definition of key figure objects is not an option. We cannot afford to change the structure of the cube or definition of the keyfigures as the cube already has millions of records and has been in use for quite some time.

<b>Does anyone know if there is a way to calculate the overall result any other way and not through the 'result display setting' in the reports. SUMCT() etc don't work, They also result in a '*'.

</b>

Thank you!

[Former Member · Accepted solution]

Hi,

Is my above suggestion of any use?

Regards

SSS

[Former Member · Accepted solution]

SSS,

Excellent suggestion! Made a note to remember this.... however

Correct me if i am wrong - This setting would result in allowing mixed values in ALL the reports. I am not sure if the reporting team would allow that. Unfortunately being in the development team i am not authorized to set this and test if it works but i will definitely suggest this and see what the reporting team's response is.

<b>Quick question: Would this setting also solve the '' in the result? I understand that 'mixed currencies' setting outputs numeric value. Would absence of a unit for this numeric value (which in this case is just a '0') still result in '' for the overall result. If it does then i am back to square one.

</b>

Deserve 10pts for the suggestion...unfortunately at this time can assign only 6pts!

Thank You!

Message was edited by:

MM

[Former Member · Accepted solution]

SSS,

I just realized that currency is not the only unit in our reports thats causing this issue. We have 'work-hours' which is causing the same problem.

So mixed currency alone wouldn't solve the problem!