BusinessObjects Board

Block Filter using a calculated dimension

building reports webintelligence legacy 1/2
#1

This cannot be difficult to achieve, but its not working.

My Webi query includes (amongst other dimensions) two dimensions - A & B, which are both numbers.

In the report, I have then created a new dimension/column ©, which is equal to A minus B.

I have then tried to create a block filter to only select rows where C is NOT equal to zero.

But while some rows are now being ignored, I am still getting rows which have a zero value in Dimension C.

I have tried writing the filter in various ways, but still get these rows. If I write block filters on the standard dimensions, these seem to work, but block filters on this computed dimensions doesnt seem to work at all.

Any advice gratefully received, as I have tried plenty of different things.

Browner_ (BOB member since 2008-01-18)

#2

I tried to replicate the same scenario and the filter on calculated dimension C worked perfectly by suppressing the zero value rows

Do you have any null values in the Report?

DWH (BOB member since 2006-04-25)

#3

Thanks.

Firstly, this is a Webi XI query, so I’m in in the wrong forum. If I cant resolve this I will move the query to the other forum.

My block filter is still returning rows I would not expect, some with 0 in them , and some with -0.

To enlarge on the problem:

  • Dimension A does not have an null values in it .

  • Measure B is a “Sum” command. This is returning some null values.

  • The block filter is selecting only rows where Measure C is not equal to zero.

  • Measure C is set to Dimension A minus Measure B. I’m getting quite a few rows where the result of Measure C is “-0”, which makes no sense to me (eg 698.40 minus 698.40 should be 0, not -0) . Measure C is also showing some values of 0, but you would expect these to be removed by the block filter. Also , when I export the results to MSExcel, some of these 0 values in Measure C (but not all of them) show as something like “7.105427357601E-15”.

None of this makes much sense to me, all help gratefully received.

Browner_ (BOB member since 2008-01-18)

#4

P.S.

If I re-write the block filter to exclude rows between 0.01 and -0.01, then everything works exactly as you would expect.

So something is very strange about the way that 0 is being calculated and/or interpreted.

Browner_ (BOB member since 2008-01-18)

#5

Seems like this has to do with the build up of the objects in the universe or database. Try making objects which only are precise to two decimals.

you can test is by enlarging the number of decimals of A and B in your report, then you will see the difference.

You could also use the formula floor (on your report) to cut the number of decimals.

HPE (BOB member since 2006-10-03)