SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


multi-valued report parameter weirdness


multi-valued report parameter weirdness

Author
Message
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4846 Visits: 13163
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.

SELECT   x.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
WHERE BuildDates.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
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1631 Visits: 1944
To begin with, Profile the SQL and check whether the SQL is syntactically correct for the multi value parameters

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
tommy.tooten
tommy.tooten
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 68
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.
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4846 Visits: 13163
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!
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2111 Visits: 872
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Scott Murray-240410
Scott Murray-240410
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 3116
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/
Michael_Garrison
Michael_Garrison
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 2754
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))
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4846 Visits: 13163
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!
Scott Murray-240410
Scott Murray-240410
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 3116
The dbo.DelimitedSplit8K function actually parses out the string and inserts each separate item into the temporary table.
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4846 Visits: 13163
So I don't even need the DelimitedSplit functions for this, because that's what removing the "(0)" does. ... I think.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search