Difficult Record Selection Formula 2

[lkerr]

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

 

[lbass]

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

 

[Turkbear]

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:

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 ..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lkerr]

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!