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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Difficult Record Selection Formula 2

Status
Not open for further replies.

lkerr

Technical User
Mar 2, 2005
162
US
Hi - I need some help with a difficult record selection formula, please.

Here's what I'm looking at: I'm running CR9 with a SQL database over a network. (If you need more information than that, let me know, I'm not too technically literate when it comes to networking.)

What I need is to create a record selection formula that will select all the Products that have been sold to both of two clients. Here's what I have so far for the record selection:

{vRDSO.ClientID} = 16 and
{vRDSO.ClientID} = 5

The way the report is set up looks like this:

GH1: {vRDSO.RegionName}
GH2: {vRDSO.Product}
Detail: {vRDSO.ClientID}, {vRDSO.ContractID}

Theoretically, I would like it to print only the products that have sold to client 16 AND client 5, and I know there are a large number (probably a hundred or more), but when I run the above record selection formula, I'm getting a null report.

What (probably very simple) thing am I missing here?

LK
 
It should be:

{vRDSO.ClientID} = 16 or
{vRDSO.ClientID} = 5

Or to simplify:

{vRDSO.ClientID} in [5, 16]

Then to display only those products that both clients have purchased, go to report->edit selection formulas->GROUP and enter:

distinctcount({vRDSO.ClientID},{vRDSO.Product}) = 2

-LB
 
Hi,
Since no single record can have BOTH 16 and 5 as a ClientID, and since CR processes data 1 record at a time, your existing formula will always fail..

You will probably need to use a Stored Procedure or Command Object ( or View) to do what you want ( something like:
Code:
Select * from table where ClientID = 16
UNION ( or UNION ALL)
select * from table where ClientID = 5

Pass the 2 values as a parameter to the SP ..



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Thank you both so much! I tried both methods, and they have both worked just fine! I appreciate your helping me and explaining just what I did wrong. Thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top