cancel
Showing results for 
Search instead for 
Did you mean: 

How to return all the records from dynamic paramater - Crystal Report

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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.

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

abhilash_kumar
Active Contributor
0 Kudos

Hi Veni,

Try this:

Select "equip"."equip_id" from equip

UNION

Select "All" from equip

-Abhilash

Former Member
0 Kudos

Abhilash,

No,  It doesn't work.  I tried in Crystal XI and aslo Crystal 2011.

Thanks

Veni

abhilash_kumar
Active Contributor
0 Kudos

There's no reason it shouldn't work. Could you run the same command on the database client and check if you see "ALL" as the 1st value in the list.

Oh, and you need to create a new Dynamic Parameter and choose "equip" from the list as the field.

-Abhilash

Former Member
0 Kudos

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

0 Kudos

HI Naga

I'm new is Crystal (XI)  , and very interested by your solution

but I don't see where I must type you UNION query with the ADD command

In the parameter Window ?

Thanks in advance

kind regards

Robert

Former Member
0 Kudos

Hi Robert,

Not in parameter window, after connection was establish then you can observed tables, views & stored procedures. on the above of them you will find an ADD command option, you need to write the commands there..

Thanks & regards,

Naga.

0 Kudos

Many thanks for the quick answer !

I have try , and it's ok

kind regards

Robert

Former Member
0 Kudos

oh great..

Answers (1)

Answers (1)

Former Member
0 Kudos

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.