Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: SQL Command parameter Post Reply Post New Topic
Author Message
jrbledsoe001
Newbie
Newbie


Joined: 24 Jul 2012
Location: United States
Online Status: Offline
Posts: 2
Quote jrbledsoe001 Replybullet Topic: SQL Command parameter
    Posted: 15 Sep 2012 at 12:01pm
Hello,
The company number in my database is a numeric field (type is int).  My Crystal report is using a SQL command object with SQL command parameters.  I want to create a SQL command parameter which will pass a single company number, a list of company numbers or ALL company numbers.  I want the parameter to prompt the user to select 'ALL', or a list of companies, or a single company.  I need help constructing the IF statement or case statement.  
 
Here is what I have working so far
 
Created a Crystal string parameter called CoNbr
Created a SQL Command parameter called CoNbr
 
Select
cpy.Company,
cpy.Name
FROM LSDEV.dbo.APCOMPANY cpy
where cpy.Company = CAST('{?CoNbr}' as INTEGER)
 
Do you know a way to pass a list of company numbers to the SQL command parameter?  How would I pass 'All' - cpy.Comany > 0?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 17 Sep 2012 at 4:19am
I'm going to assume that the command for your report is actually more complex than what you've posted.  I would try this:

1.  In the command, create the command parameter as a number and turn on the Multi-select or Multiple Values option for it. (We'll edit this in the main report to configure it correctly....)

2.  Change the last line of your command to:
 
where ({?CoNbr} = 0 or cpy.Company in {?coNbr})
 
3.  Create a second command that will provide the values for your paramter.  It will look something like this:
 
Select 0 as Company, '*All' as Name
UNION
Select cpy.Company, cpy.Name
from LSDEV.dbo.APCOMPANY cpy
order by 2
 
4.  Edit the {?CoNbr} parameter in the MAIN REPORT.  Set it to dynamic and point it to the command that you just created - value field is Company and display field is Name.  When you run the report, the list in the parameter should appear in alphabetical order by company name with "*All" at the top.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.