cancel
Showing results for 
Search instead for 
Did you mean: 

Infoset Totalling Issue

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (3)

Answers (3)

anshu_lilhori
Active Contributor
0 Kudos

Hi,

Exception aggregation might work in this situation.You need to identify the characteristic based on the data on which it makes the unique combination of row.

Regards,

AL

Former Member
0 Kudos

Compress the Infocube so that same key records will be aggregated and accordingly it will give the correct result at Infoset level.

Former Member
0 Kudos

Dear De Angus,

In my opinion there is no easy solution to this. The query designer just shows what is in the database. Since there are two values existing in the cube it will always show in the result set of the query.

You can get rid of the 0 values by collapsing (compressing) the infocube with the checkbox 'zero elimination' in the 'manage infocube' functionality.

Hope this helps.

Regards, Machiel