Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


multi-valued report parameter weirdness


multi-valued report parameter weirdness

Author
Message
Scott Murray-240410
Scott Murray-240410
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 3062
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.
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 866
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 '1,2,4,7' and col has the value '1,2,4,7' you will get a hit, else not.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 12479
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?
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

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

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 12479
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
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 12479
I'm trying (in vain) to use the DelimitedSplit8K function to pass a delimited list to my stored procedure to filter my underlying resultset

Here's my SQL

CREATE PROCEDURE BuildDatesForReport
( @StartDate DATETIME
, @EndDate DATETIME
, @BuildEventTypes VARCHAR(80)
)
AS
BEGIN
   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



If I leave out the @BuildEventType part, everything works fine.

I was trying to follow this article http://www.sqlservercentral.com/articles/T-SQL/73838/ 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!
Pieter
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 866
Time to take a step back. You have:

BuildDates.BuildEventType IN (dbo.DelimitedSplit8K(@BuildEventTypes,','));



This is the same as

BuildDates.BuildEventType = dbo.DelimitedSplit8K(@BuildEventTypes,',');



But since DelimtedSplit8K is a table-valued function, this is not going to work out. You need to query the table-valued function:

BuildDates.BuildEventType IN (SELECT col FROM dbo.DelimitedSplit8K(@BuildEventTypes,','));



You need to replace "col" with the actual column name used by the function (which I don't know by heart).

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 12479
Oh... must have missed that very subtle hint: TABLE-valued function. (So you query it, because it's a ... well, a TABLE!

Thanks!
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 12479
In 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!
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 12479
Oh Super cool!!! Works a CHAMP!!! Definitely a good trick to learn! Thanks!
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