Print Page | Close Window

SQL Command parameter

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2016
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
Printed Date: 23 Sep 2020 at 3:47am

Topic: SQL Command parameter
Posted By: jrbledsoe001
Subject: SQL Command parameter
Date Posted: 15 Sep 2012 at 12:01pm
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
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?

Posted By: hilfy
Date 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
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.

Proviti, Data & Analytics Practice -

Print Page | Close Window