BusinessObjects Board

filter on measure

building reports desktopintelligence
#1

I’v created a variable on report level:

= If (Sum(<Quantity Allocated Confirmed>)=0) ForEach <Delivery Note No SMS> Then "wrong" Else "correct"

I would like to put a filter on this variable, as I only want to show the “correct” lines.

Why does this not work? Because it’s a measure?

I did a search on the topics, I think I have to create it in the universe. Can anyone give me the code to create it in the universe?

nita (BOB member since 2005-08-16)

#2

Hi,
What is the type of the filter object measure or dimension? As you are using measure objects in the filter condition, it will by default be measure. try makign it a dimension and then make it working.

Or you can use it in other way as making it 1=Correct and 0 for wrong. (In the first variable) Make another variable as If this filter variable as 1 = then correct and 0 for wrong. So now you will see this second variable in the report, make the first variable as hidden in the report. and put fitler on this first variable.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

#3

Hi,

It is automatically defined as measure. I don’t know how to switch it to a Dimension.

Your second option is not working, still I can not put a filter on it.

Thanks for your answer anyway

nita (BOB member since 2005-08-16)

#4

Hi,
As you are creating the object on the report then you should be able to make it a dimension.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

#5

One cannot filter on a measure, one can only sort or rank on a measure.
See this Report FAQ (Frequently Asked Questions) here.

A) Report filters on measures will always work on the microcube data and not the possible aggregated data of measures within the report.

B) Try the Rank trick instead, for example create a Variable defined as:

= <Measure> > 100

Now rank your dimension against this variable and display only the Top 1.

Andreas :de: (BOB member since 2002-06-20)

#6

Not when it has an aggregate function like Sum().

The bottom line is that you cannot filter on an aggregate calculation which will be a measure by requirement. You can filter on a measure, assuming the measure comes straight from the universe without further report calculations. But once you have an aggregate on a report, you cannot filter it.

The reason is BusinessObjects does a one-pass report process. The filters are applied first and then the remaining data is aggregated based on the normal process, which is to say that measures are projected based on dimension values and block structure. Since the filters are done before the aggregation step, you simply cannot filter on an aggregate calculation.

Dave Rathbun :us: (BOB member since 2002-06-06)

#7

Andreas,

I tried your options, but the ranking option is greyed out.

Maybe a suggestion how to create the variable in the universe?

The report variable is build up like this:

= If (Sum()=0) ForEach Then “wrong” Else “correct”

nita (BOB member since 2005-08-16)

#8

You’re nearly there :slight_smile:
Change “wrong” and “correct” to 0 and 1 and then you can use that as the measure to rank against.

#9

But such filters are always applied at the lowest level of granularity held in the cube, not at higher levels of aggregation that may be shown in the report.

So, if you have a report that returned: ‘Sales Region’, ‘Sales Person’ and ‘Sales Value’ (measure), it is not possible to simply implement a filter when only ‘Sales Region’ and ‘Sales Value’ are displayed in the block. The filter will be applied at ‘Sales Person’ level, not the aggregated ‘Sales Region’ level’. (I know the ranking trick is a possibility). It doesn’t work even if you force the calculation contexts.

I cannot believe that I’ve never encountered this issue before; has such behaviour changed? (I’m using XiR2 full client)

Thanks…

anorak :uk: (BOB member since 2002-09-13)