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 Friday, September 13, 2013 2:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:21 AM
Points: 243, Visits: 2,708
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.



Post #1494727
Posted Friday, September 13, 2013 3:22 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: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
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
Post #1494745
Posted Friday, September 13, 2013 10:04 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 @ 10:14 AM
Points: 813, Visits: 5,172
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?
Post #1494776
Posted Saturday, September 14, 2013 2:54 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: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
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
Post #1494790
Posted Sunday, September 15, 2013 9:47 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 @ 10:14 AM
Points: 813, Visits: 5,172
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
Post #1494888
Posted Sunday, September 15, 2013 10:17 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 @ 10:14 AM
Points: 813, Visits: 5,172
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
Post #1494931
Posted Monday, September 16, 2013 1:28 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: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
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
Post #1494949
Posted Monday, September 16, 2013 10:26 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 @ 10:14 AM
Points: 813, Visits: 5,172
Oh... must have missed that very subtle hint: TABLE-valued function. (So you query it, because it's a ... well, a TABLE!

Thanks!
Post #1495180
Posted Monday, September 16, 2013 8:48 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 @ 10:14 AM
Points: 813, Visits: 5,172
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!
Post #1495356
Posted Tuesday, September 17, 2013 9:01 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 @ 10:14 AM
Points: 813, Visits: 5,172
Oh Super cool!!! Works a CHAMP!!! Definitely a good trick to learn! Thanks!
Post #1495744
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse