Adding an “All” option to a dynamic parameter

Sunday 19 September 2021 @ 10:05 am

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.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)






2 Responses to 'Adding an “All” option to a dynamic parameter'

  1. MHurwood - September 21st, 2021 at 1:11 am

    Some corrections and a couple of suggestions…

    Change your SQL command to:

    Select Items.ID, Items.Desc
    From Items
    Where Items.Status = ‘A’
    UNION ALL
    Select ‘…All’, ‘…All’

    You had “Item” for the columns, and “Items” for the table, so that wouldn’t have worked. Aliasing “Items” for “Items” is just redundant so can be removed. When adding the “All” row you don’t need a From Clause at all because you’re just creating the data, not reading it from a table. (Behind the scenes SQL Server would be returning “…All” for every row in the table(!), but Crystal probably removes all those duplicates.) Using “UNION ALL” should be minimally faster than just “UNION” because there’s no duplicate check done by SQL Server using that.

    Finally, if you put the “…All” part as the bit *after* the UNION then Crystal will name the returned columns “ID” and “Desc”, using the default names from the SQL columns, while having it the other way round you end up with columns called “Expr001” and “Expr002”.

    Cheers!

  2. Ken Hamady - September 21st, 2021 at 9:07 am

    Thanks, I have updated the post to reflect your changes.


Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server