Hi Bob,
I have a short question.
It’s simple .
So i Have a table with 2 columns
Vehicle | Final
a 1
a
b 2
b
I want to make a filter to see just vehicle who has 1 or 2 in the final column.
I’ve tried to make a filter on vehicle like (= =1 ) but it says that cannot use aggregate’s in complex filters.
Thank you in advance.
Andrei Mircescu
masterg (BOB member since 2010-02-19)
See if this FAQ helps you.
By the way, please post how you’re arriving at
Jansi 
(BOB member since 2008-05-12)
Keep a report level filter such as
Final in list 1, 2
amsmi (BOB member since 2006-09-08)
hi,
i don’t understand what u mean by report level filter. Can you be more explicit ?
Final variable is numeric and has this function
Final=If( < CurrentDate() And >CurrentDate()) Then 1 Else If(IsDate()) Then 2
=Date object
=Date object
=Date object
I can’t do inlist (1,2) because it told me “cannot use aggregates in complex filters”.
Do you have other suggestions ?
thank you,
Andrei Mircescu
masterg (BOB member since 2010-02-19)
Web i or deski?
In webI,You can find a report filter on the tool bar…
when you select that, you will be able to drag and drop the field on which you want to filter… In this case, drag and drop the Final object, Then you will be able to specify the condition you want to set… In your case, select In list from the operatot drop down and specify the values you want
amsmi (BOB member since 2006-09-08)
hi,
It’s deski and i can’t put a filter on variable because it’s numeric and it don’t appear in my variable that i can filter.
Thank you for your fast response.
masterg (BOB member since 2010-02-19)
In deski, just right click on your table and then select the option Format Filter.In that you can select the measure and then select the vlues you want to see
amsmi (BOB member since 2006-09-08)
I am not sure if this will work for you but I had a similar issue way back when I was into deski, but you can give it a try -
Edit your variable. Keep it really simple for the time being like ‘=’. Now create your filter using the variable and apply it on the table. Again, edit to put in your original formula.
Let us know if this works.
vinod_menon (BOB member since 2007-04-09)
hi,
I can see your point but my final variable is numeric and i can’t put a filter on it .
To try to make it a string ? or a object?
Some ideea?
Thank you ,
Andrei
masterg (BOB member since 2010-02-19)
You can filter on a measure object too…
let me give you an example:
select ‘1’ A,20 B from dual
union
select ‘2’,40 from dual
union
select ‘3’,20 from dual
this is my source for the report…
Table in report is like this :
A B
1 20
2 40
3 20
I want to apply a filter in B (which is a measure) so that I get records whcih has only value as 20 (that is 1 and 3)
I now use A and B objects in the report. I create a table and right click o Format report. Now I add B and then define the condition in it:
Condition : ==20
Now the result set is
A B
1 20
3 20
amsmi (BOB member since 2006-09-08)
hi all ,
Thank you for your fastest response.
@vinodmn_00 :
I want to filter a column based on another column.
I’ve tried your way but it dosen’t work. The same error with aggreate function.
@amsmi :
can you be more specific. I want to make a report level filter and what are you saying there( correct me if i’m wrong) it’s an sql filter.
It’s a litlle bit frustrating because, in my opinion , this is a small problem but i’m stuck with it.
Thank you all.
Andrei
masterg (BOB member since 2010-02-19)
hey…this is not a SQL filter…
I have specified very well that you apply it on the table in the report…
Click on the table and select “Format Report”
amsmi (BOB member since 2006-09-08)
Hi,
I can’t filter on measures. I tried your way but i cannot figure it out.
Maybe it’s something that i’m missing right here.
So, i think i need to do it otherway.
The problem is like this.
Brainstorming session:)
I have a table with all of this columns:
Vehicle|| PickupDate ||DeliverDate
A--------12-03-2010 10:39---------14-03-2010 18:26
A--------13-03-2010 11:49---------15-03-2010 17:03
A--------17-03-2010 12:50---------19-03-2010 21:55
B--------09-03-2010 04:21---------11-03-2010 19:33
B--------10-03-2010 13:33---------15-03-2010 20:00
The problem sounds like this:
I want me to show the trucks who are in a trip right now and if they are not, the last trip they made( the closest trip to the CurrentDate())
Example:
If i want to open the report today:
it will show me :
A--------17-03-2010 12:50---------19-03-2010 21:55 (because it’s in a trip right now)
B--------10-03-2010 13:33---------15-03-2010 20:00 (the last trip they made, closest trip to the CurrentDate())
Have any ideeas ?
Thank you and have a nice day,
Andrei Mircescu
masterg (BOB member since 2010-02-19)
do this at query level:
check this out:
Select tab.*, Case when (sysdate-d2) =(min(sysdate-d2) over(partition by vehicle)) then 1 else 0 end mins from
(Select ‘A’ vehicle, sysdate-6 d1, sysdate-2 d2 from dual
union
Select ‘A’ vehicle, sysdate-5 d1, sysdate-3 d2 from dual
union
Select ‘A’ vehicle, sysdate-1 d1, sysdate+1 d2 from dual
union
Select ‘B’ vehicle, sysdate-11 d1, sysdate-9 d2 from dual
union
Select ‘B’ vehicle, sysdate-10 d1, sysdate-5 d2 from dual
) tab
Now in the report apply filter where Mins =1…
This works…I tried it here
amsmi (BOB member since 2006-09-08)
thank you amsmi for your reply. I appreciate that you try to resolve my problem.
But that was an example . I have lots of vehicles, that are updated and deleted from database every day. So, hard coding it’s not an option.
if you have others ideea that are generic, they are welcomed.Thank you.
masterg (BOB member since 2010-02-19)
this was just hard coded for my example… You don have any hard coding… its just a chkck to find the date that is close to the current date.
amsmi (BOB member since 2006-09-08)