Print Page | Close Window

Data selection in a 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=5050
Printed Date: 03 May 2024 at 10:06pm


Topic: Data selection in a Parameter
Posted By: keiths
Subject: Data selection in a Parameter
Date Posted: 18 Dec 2008 at 9:10am

I would like to give the user the option of selecting a field in a parameter or printing all of the fields.  In other words, I want the user to be able to select one or multiple vendor names.  I can do that with the parameter.  However, if they left the parameter null or if I set up a selection of All, have the report select all of the vendor names.  Is there a way to do that?

 
Thanks for any help.


Replies:
Posted By: hilfy
Date Posted: 19 Dec 2008 at 1:39pm
Try this in your selection criteria:
 
(IsNull({?Parameter}) or {table.field} = {?Parameter})
 
Notice that I put parentheses around this.  These are required if you have additional selection criteria, otherwise the selection filter won't work correctly.
 
-Dell


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

Posted By: keiths
Date Posted: 22 Dec 2008 at 7:07am
Thanks for your response.  However, the code did not work.  Whenever I run the report and leave the vendor name parameter null, it does not come back with any records.  But, whenever I select two or three vendor names, it brings back the records for those vendors.
 
Any other ideas?
 
Thanks again for any help.

Posted By: hilfy
Date Posted: 22 Dec 2008 at 9:10am

Do you have a default value set for the parameter?  If so, you'll have to use that instead of"IsNull()".   You might also try this:

(IsNull({?Parameter}) or {?Parameter} = "" or {table.field} = {?Parameter})

-Dell



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

Posted By: keiths
Date Posted: 22 Dec 2008 at 9:53am
Yes, there is a default parameter.  It is a list of about 400 vendor names.  The user wants to be able to pick and choose several vendor names or to be able to have the report run all of them.
 
I tried the additional code and it did not work either.
 
Thanks.

Posted By: DBlank
Date Posted: 22 Dec 2008 at 2:41pm
You can set up a command to do this.
In your Database expert there is the Add Command option just above your DB source.
Double click the Add command and put the following code
 
SELECT FIELD (your vendor field that you want to select from here)
FROM table (the table your vendor field is in)
UNION
SELECT '...All'
 
In the Database Expert, Join this new command on the vendorname field to the vendor table on the vendor name field.
 
Point your Paramenter field to this command field instead of your vendor.vendorname.
 
Change your select expert to handle this with an if statement.
if {?Paramter}="...All" then
{Vendor.vendorname}={Command.field}
else {?Parameter}={Command.field}

Posted By: keiths
Date Posted: 23 Dec 2008 at 8:37am
Thank you very much DBlank.  That worked for what I was trying to do.

Posted By: DBlank
Date Posted: 15 Sep 2009 at 6:46pm

as an addendum the select statement should be

{?parameter}="...All" or {?parameter}={table.field}

Posted By: ishu1404
Date Posted: 09 Jan 2011 at 7:41pm
hi DBlank,
 
 
I have already created a command objecta and linked it with the main table as specified. I have created a static prompt at the report level but the values fetched by the command object are not displayed in the Value-List in the prompt.
How to obtain the values fetched by the command object at the report level prompt?
 
Thanks
Ishu

Posted By: DBlank
Date Posted: 10 Jan 2011 at 4:12am
it should be a dynamic parameter pointing to the comman object field you wish to select from

Posted By: ishu1404
Date Posted: 11 Jan 2011 at 12:33am
Hi DBlank,
 
  Thanks for the response...i have created a dynamic parameter but i am not seeing any option to link the parameter to the command object. This is the first time i am working on crystal report.
 Request you to kindly let me know how to link the parameter with the command object so that i can see the values fetched by the command object in the parameter values?
 
Thanks
Ishu

Posted By: DBlank
Date Posted: 11 Jan 2011 at 4:01am
add the command object to the report
create a new parameter
click on the dynamic button
click in the first row of the Value column and pick the field you want to use
add a description if you want int he description column
click in the parameter column and add the name of the param
cheange any options in options section
click OK

Posted By: ishu1404
Date Posted: 11 Jan 2011 at 8:22pm

Hi DBlank,

  Thanks for the response....
When i am clicking in the first row of the Value column i am not getting any value except "NONE". I think this is the problem.
 
  Could you please help me...
 
Regards
Ishu
 
 
   

Posted By: DBlank
Date Posted: 13 Jan 2011 at 4:17am
IN the List OF Values you have it set to 'Dynamic'?
Under Data Source you have it set to 'New'?

Posted By: ishu1404
Date Posted: 16 Jan 2011 at 11:20pm
Yes, i have set Dynamic in the list of values and New in the data source..
 
Regards
Ishu


Print Page | Close Window