Crystal Reports: Optional-Multi-Select Parameters

Most report designers are familiar with the parameter’s ‘Allow Multiple Values’ option. When enabled, this option allows a report’s user to select one or more values for a parameter at runtime. The individual running the report could select one or more countries, for example. Should the individual want to select all countries, he or she would need to add each country individually when the prompt is presented. If there are a lot of countries, this could be a time-consuming task. Fortunately, there is a better way.

To start, edit the multi-select parameter. You may want to include instructions on how to use the parameter in the Prompting Text–something like ‘Select one or more countries. Select ‘ALL’ for all countries.’

Next, click the ‘Default Values…’ button. If the parameter is a string, type ‘ALL’ in the ‘Select or enter value’ text box, if the parameter is a number type -1. Click the ‘>’ button to add the value to list of default values.

If the parameter is a number click the ‘Set Description…’ button, enter ‘ALL’ in the ‘Description’ text box, then click ‘OK’. If you want to hide the numeric values, select ‘Description’ from the ‘Display’ drop-down list.

In either case, use the up arrow at the top right corner of the ‘Default Values/Description’ listbox to ensure that ‘ALL’ is at the top of the list. This will make it easier to select ‘ALL’ when the report is run. Click ‘OK’ to exit the ‘Set Default Values’ window, then click ‘OK’ to exit the ‘Edit Parameter Field’ window.

Next, open the report’s Record Selection Formula Editor. If the {?Countries} parameter is a string, add the following text:

(If {?Countries}<>'ALL' Then
{Customer.Country} IN {?Countries}
Else
True)

If the {?Countries} parameter is a number, add the following text:

(If {?Countries}<>-1 Then
{Customer.Country} IN {?Countries}
Else
True)

Essentially, if the parameter’s value isn’t ALL, then the filter is applied. Otherwise, the filter is ignored.

When the report is run, the word ‘ALL’ will be displayed first in the list of values for the parameter. Clicking ‘Add’ will set the parameter’s value to ‘ALL’.

Summary

In addition to making your report more flexible, this techniques will make it easier to deploy, manage and schedule your report in Crystal Enterprise.

Samples

Crystal Reports 10 – Advanced Parameter Sample

30 thoughts on “Crystal Reports: Optional-Multi-Select Parameters

  1. Rashid Mahmood

    Hi there,

    I want to thank you for your this article.
    It has solved my one week awaiting problem about Crystal Reports.
    Thank you so much. If you were present with me then I would hug you :)
    May ALLAH Almighty give you happines of both worlds.

  2. Tatterdemalian

    I have another question… hopefully one easier to answer (I eventually had to use Command tables to rectify the previous one).

    How do you add a parameter that allows multiple values to a report? I tried a straight drag-and-drop, but that only results in a blank field on the report, regardless of the values in the parameter.

    Is there any way to add a list of all the values in the parameter to a report, perhaps using formula fields to create a string?

  3. Craig Buchanan

    You will need to create a formula to serialize the values in a multiple-selection parameter. If the parameter is a string, you can use Join({?MyParameter},”,”) to create a comma-delimited list. Otherwise, you will need to use a For Next statement to join the values. See the sample report for examples.

  4. kalpna

    Hi Craig,
    How to write a sql query for multi select parameters?
    For example I want to see two employee’s work history in state newyork.
    so here I pass state parameter (single value) in C#.net form.
    I can even listbox to select and pass multiple values for employee parameter but what will be the sql query for this advanced search?
    Please Help.
    Thanks,
    Kalpna

  5. Aaron

    Thanks so much!! This worked and I was finally able to move on but why doesn’t

    (If not isNull({?Countries}) Then
    {Customer.Country} IN {?Countries}
    Else
    True)

    work? It just returns no rows.

  6. dennisb

    Thank your for putting this together it is exactly what I was looking for.

    I do have one question though. When I open up the record selection formula editor, I have the following information in there already, due to my report parameters.

    {COMPLETED_INSPECTIONS_VW.SUBGRPCD} = {?SUBGROUP CODE} and
    {COMPLETED_INSPECTIONS_VW.INSPTYPE} = {?INSPTYPE} and
    {COMPLETED_INSPECTIONS_VW.CLTPRNNM} = {?CLIENT} and
    {COMPLETED_INSPECTIONS_VW.PERFORMDT} in {? START} to {?END} and
    {COMPLETED_INSPECTIONS_VW.LOCTYPDE} = {?LOCATION TYPE}

    Where exactly do I place this in what I have so far?

    (If {?SUBGROUP CODE}‘ALL’ Then
    {Customer.SUBGRPCD} IN {?SUBGROUP CODE}
    Else
    True)

    Thank you for any advice you may offer.

  7. Craig Buchanan

    Add the following to your Record-Selection Formula:

    AND (
    If {?SUBGROUP CODE}=”ALL” Then
    {Customer.SUBGRPCD} IN {?SUBGROUP CODE}
    Else
    True
    )

  8. dennisb

    So i was able to get the Record-Selection Formula to save without error YAY!, but now I get zero returns when I choose “ALL” for any of the parameters with “ALL” as an option. If I simply go back and choose all of the options then i get returns. Any ideas?

    Here is what my formula ended up looking like.
    {COMPLETED_INSPECTIONS_VW.SUBGRPCD} = {?SUBGROUP CODE}
    AND
    (
    If {?SUBGROUP CODE}= ‘ALL’ Then
    {COMPLETED_INSPECTIONS_VW.SUBGRPCD} IN {?SUBGROUP CODE}
    Else
    True
    )
    AND
    {COMPLETED_INSPECTIONS_VW.INSPTYPE} = {?INSPTYPE}
    AND
    (
    If {?INSPTYPE}= ‘ALL’ Then
    {COMPLETED_INSPECTIONS_VW.INSPTYPE} IN {?INSPTYPE}
    Else
    True
    )
    AND
    {COMPLETED_INSPECTIONS_VW.CLTPRNNM} = {?CLIENT}
    AND
    (
    If {?CLIENT}= ‘ALL’ Then
    {COMPLETED_INSPECTIONS_VW.CLTPRNNM} IN {?CLIENT}
    Else
    True
    )
    AND
    {COMPLETED_INSPECTIONS_VW.PERFORMDT} in {? START} to {?END}
    AND
    {COMPLETED_INSPECTIONS_VW.LOCTYPDE} = {?LOCATION TYPE}
    AND
    (
    If {?LOCATION TYPE}= ‘ALL’ Then
    {COMPLETED_INSPECTIONS_VW.LOCTYPDE} IN {?LOCATION TYPE}
    Else
    True
    )

  9. Craig Buchanan

    My mistake. It should be:

    AND
    (
    If {?LOCATION TYPE} <> ‘ALL’ Then
    {COMPLETED_INSPECTIONS_VW.LOCTYPDE} IN {?LOCATION TYPE}
    Else
    True
    )

  10. jans78

    Hi..
    i’m doing the date parameter.
    How to add date range at my script:

    (If {?Collection Date} ‘ALL’ Then
    ToText({CPS_DM05_VW.BATCH_DATE}) IN ToText({?Collection Date}) to ToText({?Collection Date})
    Else
    True)

    Please help me…coz the script still having an error.

    TQSM

  11. System Administrator

    Try:

    (If {?Collection Date}<>‘ALL’ Then
    ToText({CPS_DM05_VW.BATCH_DATE}) IN ToText({?Collection Date}) to ToText({?Collection Date})
    Else
    True)

  12. jans78

    still got an error..”…or a string required here”
    I set Collection Date as string and allow range values at the parameter setting.

    below is my existing script:

    (If {?Payment Channel Group} ‘ALL’ Then
    {CPS_DM05_VW.PAY_CHNL_GRP_CODE} IN {?Payment Channel Group}
    Else
    True) and
    (If {?Collection Date} ‘ALL’ Then
    totext({CPS_DM05_VW.BATCH_DATE}) in totext({?Collection Date}) to totext({?Collection Date})
    Else
    True) and
    (If {?Batch Status} ‘ALL’ Then
    {CPS_DM05_VW.BATCH_STATUS_DESC} IN {?Batch Status}
    Else
    True) and
    (If {?Process Date} ‘ALL’ Then
    ToText({CPS_DM05_VW.START_PROCESS_DATETIME}) IN ToText({?Process Date})
    Else
    True) and
    (If {?POC} ‘ALL’ Then
    {CPS_DM05_VW.TXN_POC_CODE} IN {?POC}
    Else
    True)

    TQ

  13. plazma

    I ma using Crystal Report Enterprise.
    I have a date parameter in my report.
    Now i want to make it optional(means if user wants he will enter it and if user does not wish to then he wont).
    Is this possible ?
    if yes then can anyone plz tell me how it could be??

    thx in advance

  14. mano

    Hi,
    How do i do a ucase for multiple selection.I need the parameters entered to be in upper case.Thanks!

  15. Craig Buchanan

    I would ensure that the parameter’s value is in upper case, then set the parameter’s description to be proper-case. Make sure to set the parameter’s ‘Prompt with Description Only’ to True.

  16. adelao

    Hi,
    I have a performance problem connected with the use of multiple values parameters. Like it was said above in order to select all the values for this kind of parameter without manually selectig all the values one by one, you need to create a dummy value “ALL” and create a formula that eliminates the fileter if the parameter value is ALL.
    This works just great, but my problem is that this type of filters are not pushed down to the database, the filtering process takes place in Crystal onces the record are retrived from the database.

    And this creates a big performance issue for me.
    Just to explain how big my problem is, here is an example: I have a report with selling records world wide and one of my parameters is Country. Now, let’s say I only want to see the record from US, so I set the param value to US, but because of the formula I had to use for the ALL value, the query that Crystal sends to the db does not consider the US filter, so I have to wait anyway till all the record are sent to crystal and after that fileter by it. If the filter country = ‘US’ is executed by the db, it takes second to see the result, but with crystal filtering this may take minutes.

    So if anybody know a way to forse the query execution by the db it will be very helpfull.

    Thanks

  17. Craig Buchanan

    I would have to see the record-selection formula to be able to advise you.

    To see if you record-selection formula will be sent to the database, compare it to query in Database | Show SQL Query…

  18. Jian Leen

    Thx ! this helped a lot ~
    but i was wondering the same thing as Aaron, as to why IsNull({parameter}) not possible to function properly ?

  19. arodri

    Hello,

    I’m not sure if my question directly goes along with this topic but I’m hoping someone will be able to help me! I’ve been struggling with this issue for a few days now and I have a deadline :/

    I have a report that displays certain types of items for the various locations of our business.
    There are many different “Item Types,” and under each “Item Type” there are sevreal Items. This is true for all of our locations.
    Here’s a very simple example with food: In Iowa, we could have apples and oranges (items) which fall under the Item type “Fruit.” There could also be another Item Type, “Vegtables” in Iowa with several items under that (corn, peas, …).

    What I’m trying to do is set up parameters that allow the user to type in either one OR two differnt Items OR ItemTypes to search by.

    So, for example, I want the user to see:

    “Enter Item name:”
    _____________ and/or ___________

    “Enter Item Type name:”
    _____________ and/or _____________

    -Ideally, the user would be able to type in “carrots” and “apples” and get a result. They would also be able to type in just “carrots” and leave the second box blank therefore only getting results for that specific item type.
    -So basically, the user would be able to type something into all 4 seach boxes and get a result, or only type one Item and one ItemType, or leave all 4 boxes blank and still get a result.

    Any ideas on how to do this?

    Any help would be greatly appreciated..thanks in advance!

  20. san

    hi
    I am stuck with one problem.

    I am using crystal reports Xi,
    i need to create optional prompt..
    I have two prompts, coming from two fields.

    o Criteria screen prompt: “Number of Reserve Cases Greater Than:
    OR
    “Number of Units in Reserve Greater Than:”

    if the users enters value in the first prompt then the second one will become optional, but if he enters the second one the first one is optional..
    Bottom line, user must be able to enter the values in only one prompt, but both the prompt must be displayed.

    Also in the report header, the prompt we havent entered, there must be N/A be displayed.
    I dont know if i am able to explain my problem well..
    could someone help me out.. i am stuck on this over 3 weeks..

    thanks in advance

    shree

  21. Zuber

    if you have version 2008, and if you select allow multiple values, when it prompts you to enter values how can you enter many values at once instead of entering them at a time and clicking right arrow.

  22. Craig Buchanan Post author

    @Zuber: You can’t unless you add the values to the parameter when you build it.

  23. jenny

    I used P_Country_ID as a numberic type of parameter, the singer number testing works well, however, when I want to test for “All” option by putting -1 in, it doesn’t work. Please help

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>