Urgent: Select query in Crystal Report
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5040
Printed Date: 06 May 2024 at 5:47pm
Topic: Urgent: Select query in Crystal Report
Posted By: bujji
Subject: Urgent: Select query in Crystal Report
Date Posted: 18 Dec 2008 at 12:02am
|
Hi,
I have a prompt which takes the user input as 'OPN' ,'TND','*'.
According to his input i should execute one select query if it is 'OPN'
and if he selects 'TND' i should execute one more select query ...
How can i implement this in Crystal Report 11.0 version?
|
Replies:
Posted By: rahulwalawalkar
Date Posted: 18 Dec 2008 at 12:19am
|
Hi,
How is the prompt set ,does it take multiple values or single value......
In your record selection formula you can check for
{table.fieldname} like {?prompt_name}
Cheers
Rahul
|
Posted By: bujji
Date Posted: 18 Dec 2008 at 12:24am
|
I have a drop-down so the user has to select only one value either 'TND' or 'OPN' or '*'.
but how can i mention the select query in this selection formula ?
i wrote somethign like this but am getting error :
(if uppercase ({Command.STATUS}) = 'OP'
then select distinct ld_leg_id,Status from ld_lge_t where CUR_OPTLSTAT_ID in (300) else (if uppercase({Command.STATUS} = 'TND' then select distinct ld_leg_id,Status from ld_lge_t where CUR_OPTLSTAT_ID in (320) else Select DISTINCT ld_leg_id, 'OPN' AS status from LD_LEG_T where CUR_OPTLSTAT_ID =300 UNION Select DISTINCT ld_leg_id, 'TND' AS status from LD_LEG_T where CUR_OPTLSTAT_ID =320 )) |
Posted By: rahulwalawalkar
Date Posted: 18 Dec 2008 at 12:30am
|
Hi I am not clear what you looking for in the report,Have you used command object ?,can't it be done using tables ,or views......
Please let me know what is the expected output in your report ,also if you could post some sample data ......
Cheers
Rahul
|
Posted By: bujji
Date Posted: 18 Dec 2008 at 12:38am
|
Hi,
I have one prompt in the report as 'Status' which has the option of drop-down and it has values 'OPN' and 'TND' and '*'
if the user selects 'OPN' i have to display all the loads which have the value as 300 for the column CUR_OPTLSTAT_ID in the table 'ld_leg_t '.
the select query for this will be "select distinct ld_leg_id,Status from ld_lge_t where CUR_OPTLSTAT_ID in (300)"
similarly for 'TND' i have to display all the loads which have the value as 320 for the column CUR_OPTLSTAT_ID in the table 'ld_leg_t '.the select query for this will be "select distinct ld_leg_id,Status from ld_leg_t where CUR_OPTLSTAT_ID in (320)"
similarly for '*' i have to display all the loads which have the value as 320,300 for the column CUR_OPTLSTAT_ID in the table 'ld_leg_t '.the select query for this will be "select distinct ld_leg_id,Status from ld_leg_t where CUR_OPTLSTAT_ID in (320,300)" |
Posted By: rahulwalawalkar
Date Posted: 18 Dec 2008 at 12:56am
|
Hi
Have you created command object and coded IF else in it ,if yes remove the if else part.
Open your report, go to record selection formula , then use the code below
if {?My Parameter} = 'OPN' then CUR_OPTLSTAT_ID in (300)
ELSE IF {?My Parameter} = 'TND' then CUR_OPTLSTAT_ID in (320)
else {CUR_OPTLSTAT_ID} Like {?My Parameter} Cheers
Rahul
|
Posted By: bujji
Date Posted: 18 Dec 2008 at 1:19am
|
Hi i tried ur way,
but when am trying to save it it's displaying "A number range is required here" for (300).
|
Posted By: rahulwalawalkar
Date Posted: 18 Dec 2008 at 1:25am
|
Hi
Sorry my mistake try this
if {?My Parameter} = 'OPN' then CUR_OPTLSTAT_ID in [300]
ELSE IF {?My Parameter} = 'TND' then CUR_OPTLSTAT_ID in [320]
else {CUR_OPTLSTAT_ID} Like {?My Parameter} Does the query work with * parameter value
Cheers
Rahul
|
Posted By: bujji
Date Posted: 18 Dec 2008 at 3:12am
|
Hi,
I'm not able to fetch any records for any value
 |
Posted By: rahulwalawalkar
Date Posted: 18 Dec 2008 at 3:39am
|
Hi email me your report and some sample data in xls sheet
mailto:rahulwalawalkar@yahoo.com - rahulwalawalkar@yahoo.com
cheers
rahul
|
Posted By: bujji
Date Posted: 18 Dec 2008 at 4:13am
| i have sent the details |
Posted By: Savan
Date Posted: 18 Dec 2008 at 4:18am
|
You can do this while writing the sql code in add command i,e while connecting to database itself you can create the parameters based on these parameter you can pass the value. ------------- Thanks Savan |
Posted By: themessenger
Date Posted: 18 Dec 2008 at 7:44am
|
I think you are trying to make it too difficult. Just pull in: Select DISTINCT ld_leg_id, 'OPN' AS status from LD_LEG_T where CUR_OPTLSTAT_ID =300 UNION Select DISTINCT ld_leg_id, 'TND' AS status from LD_LEG_T where CUR_OPTLSTAT_ID =320 then in the Section Expert - Details - Suppress put something like IF {?status} = status or {?status} = "*" then false else true Hope this helps. TheMessenger ------------- Managing Director www.allmymenus.com |