multi-valued report parameter weirdness

  • SSRS 2012, SQL 2012

    I watched Brian Knight's video on filtering a report using a multi-valued parameter (here:http://www.sqlservercentral.com/articles/Video/64369/), and after seemingly forever, I got it to work. The weird thing is that if I used a stored procedure and tried to use

    IN(@MultivaluedParam)

    inside the stored procedure, it would fail.

    Here's the signature of the stored procedure:

    ALTER PROC [dbo].[uspHouseBuildInfo]

    @FromDate DATETIME,

    @ToDate DATETIME,

    @BuildEventType VARCHAR(60)

    AS

    ...

    The only way I could get it to work was to copy the entire select statement from the stored procedure into the DataSet's query property, which I thought was odd. Yes, I know I could write the query better, but here it is.

    SELECTx.HouseID

    ,x.Homeowner

    ,x.StartDate

    ,x.hBuildSiteID

    ,x.SiteName

    ,x.HouseBuildDate

    ,x.BuildDayNo

    ,x.BuildMonth

    ,x.BuildWeek

    ,x.BuildEventType

    FROM

    (

    SELECT h.HouseID, h.Homeowner, h.StartDate, h.hBuildSiteID, bs.SiteName, BuildDates.HouseBuildDate

    , ROW_NUMBER() OVER (PARTITION BY h.HouseID ORDER BY [HouseBuildDate]) AS BuildDayNo

    , Month(HouseBuildDate) AS BuildMonth

    , FLOOR((ROW_NUMBER() OVER (PARTITION BY h.HouseID ORDER BY [HouseBuildDate])+1)/2) AS BuildWeek

    , CASE

    WHEN FLOOR((ROW_NUMBER() OVER (PARTITION BY h.HouseID ORDER BY [HouseBuildDate])+1)/2) = 1 THEN 'Framing'

    WHEN FLOOR((ROW_NUMBER() OVER (PARTITION BY h.HouseID ORDER BY [HouseBuildDate])+1)/2) = 2 THEN 'Insulation & Siding'

    WHEN FLOOR((ROW_NUMBER() OVER (PARTITION BY h.HouseID ORDER BY [HouseBuildDate])+1)/2) = 3 THEN 'Paint & Trim'

    WHEN FLOOR((ROW_NUMBER() OVER (PARTITION BY h.HouseID ORDER BY [HouseBuildDate])+1)/2) = 4 THEN 'Landscaping & Hardware'

    END AS BuildEventType -- end case statement

    FROM House AS h INNER JOIN

    BuildSite AS bs ON h.hBuildSiteID = bs.SiteID INNER JOIN

    BuildDates ON h.HouseID = BuildDates.bhHouseID

    WHEREBuildDates.HouseBuildDate BETWEEN @FromDate AND @ToDate

    ) x

    WHERE x.BuildEventType IN (@BuildEventType);

    originally, I had the whole query designed as a stored procedure, and just based the report on that - until no matter what I tried, the multi-valued parameter would cause the query to fail. Okay, enough repeating myself!

    My question is this:

    Did I do something wrong in my stored procedure, or why dd this not work when I tried to pass a multi-valued parameter to my dataset? The [ ] multi-values property of the parameter is checked.... so what did I miss?

    Thanks!

    Pieter

  • To begin with, Profile the SQL and check whether the SQL is syntactically correct for the multi value parameters

    Raunak J

  • Well, SSRS is cheating a bit to make this possible when you are using multi valued parameters.

    Here is an example how to work with it. http://sqlblogcasts.com/blogs/simons/archive/2007/11/22/RS-HowTo---Pass-a-multivalue-parameter-to-a-query-using-IN.aspx.

    An other option is to use the JOIN function when passing the parameter to the stored procedure, which is described over here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26/reporting-services-multi-value-parameter.

  • I was considering playing with JOIN (already used it to show the filter as a comma-separated list)... I'll give it a whirl and see what happens. The funny part is that if I push everything to SSRS, it works. So I guess, like the articles said, it's doing voodoo under the covers.

    I guess I'll explore more and try and learn what's really going on by testing until I finally understand it.

    Thanks!

  • pietlinden (9/12/2013)


    The weird thing is that if I used a stored procedure and tried to use

    IN(@MultivaluedParam)

    inside the stored procedure, it would fail.

    That should work. That is, if you have a column of which the value is equal to @MultivalueParam, you will get a hit. Else not. And if the column is an integer column and @MultivaluedParam is a string, the risk for a conversion error is considerable.

    col IN (@x, @y, @z)

    is a shortcut for

    col = @x OR col = @y OR col = @z

    If that @MultivaluedParam is something like a comma-separated string of values and you want hits on individual values, you need to crack into table format - although it would be a lot easier to use a table-valued parameter to start with. Anyway, this article on my web site gives your plenty of methods to crack that string:

    http://www.sommarskog.se/arrays-in-sql-2005.html.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • The above are all good suggestions.. You may also want to take at look at:

    http://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/[/url]

  • With Stored Procs we have found that you have to use a split string seperation function. Thats why when you took the actual select code and put it in the stotred proc, that worked. This is what our company does:

    From the Internet (dont know who author is) we use this:

    ALTER FUNCTION [dbo].[fn_SplitStringList]

    (

    @StringList VARCHAR(MAX)

    )

    RETURNS @TableList TABLE( StringLiteral VARCHAR(128))

    AS

    BEGIN

    DECLARE @StartPointer INT, @EndPointer INT

    SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)

    WHILE (@StartPointer < LEN(@StringList) + 1)

    BEGIN

    IF @EndPointer = 0

    SET @EndPointer = LEN(@StringList) + 1

    INSERT INTO @TableList (StringLiteral)

    VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer,

    @EndPointer - @StartPointer))))

    SET @StartPointer = @EndPointer + 1

    SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer)

    END -- WHILE

    RETURN

    END

    Then in stored proc with multivalue params we write the code like this, example:

    WHERE.....

    AND (ld.DESTCITY IN (select * from [dbo].[fn_SplitStringList](@DestCity))

  • Scott,

    thanks for that. I'm pretty sure I read your article before doing this... that's maybe where some of the idea came from.

    One question though... when you remove the subscript from the array, does it act like a collection or something and you can just use IN(@Collection)??? Just trying to understand how it works!

    Thanks!

  • The dbo.DelimitedSplit8K function actually parses out the string and inserts each separate item into the temporary table.

  • So I don't even need the DelimitedSplit functions for this, because that's what removing the "(0)" does. ... I think.

  • It all depends on what you are passing from SSRS to the stored procedure (which is different that what is passed if you are using straight SQL in SSRS... if you are not using stored procedures, SSRS handles the parsing). For instance, say you are allowing the report users to select physicians in a parameter, and they are allowed to select multiple physicians for their report. The parameter values gets passed as follows:

    "Dr. Smith, Dr. Jones, Dr. Dave"

    all as a single string. The I noted in the Stored Procedure that the delimiter function actually parses the values into:

    Dr. Smith

    Dr. Jones

    Dr. Dave

    Be sure though, that when you add the parameter to the stored procedure it says:

    Parameters!pLookupType.Value

    and

    NOT Parameters!pLookupType.Value(0) -- this instance will only grab the 1st value in the parameter array.

  • pietlinden (9/13/2013)


    One question though... when you remove the subscript from the array, does it act like a collection or something and you can just use IN(@Collection)??? Just trying to understand how it works!

    There are no arrays in T-SQL. There are no collections.

    col IN (@Collection)

    is the same as

    col = @collection

    That is, if @Colletion has the value[font="Courier New"] '1,2,4,7' [/font]and col has the value [font="Courier New"]'1,2,4,7'[/font] you will get a hit, else not.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Okay, I think I have it now... one option is to filter all the "required" and single-value parameters in the stored procedure, and then filter the multi-value parameters in the report. For now.

    Thanks!

    (Is there a really good book on SSRS that's not too simple?) Brian Larson's book is a really good absolute beginner book... what would you recommend after that?

  • Filtering in the report is probably OK, if that filter only removes a smaller set. But if the filter removes one million rows of one million two hundred, that's really bad.

    And I don't see why you would filter in the report. The best would be if SSRS would permit you to use a table-valued parameter, but maybe it can't. (I'm completely ignorant about SSRS.) But else if it gives you a comma-separated list, send that to the procedure and crack into table format; there are several links for this in the thread already.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland,

    That's what I was originally thinking - if filtering in SSRS only removes a few records, that's one thing, but if it removes potentially millions - why not remove them earlier? Hence the original question. I think the answer may be to use a table-valued parameter

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply