Selection Formula with a sub Query

[putts]

How do you build a record Selection Formula that does something like a sub query.

For instance, let's say we have an Invoice database and one of the items on the invoice is the salesperson.

In my report, I want to pull any invoice that has Bob as the salesperson. Trick is, there could be multiple salesperson on any invoice. So the data structure looks something like....

TBL_INVOICES
ID | INVOICE_DATE
0 | 1/1/2005
1 | 4/8/2005

TBL_INVOICE_SALES
ID | INVOICE_ID | SALES_PERSON
0 | 0 | Bob
1 | 0 | Susie
2 | 1 | Bob
3 | 1 | Mike

So, on the report I need to see all the sales people for invoices 0 and 1 because Bob was a part of the sale.

Obviously, I can't just say WHERE TBL_INVOICE_SALES.SALES_PERSON = 'Bob' because I won't get all the records.

The query would look something like....

SELECT * FROM TBL_INVOICES WHERE ID IN (SELECT INVOICE_ID FROM TBL_INVOICE_SALES WHERE SALES_PERSON = 'Bob')

Now how do I do that in Crystal?
(Version 9.0 or 10.0)

 

[Madawc]

You could definitely do it using subreports. On the main report, just read invoice lines for Bob. For each of these, pass the invoice number to a subreport that shows all invoice lines.

Subreports make a lot of work for computers, but save time for humans. You decide which is more important for your particular circumstances.

In this case, the subreport method won't be all that inefficient, because it excludes all but a handful of invoice lines.

An alternative is a Stored Procedure - use SQL commands to gather up the data and then use Crystal to print it. That should be easy for you, since you already know SQL.

Madawc Williams (East Anglia, UK)

 

[lbass]

In CR 9 you could use Add Command as your datasource, and enter your query there.

-LB

 

[putts]

First off, thanks for the replies.

slight bummer, though, because I typically build my reports with almost no parameters in them that limit the data and then I have this ultra-dynamic-database-table-driven that eventually builds up a RSF for the report and then sends that in on the fly.
The nice thing is that if I decide I want to add a parameter for, say, a date field after the report is in production, I just insert a record into a table and !BAM! it's there without me having to change my report and upload a new version.

With what you guys are advising, it sounds like I'm going to have to have the report built up with the parameter already determined to be there.

Mind you, it's not that I CAN'T do it, it just goes against my morals