in

ASP.NET Weblogs

Ian Stallings: web log

official chicken™

SQL Server Reporting Services - No Multi-Value Params?

After using Microsoft's SQL Server Reporting Services for a day or so I realized that there is no easy way to pass in multiple values for a parameter to the service and have it return the results. For instance, if I want to return a report where the variable is equal to 33, 34, and 35 there is no easy way to do it.

I use these multiple values in my SQL query to narrow my results using an Where variable IN(33,34,35) type of clause in my SQL statement.

Looking at the documentation I did run across a property called ReportParameter.MultiValue Property, which is a boolean and always set to false. So that's not useful at all.

/sarcasm
I guess I can understand why MS didn't include this obviously advanced feature. It would require them to write a very intense parameter parsing function and that would probably add at least 50 years onto the product development time.
/sarcasm

Now I apparently have to write my own user defined function for the Reporting SQL server and then use that in my script that defines the Dataset.We'll see how that goes.

So basically, instead of spending time focusing on the problem I have to focus on a weakness in the tool. This is like a carpenter trying to figure out how to use duct tape to get his hammer working properly instead of building a house. This pisses me off. I spend more time working around tools then using them to create something useful. If developers could get some decent tools we might be able to actually give the user's what they need.




Comments

 

Jesse Arnold said:

SQL 2005 Reporting Services supports this. Until then, there is not a simple work around.
March 31, 2005 9:43 PM
 

Roman Rehak said:

Yeah, I know it sucks. From what I've heard, the version 1 was written by a small team, in a very short timeframe and some features didn't make it based on how critical they were considered to be.
March 31, 2005 10:31 PM
 

TrackBack said:

April 1, 2005 10:11 AM
 

TrackBack said:

April 1, 2005 10:14 AM
 

Scott Allen said:

It's one of those features that is difficult to implement for 'general purpose' use, because of different data types (as your follow up post indicates), and without screwing up optimizations - I'll be suprised if the SQL 2005 feature will meet everyone's expectations.

It's just not as easy as passing a comma delimited string to the query.
April 9, 2005 1:10 PM
 

Ian Stallings said:

Yes I understand it may be a bit more complicated than that, but I know it's not _that_ complicated. I might have been a bit unfair to the development team, I don't know the constraints they work under. I just think this feature is one of those you don't skip over for the sake of time. But that's just my opinion.

April 14, 2005 10:38 AM
 

Choi Ng said:

Report Services 2005 let you provide multi-valur for your report parameter.

In your t-sql you should have

Select   * from yr_table

WHERE     (yr_parameter IN (@yr_parameter))

Reprot Services 2000 don't have the property to let you select the multi-value, but you create a stored procedure to work around

June 23, 2008 2:58 PM

Leave a Comment

(required)  
(optional)
(required)  
Add