CHAPTER 6 BUILDING REPORTSLet’s take a look at a parsing function that you will use in your stored procedure while working with
MVPs. Listing 6-4 defines the UDF called fn_MVParam. This function is in the Pro_SSRS database that
you have been using.
Listing 6-4. fn_MVParam, String-Parsing Function
CREATE FUNCTION dbo.fn_MVParam(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
This function, when called from your Emp_Svc_Cost_MVP stored procedure, will return the parsed
values from SSRS’s multivalued parameter selection and allow you to use this as criteria for selecting
data to include in the report. The key point of this function is that it uses several T-SQL functions itself,
such as CHARINDEX, LEN, and LEFT, to populate the @Values table with the individual items from your
report parameter string. The modification to the base Emp_Svc_Cost stored procedure shown in Listing
6-5 will be required to make the Emp_Svc_Cost_MVP stored procedure effectively work with the MVPs.
Listing 6-5. Modification to WHERE Clause for MVP
1 = CASE WHEN CAST(DATEPART(YYYY, ChargeServiceStartDate) AS VARCHAR(20)) IN
(SELECT [PARAM] FROM fn_MVParam(@ServiceYear, ',')) THEN 1
ELSE 0
END
AND
1 = CASE WHEN CAST(DATEPART(MM, ChargeServiceStartDate) AS VARCHAR(20)) IN
(SELECT [PARAM] FROM fn_MVParam(@ServiceMonth, ',' )) THEN 1
ELSE 0
END
Notice that instead of saying IN (@Year), for example, which will not work, you are calling your
function fn_MVParam. The function takes two values: the string and the delimiter. In this case, you are
using a comma as the delimiter.
When the report is run and the new function is called, you can see that you can select one, two, any
combination, or all values from the populated drop-down, and you know that your stored procedure will
effectively handle the parsing, evaluating, and criteria to deliver only the data that you want to see in the
report, as shown in Figure 6-32.