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.

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

 

What's This?
  
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.


Related Content
   Unicode Character Sets for SalesLogix Web 8.1
If you have the need to use unicode character sets so international users can view the web client in thei
Posted on Oct 29, 2014 by Mark Duthoy to SalesLogix Support
 
   Video: Demystifying Infor CRM (Saleslogix) Updates
Watch it now! If you missed yesterdays demo, "Demystifying Infor CRM (Saleslogix) Updates" ,
Posted on Sep 26, 2014 by Brianna Ojard to The Inbox
 
   Thoughts on the Future and Involvement in the Saleslogix/Infor CRM Community
Over the last year and a half I've been working on another product named Contatta. It's a great p
Posted on Sep 25, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Fixing the Orphaning of Salesfusion Data when Converting Leads to Contacts in Infor CRM (Saleslogix)
We have had a couple of client recently come to other with the same problem-When you convert a Lead to a
Posted on Sep 19, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Official Infor CRM Acquisition FAQ
What to expect now that Saleslogix is Infor CRM. Like many of you, it will take some time (probably a
Posted on Sep 18, 2014 by Brianna Ojard to The Inbox
 
Comments

 

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:

@Curies

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.

CREATE PROCEDURE dbo.uspUsers

@UserIDList @nvarchar(max)

AS

BEGIN

DECLARE @SQL nvarchar(max);

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

EXEC sp_ExecuteSQL @SQL;

END

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.

CREATE PROCEDURE dbo.uspUsers

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

AS

BEGIN

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 + ',%';

END

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 + ',';  

SELECT s.*

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.

Thanks,

Shari

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

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 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