Gerard's codebase: March 2015

You have a number of options for formatting text in SSRS. Here is a good article on formatting options for text boxes. The step by step HTML section is below. Thanks SQLchick,

http://www.sqlchick.com/entries/2010/10/31/using-different-formats-within-a-single-textbox-in-ssrs.html

HTML Tags

This option delivers a bit more flexibility, particularly if you have a complex expression in your textbox. Using HTML tags will work if you have a complex expression (i.e., shown in the design view).
First, you do need to define Placeholder Properties on the Expression:

SplitFormatting_PlaceholderPropertiesForExpr

Within the Placeholder Properties, change the default Markup type to be “HTML – Interpret HTML tags as styles.” If you forget to change this radio button, then the html tags will be rendered as literal text.

SplitFormatting_PlaceholderPropertiesHTMLRadioBtn

Then within your expression, insert the HTML tags as needed.

Only a subset of HTML tags are supported within SSRS, such as bold, italics, and underline. The MSDN page on Formatting Text and Importing HTML specifies the valid HTML tags you may use within an SSRS textbox. If you use an HTML tag that isn’t supported in SSRS, it will be ignored.
As a sidenote, using HTML tags within a simple expression will work as well:

However, in a real situation, I would reserve using HTML tags to situations when I have a complex expression. With a simple expression, I’d opt to keep the formatting options simpler.

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

Gerard's codebase