tegronakron
asked on
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!
Please help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Ok, I changed it to be -1 if it was all, and then that worked.
Thanks for pointing me in the right direction.
Thanks for pointing me in the right direction.
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
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
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.
(
if {?parameter} = "All" then true else {table.field} = {?parameter}
)