Concept
- Convert the parameter array to CSV
- In the SSRS report, on the parameters tab of the query definition, set the parameter value to:
- =join(Parameters!YourParamName
.Value,",") - Create a Table Valued function to convert the CSV string to a table that can be addressed as per normal (See function below - there are many variation on the web)
- Join or or use this function as per a normal sub SELECT statement (See example sp below
You can also use a simple text box to pass a comma separated string to SQL server directly. The user simply enters a comma separated list of values. This is useful where the user may need to select multiple values from a list that is too large to be displayed as a multi select drop-down (ie a company might have many thousands of customers which is too many to display in a drop down - but entering half a dozen client codes directly into a text box is a viable solution)
Gerard
User defined function for numeric strings
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
Author: Gerard
Create date:
Description: Returns a recordset from SSRS multi select parameter that has been passed into a stored procedure
(turns a comma seperated string into a records set)
Requirements: Paramater variable must be formed up into a comma deliminated string (or some other delimiter if the data
already contains commas)
Parameters can be passed in directly from SSRS often but some data types need to be massaged in SSRS
This is achieved in the SSRS dataset that is calling the sp by using the VB JOIN function
DS calling SP -> Properties -> Parameters
Add Parameter that is multi select -> add JOIN script to concat selected values with commas (or other char if data contains commas)
EG Param=-ResponsiblePartner Expr=JOIN(Parameters!ResponsiblePartner.Value,",")
Example of usage in SP
WHERE EMPLOYEE_CODE IN (SELECT * FROM DelimitedSplit8K(@RespPartners, ','))
INNER JOIN (SELECT * FROM DelimitedSplit8K(@RespPartners, ',')) AS t ON t.Item = s.CUST_CODE
NOTE: a useful bit of code to create a comma delimited string from a recordset
is below (useful place to leave it)
SELECT @RespPartners = COALESCE(@RespPartners + ',','') + EMPLOYEE_CODE
FROM _JWS_Responsible_Partners
Modifications: ALL MODIFICATIONS MUST BE DETAILED BELOW SHOWING DATE
Modified By: Gerard
Date:
Modification: modified DelimitedSplit8K to return a Integer recordset where multi select field is numeric
======================================================================================================================================
*/
ALTER FUNCTION [dbo].[DelimitedSplit8K_Int]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = CAST(SUBSTRING(@pString, l.N1, l.L1) AS INT)
FROM cteLen l
WHERE ISNUMERIC(SUBSTRING(@pString, l.N1, l.L1))=1
;
User defined functionfor text strings
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
Author: Unknown
Create date:
Description: Returns a recordset from SSRS multi select parameter that has been passed into a stored procedure
(turns a comma seperated string into a records set)
Requirements: Paramater variable must be formed up into a comma deliminated string (or some other delimiter if the data
already contains commas)
Parameters can be passed in directly from SSRS often but some data types need to be massaged in SSRS
This is achieved in the SSRS dataset that is calling the sp by using the VB JOIN function
DS calling SP -> Properties -> Parameters
Add Parameter that is multi select -> add JOIN script to concat selected values with commas (or other char if data contains commas)
EG Param=-ResponsiblePartner Expr=JOIN(Parameters!ResponsiblePartner.Value,",")
Example of usage in SP
WHERE EMPLOYEE_CODE IN (SELECT * FROM DelimitedSplit8K(@RespPartners, ','))
INNER JOIN (SELECT * FROM DelimitedSplit8K(@RespPartners, ',')) AS t ON t.Item = s.CUST_CODE
NOTE: a useful bit of code to create a comma delimited string from a recordset
is below (useful place to leave it)
SELECT @RespPartners = COALESCE(@RespPartners + ',','') + EMPLOYEE_CODE
FROM _JWS_Responsible_Partners
Modifications: ALL MODIFICATIONS MUST BE DETAILED BELOW SHOWING DATE
Modified By: Gerard
Date: 28/08/15
Modification: Space in comma seperated string prevents match ie 'an1, dhp1' returns an1 while 'an1,dhp1' returns an1 and dhp1
Modified By:
Date:
Modification:
======================================================================================================================================
*/
ALTER FUNCTION [dbo].[DelimitedSplit8K]
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--SET @pString =REPLACE(@pString, ' ', '')
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)--10E+1 or 10 rows
,E2(N) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
,E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
,cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(REPLACE(@pString, ' ', '')),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
,cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(REPLACE(@pString, ' ', ''),t.N,1) = @pDelimiter
)
,cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,REPLACE(@pString, ' ', ''),s.N1),0)-s.N1,8000)
FROM cteStart s
)
SELECT ItemNumber,Item FROM
(
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item= SUBSTRING(REPLACE(@pString, ' ', ''), l.N1, l.L1)
FROM cteLen l
) as a
WHERE REPLACE(Item, ' ', '') <> ''
;
;
Example sp
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
TestOnly
======================================================================================================================================
Example Call
exec udspr_REPORT_Gerard_Test2 '1010,1011,2010,3013,4012'
*/
CREATE PROC [dbo].[sp_Gerard_Test2]
(
@CSVIn VARCHAR(8000) = NULL
)
AS
BEGIN
SELECT * FROM DelimitedSplit8K(@CSVIn, ',') AS wt
/*
Example of use in Inner Join
INNER JOIN (SELECT Item FROM DelimitedSplit8K(@PracticeGroup, ',')) AS wt ON (SomeTable.SomeField = wt.Item)
Example of most common use - IN
WHERE x.x IN((SELECT Item FROM DelimitedSplit8K(@PracticeGroup, ',')
*/
END
No comments:
Post a Comment