Multiple Parameters in Record Selection

[FoxGolfer]

I have a CR10 report (SQL Server tables)that needs to report on managers with goals, without goals or all managers. The goals table is linked to the emp table by the EmpID with a left outer join.
I have determined that if I have EmpID = EmpID I get all managers with goals. (Makes sense so far.) If I change the record selection to EmpId <> EmpID I get all managers without goals.(Still good.) I need to have a parameter to select all, all with goals or all with no goals.
Can I and how do I do this with parameters? BTW, there are two other expressions in the record selection that do not change. E.g., F1 < 103 and F2 in [abc,def,ghi].
TIA,
T

 

[lbass]

Create a parameter with options "All", "No Goals" and "All with Goals" and set up your record selection formula like:

if {?Parm} = "All" then true else
if {?Parm} = "No Goals" then
isnull({goals.empID}) else
if {?Parm} = "All with Goals" then
not isnull({goals.empID})

-LB

 

[synapsevampire]

Qualify your tables and fields used in the record selection, it's critical for LEFT OUTER operations.

Sounds like you're new to databases and Crystal, so it's best to post example data and the expected output as well.

If F1 < 103 and F2 in [abc,def,ghi] is on the goals table it will overide the LEFT OUTER. Assuming this is NOT the case, try a parameter with your choices and adjust the record selection formula to:

(
if {?Choice} = "Goals" then
not(isnull({goals.empid}))
else
if {?Choice} = "No Goals" then
isnull({goals.empid})
)
and
(
{managers.F1} < 103
)
and
(
{managers.F2} in ["abc","def","ghi"]
)

So in the instance of {?Choice} equalling anything but Goals or No Goals nothing is passed, so you get everything, and if it's goals, you get only those that have goals, and is the instance of no goals you get those with null emp_id in the goals table.

Again, please flesh out your posts in the future when referencing fields, we don't know which tables contain which fields, and it sounds as though you aren't aware of of how LEFT OUTER joins work, as any child table criteria will be observed so the LEFT OUTER is overridden by it.

-k