Filter

building reports desktopintelligence
1

Hey Guru out there. I will be happy if some one can help me with this problem.

Issue:

I am trying to return values for a set of numbers but dont want to apply filter (Happy to use Defined fileter ) within the report as the value will continue to change depending on the time of the day. For example
Scenario1
6
7
8
9
10
I only want to resturn the values for between 6 and 8 but exclude 9 and 10

Scenario2
6
7
8
9
10
11
12

I only want to resturn the values for between 6 and 8 but exclude 11 and 12

Basically don’t return the last 2 values every time.
Thanjs

ondo33 (BOB member since 2006-11-09)

2

Create a variable like:

=If (<st> < (Max(<st>)In Block - 2)) Then "Y" Else "N"

Then apply a filter on the variable when it has a value “Y”

joepeters :us: (BOB member since 2002-08-29)

3

Thanks so much - It did work but I cannot apply filter to it. For whatever reason the filter greyed out.

ondo33 (BOB member since 2006-11-09)

4

Did you try filtering out at the data provider level itself?

Jansi :india: (BOB member since 2008-05-12)

5

No. The filter was applied wihin the report. I also tried to use ranking but it greyeed out as well.

Can you explain how to implement this within the data provider. Thanks

ondo33 (BOB member since 2006-11-09)

6

Not sure what you mean by the filter is greyed out. Just go to Format->Filter and create a new filter.

joepeters :us: (BOB member since 2002-08-29)

7

In essense you cannot create filter on Calculated fields.

ondo33 (BOB member since 2006-11-09)

8

You can very much apply filter on calculated or derived objects, but since the above expression has max function the filter is greyed out (as aggregates are not allowed in filters)

You can however see the possiblity of using subqueries in query panel to exclude those values

.

haider :es: (BOB member since 2005-07-18)

9

I’m sorry that you can filter out only the maximum value at the data provider level, by including a calculation in your condition. I don’t find any other way to filter out second maximum at the data provider level. :frowning:

This FAQ also talks about using sub queries, as Hyder suggested.

Jansi :india: (BOB member since 2008-05-12)

10

Thanks - What will the sub query looks like?

ondo33 (BOB member since 2006-11-09)

11

Hi Ondo,

Please try the following.
I found this when i tried for the following issue.

Prompt Based Ranking

First create a variable
v_max=()In Block - 2

now go to
Format filter=>
click add
select the object
click OK
now go to define
and write the following formula.
=<<v_max>
now click ok and apply.

Now we have our trick

go to the first variable
make it as the following

v_max=[b]max/bIn Block - 2.

(if we do this early BO will show error
Cannot use aggregates in complex filters).

Please let us know if this is working.

DoSTh :india: (BOB member since 2007-05-31)

12

Thanks - It worked

ondo33 (BOB member since 2006-11-09)