Skip to Content
0
Former Member
Dec 16, 2013 at 10:09 AM

Infoset Totalling Issue

16 Views

Hi All,

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:

InfoCube A

Charactistic1 valueA

abc100 10

abc100 0 (duplicate historic record)

abc101 15

abc102 5

abc103 10

The second InfoCube holds a key figure we want to report on against the characteristic from the first cube.

InfoCube B

Charactistic1 valueB

abc100 800

abc101 900

abc102 500

abc103 400

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.