September 12, 2013 at 1:45 am
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
September 12, 2013 at 1:51 am
To begin with, Profile the SQL and check whether the SQL is syntactically correct for the multi value parameters
Raunak J
September 12, 2013 at 3:30 am
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.
September 12, 2013 at 10:16 am
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!
September 13, 2013 at 1:01 am
pietlinden (9/12/2013)
The weird thing is that if I used a stored procedure and tried to useIN(@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]
September 13, 2013 at 6:00 am
September 13, 2013 at 6:03 am
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))
September 13, 2013 at 10:32 am
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!
September 13, 2013 at 11:31 am
The dbo.DelimitedSplit8K function actually parses out the string and inserts each separate item into the temporary table.
September 13, 2013 at 1:55 pm
So I don't even need the DelimitedSplit functions for this, because that's what removing the "(0)" does. ... I think.
September 13, 2013 at 2:16 pm
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.
September 13, 2013 at 3:22 pm
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]
September 13, 2013 at 10:04 pm
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?
September 14, 2013 at 2:54 am
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]
September 15, 2013 at 9:47 am
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