If you want your parameter’s list of values to be pulled from the database you can use a dynamic parameter. But one of the down sides of a dynamic parameter is that you can’t type additional values to include in the list.  A dynamic parameter can only show values pulled from the data source. So if, for instance, you are pulling in a list of products and you want the list to have an “All” option at the top, you can’t simply add the word “All” to the list like you could with a static parameter.

My preferred way for adding an “All” option to a dynamic parameter is to use a SQL command as the source for the dynamic parameter. Using a SQL command gives you several other advantages as well, such as allowing you to filter your the list of values. Here is an example of a SQL command that will add an “All” option to the list of values (incorporating suggestions from MHurwood below):

Select Items.ID, Items.Desc
From Items
Where Items.Status = ‘A’


UNION ALL


Select ‘…All’, ‘…All’

The part above the UNION creates a list of all the active items, showing both the ID and the description of the items. The part below the UNION adds one row to the results of the query with the “All” option. Notice that “All” entry has several periods in front of it. This is one way to sort that value to the top of the list. You can use this method to add several values to your dynamic list, if needed.

Note that you wan to avoid using the fields from this command in other parts of the report. It should be used only for the dynamic parameter.

One of my colleagues, Angela Meharg of Datisfy, reminded me that you can use optional parameters to do something similar.  Instead of explicitly selecting a word like “All”, you can skip over the parameter.  Then you can program the selection formula to say that when the users doesn’t select a value they get all values.  The formula would look something like this in the Crystal selection formula:

and (if not (HasValue({?Items})) then True else {Table.Item} = {?Items})

In English this means, if there are no values in the items parameter, then every record qualifies. Otherwise the items that qualify are the ones that match the parameter.

If you have trouble with one of these options, you can schedule a short consult and I can give you a hand.