• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Crystal Reports Pass in parameter for all or just one value

I have a crystal report with a parameter for the production line.  I am running the report with ASP.NET, and using Crystal to pull the data.  I can specify the parameter to pass into Crystal ok, but what is the best way to make it so the user can choose all production lines or a specific production line.  Right now I am passing in the specific value let's say 100, and then a blank if they want all production lines.  How do I use the select expert to limit the values for the production line to be a specific value (example 100) or to return data for all production lines if the value is blank?
Please help!
0
tegronakron
Asked:
tegronakron
1 Solution
 
bdreed35Commented:
To do this, I usually build my parameter with a default value of "ALL", and then append the other values after that.
Then, in the Record Selection formula (Report menu, Selection Formulas, Record), I do something like this:

(
 if {?parameter} <> "ALL" then
     {table.field} = {?parameter}
 else if {?parameter} = "ALL" then
     True
)

This puts criteria on the database field if it is not equal to ALL and no criteria on the field if it is equal to ALL.
0
 
wykabryanCommented:
bdreed is correct, this is a more refined way based on his that we used in 15 different parameters.
(
if {?parameter} = "All" then true else {table.field} = {?parameter}
)
0
 
tegronakronAuthor Commented:
Ok I tried this, but the problem I am running into is that parameterID is a number field and it gives me an error saying there is a type violation.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tegronakronAuthor Commented:
Ok, I changed it to be -1 if it was all, and then that worked.
Thanks for pointing me in the right direction.
0
 
Kurt ReinhardtCommented:
FYI, wykabryan's format will filter the data, but not efficiently.  This filter won't be added to the WHERE clause that Crystal Reports generates and sends to the database for processing.  bdreed35's syntax is more complicated, but will consistently pass the filter to the db (and I've tested this against both Oracle and SQL Server) for more efficient processing.

To test this, I created a very simple report and ran it, toggling between the two statements.  As expected, when I select 'All' as the parameter value, there is nothing added to the WHERE clause.  This is correct.  When I specify an explicit value, however, wykabryan's filter still doesn't get added to the WHERE clause, but bdreed35's does.

Why is this important?  Performance should always be a consideration, even for small databases.  In this case, the lack of a WHERE clause means that every possible record will be returned across the network (worse case scenario) and will be filtered on the client once it has been returnred.  With a small or local database and a simple report, this may not be too noticable.  For a large database (up to millions of records) this may be VERY noticable.

~Kurt

0
 
wykabryanCommented:
Interesting finding.  Kurt is right, anything you put into the filtering area you want as much as possible to be passed to the sql statement to improve performance of the report.  I will say, with XI the use of true does work in our environment as it will pass everything into sql.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now