SQLServerCentral.com / SQL Server 2012 / SQL 2012 - General / multi-valued report parameter weirdness / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 10:48:15 GMT20RE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxHere's the whole function and stored procedure declaration etc, just in case someone else has this fun.ALTER FUNCTION [dbo].[fn_SplitStringList] (@StringList VARCHAR(MAX))RETURNS @TableList TABLE( StringLiteral VARCHAR(128))ASBEGINDECLARE @StartPointer INT, @EndPointer INTSELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)WHILE (@StartPointer &lt; LEN(@StringList) + 1) BEGIN IF @EndPointer = 0 SET @EndPointer = LEN(@StringList) + 1INSERT INTO @TableList (StringLiteral) VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer, @EndPointer - @StartPointer))))SET @StartPointer = @EndPointer + 1SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer)END -- WHILERETURNENDCREATE PROC GetBuildEvents(@BuildEventType VARCHAR(100))ASSELECT BuildSite.SiteName , House.LotNumber , House.HouseID , House.Homeowner , BuildDates.bhHouseID , BuildDates.HouseBuildDate , BuildDates.BuildEventTypeFROM House INNER JOIN BuildSite ON House.hBuildSiteID = BuildSite.SiteID INNER JOIN BuildDates ON House.HouseID = BuildDates.bhHouseIDWHERE BuildDates.BuildEventType IN (select * from [dbo].[fn_SplitStringList](@BuildEventType));SELECT BuildSite.SiteName , House.LotNumber , House.HouseID , House.Homeowner , BuildDates.bhHouseID , BuildDates.HouseBuildDate , BuildDates.BuildEventTypeFROM House INNER JOIN BuildSite ON House.hBuildSiteID = BuildSite.SiteID INNER JOIN BuildDates ON House.HouseID = BuildDates.bhHouseIDWHERE BuildDates.BuildEventType IN (select * from [dbo].[fn_SplitStringList](@BuildEventType));Wed, 18 Sep 2013 05:42:51 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxOh Super cool!!! Works a CHAMP!!! Definitely a good trick to learn! Thanks!Tue, 17 Sep 2013 21:01:16 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxIn case any other Reporting Services rookie comes across this, here's a really good article walking you through a solution and explaining what works and what doesn't and why.http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/(apologies to everyone reading my questions - I come from an Access background, so I'm used to the constructs available there. Hence my mashing up T-SQL and weird things like collections.Case closed, I hope!Mon, 16 Sep 2013 20:48:39 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxOh... must have missed that very subtle hint: TABLE-valued function. (So you query it, because it's a ... well, a TABLE!Thanks!Mon, 16 Sep 2013 10:26:17 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxTime to take a step back. You have:[code="sql"]BuildDates.BuildEventType IN (dbo.DelimitedSplit8K(@BuildEventTypes,','));[/code]This is the same as [code="sql"]BuildDates.BuildEventType = dbo.DelimitedSplit8K(@BuildEventTypes,',');[/code]But since DelimtedSplit8K is a table-valued function, this is not going to work out. You need to query the table-valued function:[code="sql"]BuildDates.BuildEventType IN (SELECT col FROM dbo.DelimitedSplit8K(@BuildEventTypes,','));[/code]You need to replace "col" with the actual column name used by the function (which I don't know by heart).Mon, 16 Sep 2013 01:28:55 GMTErland SommarskogRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxI'm trying (in vain) to use the DelimitedSplit8K function to pass a delimited list to my stored procedure to filter my underlying resultsetHere's my SQL[code="sql"]CREATE PROCEDURE BuildDatesForReport( @StartDate DATETIME, @EndDate DATETIME, @BuildEventTypes VARCHAR(80))ASBEGIN SELECT BuildSite.SiteName, House.LotNumber, House.HouseID, House.Homeowner, BuildDates.bhHouseID, BuildDates.HouseBuildDate, BuildDates.BuildEventType FROM House INNER JOIN BuildSite ON House.hBuildSiteID = BuildSite.SiteID INNER JOIN BuildDates ON House.HouseID = BuildDates.bhHouseID WHERE BuildDates.HouseBuildDate BETWEEN @StartDate AND @EndDate AND BuildDates.BuildEventType IN (dbo.DelimitedSplit8K(@BuildEventTypes,','));END[/code]If I leave out the @BuildEventType part, everything works fine.I was trying to follow this article [url]http://www.sqlservercentral.com/articles/T-SQL/73838/[/url] but there's something there I just don't get. DelimitedSplit8K returns a table, so shouldn't I just join to the table?Sorry to be so thick. There's just something here that I just goes right over my head!Thanks!PieterSun, 15 Sep 2013 22:17:34 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxErland,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 parameterSun, 15 Sep 2013 09:47:52 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxFiltering 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.Sat, 14 Sep 2013 02:54:21 GMTErland SommarskogRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxOkay, 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?Fri, 13 Sep 2013 22:04:27 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspx[quote][b]pietlinden (9/13/2013)[/b][hr]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![/quote]There are no arrays in T-SQL. There are no collections.[code="sql"]col IN (@Collection) [/code]is the same as [code="sql"]col = @Collection[/code]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.Fri, 13 Sep 2013 15:22:58 GMTErland SommarskogRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxIt 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. SmithDr. JonesDr. DaveBe 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.Fri, 13 Sep 2013 14:16:50 GMTScott Murray-240410RE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxSo I don't even need the DelimitedSplit functions for this, because that's what removing the "(0)" does. ... I think.Fri, 13 Sep 2013 13:55:46 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxThe dbo.DelimitedSplit8K function actually parses out the string and inserts each separate item into the temporary table.Fri, 13 Sep 2013 11:31:31 GMTScott Murray-240410RE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxScott,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!Fri, 13 Sep 2013 10:32:20 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxWith 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))ASBEGIN DECLARE @StartPointer INT, @EndPointer INT SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList) WHILE (@StartPointer &lt; 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 RETURNENDThen in stored proc with multivalue params we write the code like this, example:WHERE.....AND (ld.DESTCITY IN (select * from [dbo].[fn_SplitStringList](@DestCity)) Fri, 13 Sep 2013 06:03:46 GMTMichael_GarrisonRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxThe above are all good suggestions.. You may also want to take at look at:[url=http://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/]http://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/[/url]Fri, 13 Sep 2013 06:00:44 GMTScott Murray-240410RE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspx[quote][b]pietlinden (9/12/2013)[/b][hr]The weird thing is that if I used a stored procedure and tried to use IN(@MultivaluedParam)inside the stored procedure, it would fail.[/quote]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.[code="sql"]col IN (@x, @y, @z)[/code]is a shortcut for[code="sql"]col = @x OR col = @y OR col = @z[/code]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:[url=http://www.sommarskog.se/arrays-in-sql-2005.html]http://www.sommarskog.se/arrays-in-sql-2005.html[/url].Fri, 13 Sep 2013 01:01:45 GMTErland SommarskogRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxI 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!Thu, 12 Sep 2013 10:16:07 GMTpietlindenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxWell, 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.Thu, 12 Sep 2013 03:30:14 GMTtommy.tootenRE: multi-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxTo begin with, Profile the SQL and check whether the SQL is syntactically correct for the multi value parametersThu, 12 Sep 2013 01:51:49 GMTRaunak Jhawarmulti-valued report parameter weirdnesshttp://www.sqlservercentral.com/Forums/Topic1494028-2799-1.aspxSSRS 2012, SQL 2012I watched Brian Knight's video on filtering a report using a multi-valued parameter (here:[url]http://www.sqlservercentral.com/articles/Video/64369/[/url]), 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.[code="sql"]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);[/code]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!PieterThu, 12 Sep 2013 01:45:44 GMTpietlinden