Gerard's codebase

This is a personal repository of code snippets and information.

Over the years I have generated lots of little sub programs and stored away acres of useful code snippets. The problem is always to locate them.

Even more time wasting is forgetting how to do simple things when you havnt developed in an environment for a few years (or even a few months)

My new years resolution is to start putting them up in a common place as I produce them. (thanks google)

They are handy for me and, with a bit of a clean up and documentation, they might be handy for others if they wander in here.

Gerard 2008

Wednesday, March 11, 2015

Passing Multi-Value Parameters In Reporting Services

Here is a quick and efficient way to pass and use multi value parameters in SSRS and your stored procedures or SQL.

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 ON
GO
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 ON
GO
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: