Print Page | Close Window

SQL Command parameter

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17571
Printed Date: 25 Apr 2024 at 1:35pm


Topic: SQL Command parameter
Posted By: jrbledsoe001
Subject: SQL Command parameter
Date 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?


Replies:
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
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


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window