Pingback from  Twitter Trackbacks for                 Using Multiple Value Parameters to Select Records for your Crystal Reports - George Jensen's Blog         [customerfx.com]        on Topsy.com

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

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?

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.

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.

@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.