Rather Ripped (Select All in Service Pack 1)

re: Rather Ripped (Select All in Service Pack 1)

furmangg — Tue, 15 Aug 2006 20:30:47 GMT

This hotfix will put back the "select all" option within Report Builder, too, right?

re: Rather Ripped (Select All in Service Pack 1)

bwelcker — Thu, 17 Aug 2006 02:01:38 GMT

Yes, this change is for both Report Manager as well as Report Builder.

re: Rather Ripped (Select All in Service Pack 1)

UT — Thu, 17 Aug 2006 03:10:09 GMT

Hi Brian,
I have a report with stored procedure as my main dataset with one parameter. I use multi-valued parameter as my parameter and pass it into stored procedure. My stored procedure has simple query statement as below:
SELECT name FROM customer WHERE country IN (@country)
When I previewed the report, it displayed a blank report. That's what happen when I select all countries and it works fine if I only select one country. I reckon that the multi-valued parameter return as a single line without comma-delimited ('Australia, Indonesia'), while we expecting ('Australia', 'Indonesia').
What do you think? Did I miss something in here?

Thanks n Regards,
UT.

re: Rather Ripped (Select All in Service Pack 1)

bwelcker — Fri, 18 Aug 2006 08:55:54 GMT

Stored procedures do not support muti-valued parameters. You could do something like concatenate the values together and pass them as a single string and then break them apart inside the proc.

re: Rather Ripped (Select All in Service Pack 1)

Lior — Sun, 20 Aug 2006 17:59:49 GMT

"SAVED!"

That’s what I thought when I saw this post :)

The thought of rebuilding all those Report Builder reports from scratch to support some kind of workaround kept me up at nights and without energy at day.

SAVED!

re: Rather Ripped (Select All in Service Pack 1)

Jim Evans — Wed, 20 Sep 2006 13:12:52 GMT

Why not implement something like this.

Allow "Select All" to pass a NULL value for the parameter to the sproc. Then write the sproc like this:

-- Parameter declaration
@Country varchar(3000) = NULL

WHERE ((@Country IS NULL) OR (table.counrty IN(@Country )))

re: Rather Ripped (Select All in Service Pack 1)

gbn — Thu, 21 Sep 2006 20:34:44 GMT

WHERE ((@Country IS NULL) OR (table.counrty IN(@Country )))

SQL will not evaluate this.
It will not parse the string.
It will look effectively do table.counrty = "1,2,3,4"

re: Rather Ripped (Select All in Service Pack 1)

Eric — Mon, 25 Sep 2006 01:05:34 GMT

We've actually implimented our own logic where OUR "Select All" does pass a null. Will we be able to turn the default "Select All" feature off?

re: Rather Ripped (Select All in Service Pack 1)

TeeNah — Mon, 25 Sep 2006 20:25:47 GMT

Quick note - the KB article is now public:
http://support.microsoft.com/?id=919478

re: Rather Ripped (Select All in Service Pack 1)

Jim Johnson — Tue, 26 Sep 2006 21:40:56 GMT

Regarding passing a multi-value parameter into a stored procedure, what you are passing is this 'value1 ,value2, value3'. In the stored procedure this will split out the parameter into a table, which you then use in the sproc:
CREATE PROCEDURE [dbo].[something]
(@code varchar(1024) )

declare @temp table(Code varchar(3) primary key)

If IsNull(@code ,'') = ''
BEGIN
Set @code = 'ALL'
END
ELSE
WHILE LEN(@code ) > 0
BEGIN
IF PATINDEX('%,%', @code ) > 0
BEGIN
INSERT INTO @temp VALUES(RTRIM(SUBSTRING(@code ,1,PATINDEX('%,%', @code )-1)))
SET @code = LTRIM(SUBSTRING(@code ,PATINDEX('%,%', @code )+1,1024))
END
ELSE
BEGIN
INSERT INTO @temp VALUES(LTRIM(RTRIM(@code )))
BREAK
END
END

re: Rather Ripped (Select All in Service Pack 1)

Data Wizard — Wed, 27 Sep 2006 01:22:57 GMT

Well done for putting your hand up and admitting a mistake, but if you want my opinion the entire suite of products SSRS, SSAS etc is just too damn confusing.

SQL 2005 just looks and feels like it was designed by a committee.

The learning actually should be that it is FAR HARDER to build something SIMPLE than it is to build something that WORKS...

This written on the day we decided to go with what we have in SQL and forget SSRS...

re: Rather Ripped (Select All in Service Pack 1)

bwelcker — Thu, 28 Sep 2006 19:25:55 GMT

I'm sorry that you find the product confusing. While SQL 2005 was not designed by "committee", it was designed by a large development team and addresses a very broad set of scenarios. It is hard to develop products that are best in class (best reporting engine, best relational engine, best OLAP engine) as well as seamlessly integrated. You often make tradeoffs between simplicity and flexibility. I think if you told users who are familiar with competitive reporting tools whether SSRS is hard to use, they would not all agree. Anyway, we will continue to work on usability and hope that you will try it again in the future.

re: Rather Ripped (Select All in Service Pack 1)

Jason — Thu, 05 Oct 2006 21:15:48 GMT

Stored procedures do not support muti-valued parameters. You could do something like concatenate the values together and pass them as a single string and then break them apart inside the proc. That's not totally true: http://msdn.microsoft.com/msdnmag/issues/06/06/datapoints/default.aspx#S5 Using a (custom-written) split function as explained in the provided link will allow you to use multi-values with stored procedures. Make sure to set the command type to storedproc.

re: Rather Ripped (Select All in Service Pack 1)

Karim Sameh — Thu, 12 Oct 2006 10:38:49 GMT

That's really great I was getting confused , because on our testing machine (which we had not installed the SP1 on it), it was there and our client saw it like that and he was using the select all option, but when deploying on life server (with SP1 on it) we couldn't find it ????

However , i still can not find the HotFix, I found the KB page but is the only way to get the Hotfix is to send an E-mail to Microsoft support ????!!!!!!!

re: Rather Ripped (Select All in Service Pack 1)

Jerry — Thu, 12 Oct 2006 21:22:10 GMT

Karim

That IS the correct behaviour and the exact problem

SP1 removes the "Select All" option

Yes, the only way thus far (before SP2 comes out) is to call Microsoft Support (I did, spent 30 mins getting it, FREE of charge as it should) ~ 300MB download

http://support.microsoft.com/kb/919478

re: Rather Ripped (Select All in Service Pack 1)

Trevor Howe — Thu, 26 Oct 2006 15:31:24 GMT

Hi Brian

I am both glad and sad to see that there are a lot of issues around parameters in RS2005. Glad because I see that I am not the only sucker who spends endless hours searching the internet for the solution to what appears to be a simple problem and sad because it adds a level of frustration to what is otherwise a great product (not only RS but the whole SQL 2005 suite).

I am primarily using MDX to supply the data for my reports. I have moved away from using too many parameters in the main MDX mainly due to the 4**** tuples error message and also because the stupid things kept falling over when I made a change during development. What I am doing now is using SQL queries run against my datamarts to supply the list values and default values for my parameters. I have more control using this method, especially when I want to filter a paramter list for example to show me the dimension values that were valid at a particular point in time (Type 2 slowly changing dimensions). I can also default a month parameter to the latest month that has been loaded into a particular datamart table. Then instead of filtering the MDX query, I let a larger dataset come back and then filter the table using the parameter value. This might not be so good for larger datasets, but I seem to be fine for now.

Multi value parameters are great, but I see that the Select All functionality has been removed only to be promised tom make a triumphant return in the next service pack. Even then, I dont think this will solve problem. As an example, I have one parameter that allows the user to select one or many regions from a region dimension table. I use the method above to populate the dataset with only values that were during the month of my "month" parameter value. The problem comes when I do the default value setting. It is a multi value parameter so I specify the same query as the available values query. So the parameter defaults to all Regions. Great so far, but here is my next problem. Because most of my reports are printable, I have to create a number of text fields in the header of my report to display the parameters that were selected. So I use the Join function to display the parameter values (or labels) for the region. I have about 20 regions in the datamart, so you can imagine how busy the text box is to start with. What I would really like is for my expression to detect that all regions have been selected and then display the text "All" or something similar.

Thats my griping over, but I seriously think MS must put in a bit of extra work related to parameters or at the very lease for someone to accumulate all the parameter gripes and publish a white paper of work arounds to known bugs related to parameters. Which reminds me of another gripe. Why, when you make a change in the MDX designer, does some parameters automatically change back to multi value when I only want a single value select, like in the case of my month selection.

There I feel much better now. Eagerly awaiting responses from my fellow "parameter challenged" RS colleagues

SQL Server 2005 SP 2 « Charlie Maitland’s Blog

SQL Server 2005 SP 2 « Charlie Maitland’s Blog — Fri, 27 Oct 2006 11:35:10 GMT

PingBack from http://charliem.wordpress.com/2006/10/27/sql-server-2005-sp-2/

re: Rather Ripped (Select All in Service Pack 1)

vince — Wed, 01 Nov 2006 16:54:38 GMT

Brian -

I am looking for a stable and usable multi-select dropdown for C#.Net much like the one in RS 2005 for multi-value parameters. Does Microsoft provide this as a custom control? If not, can you point me in the right direction on any info?

thanks

re: Rather Ripped (Select All in Service Pack 1)

Vince Bossio — Tue, 07 Nov 2006 18:46:06 GMT

Is there away to create multi-select dropdowns such as those used in Report Manager for other pages in a Visual Studio .NET project?

In other words, does MS make the custom control used for multi-select dropdowns available for developers to use elsewhere?

re: Rather Ripped (Select All in Service Pack 1)

W Stuart — Tue, 07 Nov 2006 21:22:36 GMT

The simple solution is to use:

Join(Parameter!PARMLIST_IN!Value,"','") when you define the stored procedure parameter "Value".

This places a single quote around each "," so your parameter list looks like: X1','X2','X3.

Then in the stored procedure use:

select * from X where parmlist in ('||PARMLIST_IN||')

This then creates the "in" PARMLIST_IN" to look like 'X1','X2','X3'.

re: Rather Ripped (Select All in Service Pack 1)

Gerhard — Thu, 30 Nov 2006 15:54:52 GMT

Hi Brian,

I am using SP2.

Is there realy no option to turn "Select All" off.

Performance is bad.

Best Regards

Gerhard

re: Rather Ripped (Select All in Service Pack 1)

Simon Elms — Thu, 28 Dec 2006 05:15:18 GMT

I hope when SP2 comes out that there is some switch that can hide/remove "Select All" in a multivalue parameter list. While it is definitely useful in short lists, it'd be a nightmare in long lists populated from a database query.

I'm currently designing a report where the list for one of the parameters can have several hundred items in it. Originally designed prior to SP1, if anyone clicked the "Select All" option it would run like a dog. Now, under SP1, I've added an 'ALL' value to the query the list is based on. I've used the JOIN and SPLIT functions suggested in prior comments above so I can pass a comma-separated list of values to a stored procedure which produces the recordset the report uses. Much more efficient than the old "Select All".

Now I'm not looking forward to SP2 if it means my list will now have "Select All" as well as my custom "[Select All Warehouses]" (which has the value 'ALL').

The one problem with a custom "[Select All Warehouses]" (or similar) is that it doesn't have the same behaviour as "Select All". If a user selects one or more items, then selects the "[Select All Warehouses]" item, the other items aren't cleared. Does anyone know if there is a parameter list click event, or something similar, that I could handle to clear all other items in the list when my custom "[Select All Warehouses]" is selected?

re: Rather Ripped (Select All in Service Pack 1)

Tony Rogerson — Tue, 02 Jan 2007 14:04:07 GMT

Unaware of this Select All problem I've done all my dev on SP1 and now I'm trying out SP2 I see I have no way of disabling this system generated option.

This is crazy.

I use stored procedures and pass CSV and split it out in the proc into dynamic SQL or a set and do a join.

This "feature" needs a method of disabling it's appearance; we don't want a huge IN clause, I don't even want an IN clause period if I don't need to join - it just causes work and for complex SQL which is what I am doing the performance hit is too great.

If you can find time to put other features into these service packs then why can't you put a simple option in to allow us to control this Select All appearance?

I've raised a bug on connect if anybody would like to vote....

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249227

re: Rather Ripped (Select All in Service Pack 1)

Chris McGuigan — Tue, 23 Jan 2007 03:06:56 GMT

SP2 - Return of ALL, Noooo!!!!!

'ALL' is to return to parameter drop downs in SP2, in exactly the same way i existed prior o SP1.

I've already spent time fixing reports because of the removal of 'all' in SP1. I know it was for a valid reason and my changes made the query more efficient when 'ALL' was selected. But now you're reverting back with SP2!

I think you should make an exception to the rule about functionality change in an SP here. At least make it optional. Can you tell from the report def, exactly which version it was created in, if its not SP'd default to that versions behaviour. If it's SP1, default it not to have auto 'ALL' if it's brand new - default it to Yes if that's the consensus.

You made an exception in RS2000 SP2(?) when you added the print functionality which was fine.

Cheers

Chris

re: Rather Ripped (Select All in Service Pack 1)

bwelcker — Mon, 05 Feb 2007 01:49:19 GMT

I understand all of the angst around this decision. We will definitely make this configurable in the future, either on a server level or a per-report level.

re: Rather Ripped (Select All in Service Pack 1)

Eisen Scherrer — Thu, 01 Mar 2007 00:53:37 GMT

I would prefer server level.

re: Rather Ripped (Select All in Service Pack 1)

sam — Sat, 03 Mar 2007 04:53:01 GMT

Here is how I built my param list:

SELECT * FROM

(

SELECT Fieldname FROM table WHERE yada yada )

UNION

(

SELECT ' <SELECT ALL>' AS Fieldname FROM table WHERE recodid = 1

)

) BINS

ORDER BY BINS. BINADDR

And this is in the WHERE criteria of the report query:

AND (BIN.BINADDR IN (:Bin) OR ' <SELECT ALL>' IN (:Bin) )

Its a little cheezy but it works.

Question for the masters here:

I need to use a very large parameter list such as customer list, product list where there are thousands of records. Is it possible that yall can make the dropdown lists for parameters MUCH larger in the report manager. It is pretty frustrating to to have screen resolution of 1200 x 1200 and then have this little tiny list to work from showing you six of the three thousand or so records you have to select from. Also, search as you type would be most helpful, auto-fill-in for the address bar in IE.

re: Rather Ripped (Select All in Service Pack 1)

Chris McGuigan — Tue, 20 Mar 2007 21:50:56 GMT

Hi Brian,

I'm coming in a bit late on this thread but just so you get some feedback.

I'm not whining about this reversal on the 'All' feature - I've done that on the MS forum a while ago ;-)

But it does mean that I will not be updating to SP2 because this one thing has such a significant impact in so many reports.

Now I'm use to it, I can really see the sense of taking the feature out in SP1. It really allows us to implement an 'All' option as efficiently as possible. However, I see the flipside, whilst I am an accomplished SQL programmer, many are not and would probably find implementing this themselves quite a task.

Cheers

Chris

re: Rather Ripped (Select All in Service Pack 1)

bwelcker — Wed, 21 Mar 2007 10:03:07 GMT

Chris, I understand your reluctance to update to SP2 because of the change. It was a very hard decision and in the end, we went with the direction that we thought most people would be happy with. I hope that we can provide a more granular choice in the future.

The curious saga of “(Select All)” and multi-valued parameters in Reporting Services

Russell Christopher's Semi-Useful BI Musings — Mon, 09 Apr 2007 21:15:33 GMT

The back- story: Once upon a time, when Reporting Services 2005 was just barely gold, one was able to

Select All option for Multi-valued parameters

My thoughts — Fri, 20 Apr 2007 23:02:41 GMT

The 'Select All' option in the multi-valued parameter is again back with SP2. Yeah...it was released

re: Rather Ripped (Select All in Service Pack 1)

Lasantha — Mon, 23 Apr 2007 09:37:15 GMT

Hi all,

Finally can any one tell how to pass multi value parameters to a stored procedure. example would be great.

Thanks in advance

re: Rather Ripped (Select All in Service Pack 1)

Jan Morten — Thu, 10 May 2007 10:06:53 GMT

Is it possible to hide (select all)? I want to do that because when we have large drop down lists the performance is not very good.

Thanks!

Jan Morten

At the End of Paths Taken (Modifying Where Clause when All Parameter Values are Selected)

Direct Reports (Brian Welcker's Weblog) — Tue, 22 May 2007 19:27:55 GMT

With the return of “Select All” in Service Pack 2, all multi-value parameters allow the user to pick

re: Rather Ripped (Select All in Service Pack 1)

anaylor01 — Tue, 25 Dec 2007 23:41:38 GMT

When I try to use Select All I get This "Incorrect syntax near ','

SSRS: errores en selecci?n de par?metros (SP2) | hilpers

SSRS: errores en selecci?n de par?metros (SP2) | hilpers — Tue, 20 Jan 2009 22:53:02 GMT

PingBack from http://www.hilpers-esp.com/408018-ssrs-errores-en-seleccion-de