Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Report Selection - how to choose data

Status
Not open for further replies.

HolleeC

Programmer
Apr 18, 2007
6
US
Hi guys. Sorry for the confusing explanation, but I'm a newb so here goes.
I have a crazy report that has one main table, then it has links (some outer joins) to 6 other tables, so it's a beast. I have it working semi ok with just the outer joins and links, but now have a quandary with one of the tables. This table is linked with a Left outer join to the main table. It isn't linked to any of the others. Apparently there are about 7 different user flags in this table that may or may not be selected. So I have to search that table for all the flags that may be there, and if it contains any of the ones they don't want, don't select that entire record. Is there a way to do this in the report selection criteria or do I need a subreport?
Thank you!
 
Hi,
Try
Code:
NOT {Table.UserFlag} in [[i]'LIST','OF','UNWANTED','FLAGS'[/i]]

to eliminate any record that has one of those flags..

It will not eliminate the matching records from the table it is linked to because of the join type..If you want no record from the main table that matches the userflag containing one, try using a equi-join to that table.

[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Hi Turkbear. Thanks so much. I don't think it works, because I haven't explained properly. I tried the equijoin and also the Not In statements, but the problem is that I have to evaluate whether or not there is a flag after I've returned the data.
Here's a sample of the report:

Asset Make/Model Flag Exists?
123 Hydrometer MetCal False
123 Hydrometer Vendor True
123 Hydrometer PostTest True

Now the problem is - It's ok for these flags to be false, but if any of them are true, then the entire asset group shouldn't even show up on the report. I get the Flags from another table, and they are tied to the main table by asset id.
I think I'm missing the perfect answer somewhere - hopefully it's possible to do this ;)
 
Actually, Turkbear, if you select on a left outer joined table it effectively undoes the left join (unless you add the selection to the from clause in a command), so there should be no selection on that table.

Create a formula {@hastrueflag} in the field explorer:

if {table.flag} in ["Vendor","PostTest"] then 1

Then go to report->selection formula->GROUP and enter:

sum({@hastrueflag},{table.assetgroup}) = 0

-LB
 
Hi,
Thanks, LB, I always seem to forget that about Left-Outer joins ( And I should know better after over 20 yrs developing with Oracle databases - and I have even posted info here about that behavior!!)








[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Thanks so much lbass - that worked! I had to modify it a little bit because I had to modify hastrue flag to
if {table.exists} then 1. Now the assets with any true flags definitely don't show up.
You are a genius!
There's still a little to do though because there are duplicates (for all the possible flags) but I didn't want to be pushy asking for more help.
But just in case you were bored... how do I eliminate all but one of the False flag assets?

Asset ID Make/Model Flag Exists?
123 Amplifier MetCal False
123 Amplifier Vendor False
123 Amplifier Rad False
123 Amplifier Stock False

I just need one of these suckers to show on the report.

 
Hi guys, never mind I got it going! YAY
I just put the duplicates in another group and then it filtered it out for me. Thanks so much!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top