Crystal Reports 7 Parameter Question

[kdecker]

I need to include a parameter as part of a LEFT OUTER JOIN selection criteria, DIRECTLY in the Crystal Report SQL area (can not use query file)... How would I do that?

eg:

Select f_name
From employee e LEFT OUTER JOIN user u ON
(e.empl_id = u.empl_id AND
u.lst_mod_dt = '2003-08-26')
WHERE e.empl_id = 'SOME USER'

 

[synapsevampire]

I can't recall if CR 7 allowed for FROM WHERE and order by TO BE CHANGED DIRECTLY (check under Database->Show SQL Query).

I would either use something akin to the following which can be constructed via the record selection formula:

Select f_name
From employee e LEFT OUTER JOIN user u ON
(e.empl_id = u.empl_id AND)
WHERE e.empl_id = 'SOME USER'
and
u.lst_mod_dt = '2003-08-26'

In which case you won't get a Left outer join as you are explicitly stating the child criteria that must be met.

I fear that in In CR 7 you'll need to create a View to accomplish this.

In CR 8/8.5 you might use ADO SQL, and in CR 9 and up you can enter SQL directly.

Consider upgrading too.

-k

 

[rhinok]

Based on your SQL Example, I'm guessing that you want the following:[ol][li]All employee records (dataset potentially defined by a parameter)

[/li][li]Only those matching User Records that were modified yesterday[/li][/ol]Is this correct? Are both User and Date defined as parameters? If so, then you might be able to accomplish what you want through conditional suppression:[ol][li]Left Outer Join the two tables on E.User ID only

[/li][li]Create your Record Selection Criteria:

{e.empl_id} = {?User}

This will force the Employee record to be returned regardless of the User Record

[/li][li]Conditionally Suppress all User fields where u.lst_mod_dt <> {?Date}. If the Last Modified Date isn't a parameter then substitute a formula for the parameter.
[/li][/ol]Performance shouldn't be an issue since your dataset is based on Employees, not Users.

 

[kdecker]

Thanks for the help!

However, that was just an example of what I am doing... I actually really need to join on both fields... Does Cystal not support parameters in the SQL directly?

 

[rhinok]

I honestly can't remember with Crystal 7. Can you base the report on a Stored Procedure?