Zeros in reports causing a problem
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.
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.
Message was edited by: