Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

multi-valued report parameter weirdness Expand / Collapse
Author
Message
Posted Thursday, September 12, 2013 1:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:32 PM
Points: 891, Visits: 5,707
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
Post #1494028
Posted Thursday, September 12, 2013 1:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
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
Post #1494030
Posted Thursday, September 12, 2013 3:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 21, 2014 5:49 AM
Points: 10, Visits: 61
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.
Post #1494068
Posted Thursday, September 12, 2013 10:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:32 PM
Points: 891, Visits: 5,707
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!
Post #1494275
Posted Friday, September 13, 2013 1:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 817, Visits: 743
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
Post #1494474
Posted Friday, September 13, 2013 6:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:27 AM
Points: 243, Visits: 2,737
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/
Post #1494552
Posted Friday, September 13, 2013 6:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:09 AM
Points: 1,141, Visits: 2,489
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))

Post #1494553
Posted Friday, September 13, 2013 10:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:32 PM
Points: 891, Visits: 5,707
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!
Post #1494662
Posted Friday, September 13, 2013 11:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:27 AM
Points: 243, Visits: 2,737
The dbo.DelimitedSplit8K function actually parses out the string and inserts each separate item into the temporary table.
Post #1494683
Posted Friday, September 13, 2013 1:55 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:32 PM
Points: 891, Visits: 5,707
So I don't even need the DelimitedSplit functions for this, because that's what removing the "(0)" does. ... I think.
Post #1494721
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse