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!
tegronakronAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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 ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.