on 03-06-2013 2:01 PM
I am using Crystal 2011.
How can I add "ALL" to the dynamic parameter field, so I can return all records in the report. Is there a way to it without using a Add Command in Database Expert.
Thanks in advance
Veni
Hi Veni,
If you would like to display 'ALL' word in the parameter list, then you will use 'UNION ALL' in the Add command.
For Example if i would like to display 'ALL' word in the country parameters list then you will write a command in ADD command like below
SELECT 'ALL' As Country from Customer
UNION ALL
SELECT `Customer`.`Country` FROM Customer
So by using the above command the ALL word is also add in parameter list of dynamic prompt when you create a dynamic parameter on Country field.
Then in the Record selection you need to write the formula as
if {?My Parameter} = 'ALL' then true else {?My Parameter} = {Customer.Country}
After following the above steps please select 'ALL' in parameter list and test the issue...
Thanks & regards,
--Naga.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The one thing I would change in Naga's response is the record selection formula. When you use an If statement, the formula generally does not get passed down to the database for processing. Instead, Crystal will pull all of the data into memory and filter it there, which can slow down report processing. However, with a small change to what he posted, you can push the filter down to the database for processing:
({?My Parameter} = 'ALL' or {?My Parameter} = {Customer.Country})
Not the parentheses on the outside of this statement - they MUST be there if there are other items in the selection criteria.
If you want users to be able to select multiple values in the parameter, the formula will change to something like this:
('ALL' in {?My Parameter} or {Customer.Country} in {?My Parameter})
-Dell
Thank you for all your replies. I really appreciate that.
I tried the Union All in Add Command for some reason I don't see 'ALL' in my parameter list.
I am using MS SQL as my database and here is the query I tried.
select 'ALL' AS equip_id from equip
UNION ALL
Select "equip"."equip_id" from equip
Thank you Abhilash.
I did run the same command on the database and I see ALL in the list.
If I try only
Select 'ALL' from equip it works when I union it with the same table I don't see ALL in the list.
But I just realized in Crysal Reports in the parameter form we can select all the values by clicking on the >> button. All the values go into selected values box.
Thank you once again.
Veni
Hi Veni,
If you make the dynamic parameter as optional and use it in the record selection, it will return all the records in the report.
- Nrupal.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.