want to give option of "all" in parameter

[sbcsandhu]

i have report in crystal xi and sql server.

my report will have about 4 or 5 parameters

i want to give the user the option to pick all
how would i do that in my sql server query that i wrote.
is there away to do it in my query and not in crystal?
if i cant do it in my query, how would i do it in crystal

where main.user_id = '{?User_ID}'
and( main.action_date > = {?Action Date Start}
and main.action_date <= {?Action Date End})

 

[sbcsandhu]

here is my entire command statement

need the give the user the option of all, if they want to see all the data for that parameter

select main.rec_no as rec_no_Main,
main.user_id as User_id_Main,
main.User_last_name as User_last_name_main,
main.User_first_name as User_first_name_main,
main.User_middle_initial as User_middle_initial_main,
main.action_date as action_date_main,
main.Action_time as action_time_main,
main.Window_title as window_title_main,
main.Trans_id as Trans_id_main,
main.key_name as Key_Name_main,
main.key_value as key_value_main,
main.action_type as action_type_main,
main.workstation_id as workstation_id_main,
main.trans_succ as trans_succ_main,
main.review_status as review_status_main,
main.inq_sw as inq_sw_main,
(select [attribute name]
from mrextract.dbo.t_code code
where code.[tag id] = mod.col_name) as attribute_name,
mod.*
from mrextract.dbo.MR0052E_MAIN main
inner join mrextract.dbo.MR0052E_MOD mod on main.rec_no = mod.rec_no
and main.user_id = '{?User_ID}'
and( main.action_date > = {?Action Date Start}
and main.action_date <= {?Action Date End})
and main.trans_succ = '{?Transaction Sucessful}'

 

[sbcsandhu]

when i do the parameter in the record selection, it goes thru the entire table, but when i do the parameter in the command it give me a quick result set

thats is why i would like to do the "all" in the command over the record seletion

 

[lbass]

I'm not familiar with setting up conditions in the From clause, but I imagine you could do it the same as in the where clause. Decide on some defaults, let's say '00' for user ID, 2999-9-9 for date, and 'NA' for transaction success.

from mrextract.dbo.MR0052E_MAIN main
inner join mrextract.dbo.MR0052E_MOD mod on main.rec_no = mod.rec_no and
(
(
'{?User_ID}' <> '00' and
main.user_id = '{?User_ID}'
) or
'{?User_ID}' = '00'
) and
(
(
{?Action Date Start} <> to_date('2999-09-09') and
main.action_date > = {?Action Date Start} and
main.action_date <= {?Action Date End}
) or
{?Action Date Start} = to_date('2999-09-09')
) and
(
(
'{?Transaction Sucessful}' <> 'NA' and
main.trans_succ = '{?Transaction Sucessful}'
) or
'{?Transaction Sucessful}' = 'NA'
)

I'm not sure how dates are expressed in your datasource, so you might have to adjust that part.

-LB