Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

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.


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:


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


What's This?
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.

Related Content
   Can I Bind an Attachment to a Specific Activity in Infor CRM version 7.54?
Recently I had a user ask if they could bind an attachment to a specific Activity within Infor CRM??
Posted on Jul 07, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (formerly SalesLogix) Administrator Locked Out
I thought I had seen all the issues affecting the datalink but this was a new one. We could not login t
Posted on Jun 18, 2015 by Mark Duthoy to SalesLogix Support
   How do I remove users from my Infor CRM system (formerly SalesLogix)?
I had an Administrator ask me for the steps they needed to take in order to remove a user from the Infor
Posted on May 08, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (Formerly Saleslogix) v8.1 update 03 and 05- Uncaught Error Scheduling Contact Processes
 In the Infor CRM (formerly Saleslogix) web client, under the Contact area you have the ability to s
Posted on May 07, 2015 by Kris Halsrud to Kris Halsrud's Blog
   Problem Showing All Area, Category, and Issue Values in Infor CRM (Saleslogix) Mobile And How to Fix
There is an issue with the Infor CRM (Saleslogix) Mobile client where, if you have a large list of Area,
Posted on Apr 24, 2015 by Ryan Farley to Ryan Farley's Blog


Clare said:

a string array is required in the Join function and apparently ({?UserName) is not recognised as a string array.

April 15, 2010 8:19 PM

Nissim Levi said:

This is very nice & elegant solution.

How About more complex requirements?

What if the parameter is multiple and dynamic, the value is code (number) and it presents also description. Now I want to present the user his selection (the descriptions only). how do you do that?

further more if the user select all the values in the list I want to present "ALL" in the report header. is there an elegant way to do that?

May 2, 2010 2:58 AM

Marjon said:

Well, that only one table and one record.

How about you make a report containing 5 related tables and make a three parameter fields and then pass to the crystal report viewer and generate the report.

June 11, 2010 4:20 AM

Curies said:

Well this work good or less good but still it work on side of crystal report. My question is how to workaround multiply values in the command/sql statment. U cant put parameter in query and make it multiple(I dont know why we have IN). We could workaround it if we would be able to change for example by formula parameter which goes to query(format it, or entire change it) Maybe I missing somethink if someone have solution on it please help me up.

July 27, 2010 9:06 AM

Kevin Suchlicki said:


These will work for SQL Server.  Other DBs will have their own methods.  You have a couple of options for passing a multiple-value list to a SQL Server sproc:

1) Pass the list as a comma-separated string.  Then in the sproc, build your SQL as nvarchar and execute it dynamically using sp_ExecuteSQL.


@UserIDList @nvarchar(max)



DECLARE @SQL nvarchar(max);

SET @SQL = 'SELECT * FROM dbo.Users WHERE UserID IN (' + @UserIDList + ')';

EXEC sp_ExecuteSQL @SQL;


2) Pass the list as a delimited string (with no spaces, delimiter of your choosing... i like pipes), and use LIKE.  You need to be sure none of the values will contain the delimiter you choose in actual data.


@UserIDList @nvarchar(max) -- example: asmith,jdoe,rbrown



SET @UserIDList = ',' + @UserIDList + ',';  -- need to add the delimiter before and after the string for this to work correctly

SELECT * FROM dbo.Users WHERE @UserIDList LIKE '%,' + UserID + ',%';


The second method may be very slow if the table is large and the only filter you are using is the @UserIDList.  If you have other filters, like dates, then SQL Server can use those to narrow down the result set first.  

One trick you can use as a variation on the above is to generate a table in a subquery and join (you could also use "IN") that to a master table, which may be faster than using "LIKE" on the master table directly.  For example, if you wanted all the sales by some list of users:

SET @UserIDList = ',' + @UserIDList + ',';  


FROM dbo.Sales s

INNER JOIN (SELECT UserID FROM dbo.Users WHERE @UserIDList LIKE '%,' + UserID + ',%') u ON u.UserID = s.UserID;

The idea behind this is that string comparisons are slow but joins on indexed fields are fast.

August 13, 2010 9:18 AM

Shari said:

I am using Crystal Reports 2008 on an Oracle DB, BOBJ Enterprise XI 3.1, w/o using a universe.

I tried this example and I am getting a syntax error that {USERINFO.USERNAME} This field name is unknown.

How do I make BOBJ {USERINFO} available to my Crystal Report? I really need this information for facility based report filter. I already have a cross-reference table built with BOBJ Username and Facility Id.

My Data Source is Oracle DB on a separate server than the BOBJ server.



October 28, 2010 10:49 AM

Bruce Connolly said:

Hi, George,

 Your "Using Multiple Value Parameters to Select Records for your Crystal Reports" was great.  It certainly did "help someone down the road" - me.  Thank you very much.

October 25, 2011 6:36 PM

Waldy said:

Surely you only need to add the commas if you are going to pass the parameter to a stored procedure.

{report.field} in {?parameterfield}

works just fine.  

January 20, 2012 8:26 AM

Larry said:

Worked like a charm for me. My group header suppression formula wasn't working because the first ID in the DB was not in the user selected list of IDs but the 2nd one was and it was mistakenly suppressing the GH.

February 7, 2012 3:43 PM

nadeekadn said:

can i use , for enter parameeter value???

March 20, 2012 1:47 AM

Mw said:

in a multiple list parameter there are 2 sections ( value and description ).  The join command  <Join({?UserName},",")> gives the list based on the value field. Is there a way to do a join on the Description field ?

March 28, 2012 9:25 AM

marleng said:

Thanks so much for this helpful post, I have been struggling trying to modify the parameters of my report for a few days this was quite helpful. While I was still looking for help I also ran into this other <a href="www.youtube.com/watch helpful video on parameter for custom reports.</a>

October 2, 2012 7:14 PM

Pass Value to succeeding pages down the road | User7 said:

Pingback from  Pass Value to succeeding pages down the road | User7

August 18, 2013 3:06 AM

Pass Value to succeeding pages down the road | User31 said:

Pingback from  Pass Value to succeeding pages down the road | User31

August 18, 2013 3:38 AM

Pass Value to succeeding pages down the road | user44 said:

Pingback from  Pass Value to succeeding pages down the road | user44

August 18, 2013 3:54 AM

Martin said:

Great one, thanks!

January 20, 2014 3:47 PM

Mott said:

Thanks for sharing!  Very helpful.

January 23, 2014 5:23 PM

Rod said:

Thank You. The Join function example helped me down the road.

September 25, 2014 2:13 PM

mankuji said:

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: www.mindstick.com/.../How%20to%20pass%20parameters%20with%20multiples%20values%20in%20crystal%20report

December 3, 2014 1:36 AM

Crystal Report Always prompting for Date Parameter | Zavcedo Answers said:

Pingback from  Crystal Report Always prompting for Date Parameter | Zavcedo Answers

December 7, 2014 10:49 AM

Leave a Comment

All contents Copyright © 2015 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register