We have a InfoSet created to join two InfoCubes based on a shared key. The first InfoCube however has some duplicate data in it which can only be identified by a zero value in one of the key figures.
Below is a very simplified version of our issue which hopefully distils the problem down to the basic level but provides enough detail for you to understand.
So for example:
abc100 0 (duplicate historic record)
The second InfoCube holds a key figure we want to report on against the characteristic from the first cube.
So when this data is joined in the InfoSet we get
Charactistic1 valueA valueB
abc100 10 800
abc100 0 800
abc101 15 900
abc102 5 500
abc103 10 400
The problem here is that value B is doubling up as it appears against both entries for abc100. We actually don’t want to see the value in the 2nd line above.
So we created a formula as follows: (valueA > 0) * valueB
That then gives us the following
Charactistic1 valueA valueB formula
abc100 10 800 800
abc100 0 800 0
abc101 15 900 900
abc102 5 500 500
abc103 10 400 400
So that works perfectly as long as we have characteristic 1 in the report. As soon as it is removed (there are other characteristics in the InfoSet and this is a great simplified example just to show the issue) the formula then doesn’t quite work as we want:
valueA valueB formula
10 1600 1600
15 900 900
5 500 500
10 400 400
As you can see as we are now looking at a summarised level when valueA is no longer zero and valueB is totalled to 1600 the formula returns 1600.
The effect of this in the actual report where we only show the formula key figure is that the total jumps around all over the place
depending on what is added or removed from the report. Result, users are confused as to what the correct value is and the report is unusable.
So, question is . . .
Is it possible via just the query designer to force to formula to evaluate at the correct drilldown level when applying the valueA > 0 test?
If not, how can we achieve the results we need?
There is unfortunately no characteristic we can link into to identify these extra records from InfoCube A, only the key figure.
Do we therefore need to build something into the InfoCube itself to make this work?
Any advice or thoughts would be much appreciated.