Using Multiple Value Parameters to Select Records for your Crystal Reports

I have often been asked to provide the ability to allow users a multi select parameter, such as User Name, and then have that parameter limit the data in their Crystal report.  I have to admit this one has always been one of the items I always have struggled through, coming up with one alternative or another to meet the client needs.  Recently I was asked this same question by a support client of ours.  Together, we can up with a pretty lengthy Record Selection formula to achieve the desired results.  Not happy with how hard it was to achieve this I decided to dig into this issue again this morning and my results were surprisingly a lot easier then I ever imagined.


To prove my point follow along by creating a new Crystal report connected to your SalesLogix database,  The only table you need to add to this report is UserInfo and we only need one field, UserName.


There are three pieces to this puzzle.  The first is the parameter.


The parameter I created was labeled UserName, I changed the “List of Values” field to Dynamic, “Prompt with group text” I typed in UserNameList and then selected the UserName from the field drop down under the word Value. Other settings are “Prompt Text” = Select the Users you want to display in this Report, “Prompt with Description Only” is set to True, and the most important is “Allow multiple values” is set to True.


Next let’s Create a formula field to see our results,  Name the field anything you would like and add the following as the formula.


Join({?UserName},”,”)


Now drag your formula field onto your report.  You should now be prompted by the report to select some users to display on your report. Select two or three to prove the point. When you close the parameter you will find you formula field is now populated with the names of the individuals you selected.  Each value is separated by a comma.


The final piece is the record selection criteria.  Remember the record selection criteria is set using the Report>Selections Formulas>Record menu item.  When the formula editor opens, add the following formula:


{USERINFO.USERNAME} in {@Test}


For your version of the formula, “{@Test} should be replaced with the your formula field.  When you close the editor you immediately see your list of users drop to the two or thee you had selected.


Embarrassingly easy stuff.  I hope this helps someone down the road. 


Thanks Geo


 

2 Comments

  1. I have a code source using crystal report in visual studio 2008.
    I want to display my report with all the values of parameters which have multiple values.
    It displays only the first value instead of many values. I use 2 parameters : periode de paie, matricule. I have problems about the second parameter for displaying all the matricules in my report. A part of my codes is below:
    for full implementation refer here: http://www.mindstick.com/blog/667/How%20to%20pass%20parameters%20with%20multiples%20values%20in%20crystal%20report

    Reply
  2. I need the userid (numeric) list instead of a string values list. So when I tried to put the formula Join({?UserID},”,”), it gives an error that ‘A String array is required here”. I tried to change it to Join(ToText({?EmpCd}),”,”), then I get the error “This array must be subscripted. For example Array[i]”. Your help is much appreciated.

    Reply

Submit a Comment

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

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!