Multivalue Parameter SQL Server 2005 SP1

Hello, I've installed SP1 for SQl Server 2005 and I noticed that the option "Select all" on a multi value parameter drop down (using web browser) is missing. Even if I can see it on VS 2005.....
Anyone can help me?
Thank you very much.
I rely on the 'Select All' capability as well. How can I get it back now that I've upgraded to SP1?|||

I finally found the answer on this. SP1 removes the 'Select All' capability previously applied when choosing the 'multi-value' option on a parameter list. Could not easily find this in the Release Notes.

I finally found it by looking through the updated version of the 'Books Online'. I must admit I understand the logic behind changing things here and the revised approach will make for more efficient queries (at the expense of recoding) - but it would be nice to know things prior to installing the SP - not after.

FYI - if, like me you develop on one system, and deploy to another, you can continue to develop on 2005/SP1, deploy to a 2005/non-SP1 system and things will work as before. Once you update the deployment target system to 2005/SP1 - the option is gone and you'll have to go back and do the parameter dataset recoding as outlined in the updated Books Online.

Bob

|||

Where did you find this information in Books Online? Can you provide a link? I'd like to take a closer look, because we also use this feature (which is admittedly funky) extensively.

Joe

|||

There is updated BOL contents available on MSDN which explains how to change SQL-based queries to simulate the "ALL" element: http://msdn2.microsoft.com/en-us/library/ms155917.aspx (scroll to the section about "Adding an All Member to a Multivalue Parameter").

-- Robert

|||

i believe there is also a hotfix available for this problem

|||

Do you have a link to the hotfix?

michael

|||http://support.microsoft.com/kb/918222|||

Thank you very much.

Michael

|||

Hello,

I faced the problem that on my development server <select all> was displaying and on the production it was not due to SP2 on development compared to SP1 on production.

SP2 was then loaded on the production server and rebooted the machine even then the <select all> cannot be seen. Would i need to reload the report or any futher fixes.

I know I am close to reaching a solution

Thanks

Multivalue Parameter SQL Server 2005 SP1

Hello, I've installed SP1 for SQl Server 2005 and I noticed that the option "Select all" on a multi value parameter drop down (using web browser) is missing. Even if I can see it on VS 2005.....
Anyone can help me?
Thank you very much.I rely on the 'Select All' capability as well. How can I get it back now that I've upgraded to SP1?|||

I finally found the answer on this. SP1 removes the 'Select All' capability previously applied when choosing the 'multi-value' option on a parameter list. Could not easily find this in the Release Notes.

I finally found it by looking through the updated version of the 'Books Online'. I must admit I understand the logic behind changing things here and the revised approach will make for more efficient queries (at the expense of recoding) - but it would be nice to know things prior to installing the SP - not after.

FYI - if, like me you develop on one system, and deploy to another, you can continue to develop on 2005/SP1, deploy to a 2005/non-SP1 system and things will work as before. Once you update the deployment target system to 2005/SP1 - the option is gone and you'll have to go back and do the parameter dataset recoding as outlined in the updated Books Online.

Bob

|||

Where did you find this information in Books Online? Can you provide a link? I'd like to take a closer look, because we also use this feature (which is admittedly funky) extensively.

Joe

|||

There is updated BOL contents available on MSDN which explains how to change SQL-based queries to simulate the "ALL" element: http://msdn2.microsoft.com/en-us/library/ms155917.aspx (scroll to the section about "Adding an All Member to a Multivalue Parameter").

-- Robert

|||

i believe there is also a hotfix available for this problem

|||

Do you have a link to the hotfix?

michael

|||http://support.microsoft.com/kb/918222|||

Thank you very much.

Michael

|||

Hello,

I faced the problem that on my development server <select all> was displaying and on the production it was not due to SP2 on development compared to SP1 on production.

SP2 was then loaded on the production server and rebooted the machine even then the <select all> cannot be seen. Would i need to reload the report or any futher fixes.

I know I am close to reaching a solution

Thanks

sql

Multivalue Parameter SQL Server 2005 SP1

Hello, I've installed SP1 for SQl Server 2005 and I noticed that the option "Select all" on a multi value parameter drop down (using web browser) is missing. Even if I can see it on VS 2005.....
Anyone can help me?
Thank you very much.
I rely on the 'Select All' capability as well. How can I get it back now that I've upgraded to SP1?|||

I finally found the answer on this. SP1 removes the 'Select All' capability previously applied when choosing the 'multi-value' option on a parameter list. Could not easily find this in the Release Notes.

I finally found it by looking through the updated version of the 'Books Online'. I must admit I understand the logic behind changing things here and the revised approach will make for more efficient queries (at the expense of recoding) - but it would be nice to know things prior to installing the SP - not after.

FYI - if, like me you develop on one system, and deploy to another, you can continue to develop on 2005/SP1, deploy to a 2005/non-SP1 system and things will work as before. Once you update the deployment target system to 2005/SP1 - the option is gone and you'll have to go back and do the parameter dataset recoding as outlined in the updated Books Online.

Bob

|||

Where did you find this information in Books Online? Can you provide a link? I'd like to take a closer look, because we also use this feature (which is admittedly funky) extensively.

Joe

|||

There is updated BOL contents available on MSDN which explains how to change SQL-based queries to simulate the "ALL" element: http://msdn2.microsoft.com/en-us/library/ms155917.aspx (scroll to the section about "Adding an All Member to a Multivalue Parameter").

-- Robert

|||

i believe there is also a hotfix available for this problem

|||

Do you have a link to the hotfix?

michael

|||http://support.microsoft.com/kb/918222|||

Thank you very much.

Michael

|||

Hello,

I faced the problem that on my development server <select all> was displaying and on the production it was not due to SP2 on development compared to SP1 on production.

SP2 was then loaded on the production server and rebooted the machine even then the <select all> cannot be seen. Would i need to reload the report or any futher fixes.

I know I am close to reaching a solution

Thanks

Multivalue Parameter SQL Server 2005 SP1

Hello, I've installed SP1 for SQl Server 2005 and I noticed that the option "Select all" on a multi value parameter drop down (using web browser) is missing. Even if I can see it on VS 2005.....
Anyone can help me?
Thank you very much.
I rely on the 'Select All' capability as well. How can I get it back now that I've upgraded to SP1?|||

I finally found the answer on this. SP1 removes the 'Select All' capability previously applied when choosing the 'multi-value' option on a parameter list. Could not easily find this in the Release Notes.

I finally found it by looking through the updated version of the 'Books Online'. I must admit I understand the logic behind changing things here and the revised approach will make for more efficient queries (at the expense of recoding) - but it would be nice to know things prior to installing the SP - not after.

FYI - if, like me you develop on one system, and deploy to another, you can continue to develop on 2005/SP1, deploy to a 2005/non-SP1 system and things will work as before. Once you update the deployment target system to 2005/SP1 - the option is gone and you'll have to go back and do the parameter dataset recoding as outlined in the updated Books Online.

Bob

|||

Where did you find this information in Books Online? Can you provide a link? I'd like to take a closer look, because we also use this feature (which is admittedly funky) extensively.

Joe

|||

There is updated BOL contents available on MSDN which explains how to change SQL-based queries to simulate the "ALL" element: http://msdn2.microsoft.com/en-us/library/ms155917.aspx (scroll to the section about "Adding an All Member to a Multivalue Parameter").

-- Robert

|||

i believe there is also a hotfix available for this problem

|||

Do you have a link to the hotfix?

michael

|||http://support.microsoft.com/kb/918222|||

Thank you very much.

Michael

|||

Hello,

I faced the problem that on my development server <select all> was displaying and on the production it was not due to SP2 on development compared to SP1 on production.

SP2 was then loaded on the production server and rebooted the machine even then the <select all> cannot be seen. Would i need to reload the report or any futher fixes.

I know I am close to reaching a solution

Thanks

Multivalue Parameter SQL Server 2005 SP1

Hello, I've installed SP1 for SQl Server 2005 and I noticed that the option "Select all" on a multi value parameter drop down (using web browser) is missing. Even if I can see it on VS 2005.....
Anyone can help me?
Thank you very much.I rely on the 'Select All' capability as well. How can I get it back now that I've upgraded to SP1?|||

I finally found the answer on this. SP1 removes the 'Select All' capability previously applied when choosing the 'multi-value' option on a parameter list. Could not easily find this in the Release Notes.

I finally found it by looking through the updated version of the 'Books Online'. I must admit I understand the logic behind changing things here and the revised approach will make for more efficient queries (at the expense of recoding) - but it would be nice to know things prior to installing the SP - not after.

FYI - if, like me you develop on one system, and deploy to another, you can continue to develop on 2005/SP1, deploy to a 2005/non-SP1 system and things will work as before. Once you update the deployment target system to 2005/SP1 - the option is gone and you'll have to go back and do the parameter dataset recoding as outlined in the updated Books Online.

Bob

|||

Where did you find this information in Books Online? Can you provide a link? I'd like to take a closer look, because we also use this feature (which is admittedly funky) extensively.

Joe

|||

There is updated BOL contents available on MSDN which explains how to change SQL-based queries to simulate the "ALL" element: http://msdn2.microsoft.com/en-us/library/ms155917.aspx (scroll to the section about "Adding an All Member to a Multivalue Parameter").

-- Robert

|||

i believe there is also a hotfix available for this problem

|||

Do you have a link to the hotfix?

michael

|||http://support.microsoft.com/kb/918222|||

Thank you very much.

Michael

|||

Hello,

I faced the problem that on my development server <select all> was displaying and on the production it was not due to SP2 on development compared to SP1 on production.

SP2 was then loaded on the production server and rebooted the machine even then the <select all> cannot be seen. Would i need to reload the report or any futher fixes.

I know I am close to reaching a solution

Thanks

Multiserver administratie on SQL2005

Hello,
does anyone has managed to get MultiServer jobs working on SQL2005.
I have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP
server and DNS server. The two others are SQL2005, using the same
service account that has full administrative rights. I log on also as
administrator when I try to make one of the SQL2005 the master server.
During enlisting of the target server on the master server I get the
error that the target server could not logon on the master server
(altough its service account has full admin rights on the master server)
Any suggestions
Thanks a lot in advance
Marc MertensMarc
Do you mean to run a job on the source server that does the work on the
destination? Linked servers?
"Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hello,
> does anyone has managed to get MultiServer jobs working on SQL2005. I
> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
> and DNS server. The two others are SQL2005, using the same service account
> that has full administrative rights. I log on also as administrator when I
> try to make one of the SQL2005 the master server. During enlisting of the
> target server on the master server I get the error that the target server
> could not logon on the master server (altough its service account has full
> admin rights on the master server)
> Any suggestions
> Thanks a lot in advance
> Marc Mertens|||No, MultiServer administration means that you create a multserver job on
a master server that is downloaded on target servers and executed there.
You enable multiserver administration by right clicking on the SQL
Server agent and choosing the correct menu entry in All Tasks. This is a
feature already available on a SQL2000 (where it worked without a
problem), but it does not seems to work anymore in SQL2005 (at least I
can not get it working).
Marc
Uri Dimant wrote:
> Marc
> Do you mean to run a job on the source server that does the work on the
> destination? Linked servers?
>
> "Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
> news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
>

Multiserver administratie on SQL2005

Hello,
does anyone has managed to get MultiServer jobs working on SQL2005.
I have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP
server and DNS server. The two others are SQL2005, using the same
service account that has full administrative rights. I log on also as
administrator when I try to make one of the SQL2005 the master server.
During enlisting of the target server on the master server I get the
error that the target server could not logon on the master server
(altough its service account has full admin rights on the master server)
Any suggestions
Thanks a lot in advance
Marc MertensMarc
Do you mean to run a job on the source server that does the work on the
destination? Linked servers?
"Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hello,
> does anyone has managed to get MultiServer jobs working on SQL2005. I
> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
> and DNS server. The two others are SQL2005, using the same service account
> that has full administrative rights. I log on also as administrator when I
> try to make one of the SQL2005 the master server. During enlisting of the
> target server on the master server I get the error that the target server
> could not logon on the master server (altough its service account has full
> admin rights on the master server)
> Any suggestions
> Thanks a lot in advance
> Marc Mertens|||No, MultiServer administration means that you create a multserver job on
a master server that is downloaded on target servers and executed there.
You enable multiserver administration by right clicking on the SQL
Server agent and choosing the correct menu entry in All Tasks. This is a
feature already available on a SQL2000 (where it worked without a
problem), but it does not seems to work anymore in SQL2005 (at least I
can not get it working).
Marc
Uri Dimant wrote:
> Marc
> Do you mean to run a job on the source server that does the work on the
> destination? Linked servers?
>
> "Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
> news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> does anyone has managed to get MultiServer jobs working on SQL2005. I
>> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
>> and DNS server. The two others are SQL2005, using the same service account
>> that has full administrative rights. I log on also as administrator when I
>> try to make one of the SQL2005 the master server. During enlisting of the
>> target server on the master server I get the error that the target server
>> could not logon on the master server (altough its service account has full
>> admin rights on the master server)
>> Any suggestions
>> Thanks a lot in advance
>> Marc Mertens
>

Multi-Select Value dropdown "Select All" in SP1

Hi,
With SQL-Server 2005 SP1, the "Select All" in the dropdown does not appear.
Without SP1 the "Select All" in Multi-Select Value did appear. How can I fix
it?
Regards,
TomI just installed SP1 on a test machine and checked this out. I am getting
the same results as you. I am not happy about it at all. I will use my
contacts at MS to see what is happening.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> Hi,
> With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> appear.
> Without SP1 the "Select All" in Multi-Select Value did appear. How can I
> fix
> it?
> Regards,
> Tom
>|||Have you heard anything back from Microsoft on this? It is causing me real
problems with users.
"Bruce L-C [MVP]" wrote:
> I just installed SP1 on a test machine and checked this out. I am getting
> the same results as you. I am not happy about it at all. I will use my
> contacts at MS to see what is happening.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> > Hi,
> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> > appear.
> > Without SP1 the "Select All" in Multi-Select Value did appear. How can I
> > fix
> > it?
> > Regards,
> > Tom
> >
> >
>
>|||Me too... terrible bug! This is really hurting us too. Hope there's a hotfix
or a config option somewhere.
"Dan D" wrote:
> Have you heard anything back from Microsoft on this? It is causing me real
> problems with users.
> "Bruce L-C [MVP]" wrote:
> > I just installed SP1 on a test machine and checked this out. I am getting
> > the same results as you. I am not happy about it at all. I will use my
> > contacts at MS to see what is happening.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> > news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> > > Hi,
> > > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> > > appear.
> > > Without SP1 the "Select All" in Multi-Select Value did appear. How can I
> > > fix
> > > it?
> > > Regards,
> > > Tom
> > >
> > >
> >
> >
> >|||Found some more info:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=f68f4903-5171-4fff-9d70-1b4342b03a35
Please add comments and vote for it and yell loud so MS changes this!
"Bruce L-C [MVP]" wrote:
> I just installed SP1 on a test machine and checked this out. I am getting
> the same results as you. I am not happy about it at all. I will use my
> contacts at MS to see what is happening.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> > Hi,
> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> > appear.
> > Without SP1 the "Select All" in Multi-Select Value did appear. How can I
> > fix
> > it?
> > Regards,
> > Tom
> >
> >
>
>|||I just got info on this. What happened is the Select All parameter caused
problems with Analysis Services. So, this is by design. What I will be doing
is rewriting my queries and stored procedures that use this to use a new
parameter I will add to the list called All.
If a query it would look like this:
select somefields from sometable where (@.Param='All' or somefield in
(@.Param))
This will result in better performance anyway but not as nice a user
interface for multi-select. However, most of my non-multi-select parameters
off an All option so my users are used to looking for it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan D" <Dan D@.discussions.microsoft.com> wrote in message
news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
> Have you heard anything back from Microsoft on this? It is causing me
> real
> problems with users.
> "Bruce L-C [MVP]" wrote:
>> I just installed SP1 on a test machine and checked this out. I am getting
>> the same results as you. I am not happy about it at all. I will use my
>> contacts at MS to see what is happening.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
>> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
>> > Hi,
>> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
>> > appear.
>> > Without SP1 the "Select All" in Multi-Select Value did appear. How can
>> > I
>> > fix
>> > it?
>> > Regards,
>> > Tom
>> >
>> >
>>|||Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have any more
specifics? How was it causing problems? Is it just simply an issue of users
choosing Select All which made the list of selected items huge causing
performance problems?
Do you mind sharing who at MS you got that answer from?
Unless there's some bug that it caused that I don't know about, my strong
opinion is that they shouldn't remove a feature just because some dumb
developers abused it and caused performance problems.
"Bruce L-C [MVP]" wrote:
> I just got info on this. What happened is the Select All parameter caused
> problems with Analysis Services. So, this is by design. What I will be doing
> is rewriting my queries and stored procedures that use this to use a new
> parameter I will add to the list called All.
> If a query it would look like this:
> select somefields from sometable where (@.Param='All' or somefield in
> (@.Param))
> This will result in better performance anyway but not as nice a user
> interface for multi-select. However, most of my non-multi-select parameters
> off an All option so my users are used to looking for it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
> > Have you heard anything back from Microsoft on this? It is causing me
> > real
> > problems with users.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I just installed SP1 on a test machine and checked this out. I am getting
> >> the same results as you. I am not happy about it at all. I will use my
> >> contacts at MS to see what is happening.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> >> > Hi,
> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> >> > appear.
> >> > Without SP1 the "Select All" in Multi-Select Value did appear. How can
> >> > I
> >> > fix
> >> > it?
> >> > Regards,
> >> > Tom
> >> >
> >> >
> >>
> >>
> >>
>
>|||>>>>>>
This is from Brian Welcker, the program manager for Reporting Services: When
used in conjunction with Analysis Services, the Select All feature
circumvents the built-in Analysis Services "All" member, individually
selecting every member in a dimension. This causes such a massive
performance degradation for AS queries (and is likely for users to use since
all AS parameters are multi-valued). For SQL queries, it is not as impactful
on performance (although an IN clause with 100 values is not very
efficient).
>>>>>>
Given the above it was not an unreasonable way to go. Except, I don't care
about Analysis Services personally so my preference would have been to leave
it alone. Kindof a NIMBY feeling. Now that I know it is by design then I
will work around it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:C3A68DD9-9C3C-4111-89E7-436539274863@.microsoft.com...
> Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have any
> more
> specifics? How was it causing problems? Is it just simply an issue of
> users
> choosing Select All which made the list of selected items huge causing
> performance problems?
> Do you mind sharing who at MS you got that answer from?
> Unless there's some bug that it caused that I don't know about, my strong
> opinion is that they shouldn't remove a feature just because some dumb
> developers abused it and caused performance problems.
> "Bruce L-C [MVP]" wrote:
>> I just got info on this. What happened is the Select All parameter caused
>> problems with Analysis Services. So, this is by design. What I will be
>> doing
>> is rewriting my queries and stored procedures that use this to use a new
>> parameter I will add to the list called All.
>> If a query it would look like this:
>> select somefields from sometable where (@.Param='All' or somefield in
>> (@.Param))
>> This will result in better performance anyway but not as nice a user
>> interface for multi-select. However, most of my non-multi-select
>> parameters
>> off an All option so my users are used to looking for it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
>> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
>> > Have you heard anything back from Microsoft on this? It is causing me
>> > real
>> > problems with users.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> I just installed SP1 on a test machine and checked this out. I am
>> >> getting
>> >> the same results as you. I am not happy about it at all. I will use my
>> >> contacts at MS to see what is happening.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
>> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
>> >> > Hi,
>> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
>> >> > appear.
>> >> > Without SP1 the "Select All" in Multi-Select Value did appear. How
>> >> > can
>> >> > I
>> >> > fix
>> >> > it?
>> >> > Regards,
>> >> > Tom
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Microsoft made a BAD Choice in resolving this problem. An option to disable
the select all could be made available when setting up parameters. This would
allow those that run into the problem to disable the functionality while
leaving it available for the remaining users.
PUT IT BACK
"Bruce L-C [MVP]" wrote:
> >>>>>>
> This is from Brian Welcker, the program manager for Reporting Services: When
> used in conjunction with Analysis Services, the Select All feature
> circumvents the built-in Analysis Services "All" member, individually
> selecting every member in a dimension. This causes such a massive
> performance degradation for AS queries (and is likely for users to use since
> all AS parameters are multi-valued). For SQL queries, it is not as impactful
> on performance (although an IN clause with 100 values is not very
> efficient).
> >>>>>>
> Given the above it was not an unreasonable way to go. Except, I don't care
> about Analysis Services personally so my preference would have been to leave
> it alone. Kindof a NIMBY feeling. Now that I know it is by design then I
> will work around it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
> news:C3A68DD9-9C3C-4111-89E7-436539274863@.microsoft.com...
> > Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have any
> > more
> > specifics? How was it causing problems? Is it just simply an issue of
> > users
> > choosing Select All which made the list of selected items huge causing
> > performance problems?
> >
> > Do you mind sharing who at MS you got that answer from?
> >
> > Unless there's some bug that it caused that I don't know about, my strong
> > opinion is that they shouldn't remove a feature just because some dumb
> > developers abused it and caused performance problems.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I just got info on this. What happened is the Select All parameter caused
> >> problems with Analysis Services. So, this is by design. What I will be
> >> doing
> >> is rewriting my queries and stored procedures that use this to use a new
> >> parameter I will add to the list called All.
> >>
> >> If a query it would look like this:
> >> select somefields from sometable where (@.Param='All' or somefield in
> >> (@.Param))
> >>
> >> This will result in better performance anyway but not as nice a user
> >> interface for multi-select. However, most of my non-multi-select
> >> parameters
> >> off an All option so my users are used to looking for it.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
> >> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
> >> > Have you heard anything back from Microsoft on this? It is causing me
> >> > real
> >> > problems with users.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> I just installed SP1 on a test machine and checked this out. I am
> >> >> getting
> >> >> the same results as you. I am not happy about it at all. I will use my
> >> >> contacts at MS to see what is happening.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> >> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> >> >> > Hi,
> >> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> >> >> > appear.
> >> >> > Without SP1 the "Select All" in Multi-Select Value did appear. How
> >> >> > can
> >> >> > I
> >> >> > fix
> >> >> > it?
> >> >> > Regards,
> >> >> > Tom
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Just found out from Chris Hays at MS that there's going to be a hotfix to
reenable the "select/unselect all" feature for parameters in SSRS. No date
announced yet, but I think it's coming. They'll eventually (whether in a SP
or the next release, I don't know) make it configurable.
"Big John" wrote:
> Microsoft made a BAD Choice in resolving this problem. An option to disable
> the select all could be made available when setting up parameters. This would
> allow those that run into the problem to disable the functionality while
> leaving it available for the remaining users.
> PUT IT BACK
>
> "Bruce L-C [MVP]" wrote:
> > >>>>>>
> > This is from Brian Welcker, the program manager for Reporting Services: When
> > used in conjunction with Analysis Services, the Select All feature
> > circumvents the built-in Analysis Services "All" member, individually
> > selecting every member in a dimension. This causes such a massive
> > performance degradation for AS queries (and is likely for users to use since
> > all AS parameters are multi-valued). For SQL queries, it is not as impactful
> > on performance (although an IN clause with 100 values is not very
> > efficient).
> > >>>>>>
> >
> > Given the above it was not an unreasonable way to go. Except, I don't care
> > about Analysis Services personally so my preference would have been to leave
> > it alone. Kindof a NIMBY feeling. Now that I know it is by design then I
> > will work around it.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
> > news:C3A68DD9-9C3C-4111-89E7-436539274863@.microsoft.com...
> > > Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have any
> > > more
> > > specifics? How was it causing problems? Is it just simply an issue of
> > > users
> > > choosing Select All which made the list of selected items huge causing
> > > performance problems?
> > >
> > > Do you mind sharing who at MS you got that answer from?
> > >
> > > Unless there's some bug that it caused that I don't know about, my strong
> > > opinion is that they shouldn't remove a feature just because some dumb
> > > developers abused it and caused performance problems.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I just got info on this. What happened is the Select All parameter caused
> > >> problems with Analysis Services. So, this is by design. What I will be
> > >> doing
> > >> is rewriting my queries and stored procedures that use this to use a new
> > >> parameter I will add to the list called All.
> > >>
> > >> If a query it would look like this:
> > >> select somefields from sometable where (@.Param='All' or somefield in
> > >> (@.Param))
> > >>
> > >> This will result in better performance anyway but not as nice a user
> > >> interface for multi-select. However, most of my non-multi-select
> > >> parameters
> > >> off an All option so my users are used to looking for it.
> > >>
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
> > >> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
> > >> > Have you heard anything back from Microsoft on this? It is causing me
> > >> > real
> > >> > problems with users.
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> >> I just installed SP1 on a test machine and checked this out. I am
> > >> >> getting
> > >> >> the same results as you. I am not happy about it at all. I will use my
> > >> >> contacts at MS to see what is happening.
> > >> >>
> > >> >>
> > >> >> --
> > >> >> Bruce Loehle-Conger
> > >> >> MVP SQL Server Reporting Services
> > >> >>
> > >> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
> > >> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
> > >> >> > Hi,
> > >> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does not
> > >> >> > appear.
> > >> >> > Without SP1 the "Select All" in Multi-Select Value did appear. How
> > >> >> > can
> > >> >> > I
> > >> >> > fix
> > >> >> > it?
> > >> >> > Regards,
> > >> >> > Tom
> > >> >> >
> > >> >> >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||Thanks for sharing with us.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:8000BBCE-9564-4D49-9D7A-B3DABBD62F5A@.microsoft.com...
> Just found out from Chris Hays at MS that there's going to be a hotfix to
> reenable the "select/unselect all" feature for parameters in SSRS. No date
> announced yet, but I think it's coming. They'll eventually (whether in a
> SP
> or the next release, I don't know) make it configurable.
> "Big John" wrote:
>> Microsoft made a BAD Choice in resolving this problem. An option to
>> disable
>> the select all could be made available when setting up parameters. This
>> would
>> allow those that run into the problem to disable the functionality while
>> leaving it available for the remaining users.
>> PUT IT BACK
>>
>> "Bruce L-C [MVP]" wrote:
>> > >>>>>>
>> > This is from Brian Welcker, the program manager for Reporting Services:
>> > When
>> > used in conjunction with Analysis Services, the Select All feature
>> > circumvents the built-in Analysis Services "All" member, individually
>> > selecting every member in a dimension. This causes such a massive
>> > performance degradation for AS queries (and is likely for users to use
>> > since
>> > all AS parameters are multi-valued). For SQL queries, it is not as
>> > impactful
>> > on performance (although an IN clause with 100 values is not very
>> > efficient).
>> > >>>>>>
>> >
>> > Given the above it was not an unreasonable way to go. Except, I don't
>> > care
>> > about Analysis Services personally so my preference would have been to
>> > leave
>> > it alone. Kindof a NIMBY feeling. Now that I know it is by design then
>> > I
>> > will work around it.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
>> > news:C3A68DD9-9C3C-4111-89E7-436539274863@.microsoft.com...
>> > > Bruce. Thanks for the info. I'm an MDX nerd myself, so do you have
>> > > any
>> > > more
>> > > specifics? How was it causing problems? Is it just simply an issue of
>> > > users
>> > > choosing Select All which made the list of selected items huge
>> > > causing
>> > > performance problems?
>> > >
>> > > Do you mind sharing who at MS you got that answer from?
>> > >
>> > > Unless there's some bug that it caused that I don't know about, my
>> > > strong
>> > > opinion is that they shouldn't remove a feature just because some
>> > > dumb
>> > > developers abused it and caused performance problems.
>> > >
>> > > "Bruce L-C [MVP]" wrote:
>> > >
>> > >> I just got info on this. What happened is the Select All parameter
>> > >> caused
>> > >> problems with Analysis Services. So, this is by design. What I will
>> > >> be
>> > >> doing
>> > >> is rewriting my queries and stored procedures that use this to use a
>> > >> new
>> > >> parameter I will add to the list called All.
>> > >>
>> > >> If a query it would look like this:
>> > >> select somefields from sometable where (@.Param='All' or somefield in
>> > >> (@.Param))
>> > >>
>> > >> This will result in better performance anyway but not as nice a user
>> > >> interface for multi-select. However, most of my non-multi-select
>> > >> parameters
>> > >> off an All option so my users are used to looking for it.
>> > >>
>> > >>
>> > >> --
>> > >> Bruce Loehle-Conger
>> > >> MVP SQL Server Reporting Services
>> > >>
>> > >> "Dan D" <Dan D@.discussions.microsoft.com> wrote in message
>> > >> news:89710EA2-AF56-42DA-9027-F472ED89A28C@.microsoft.com...
>> > >> > Have you heard anything back from Microsoft on this? It is
>> > >> > causing me
>> > >> > real
>> > >> > problems with users.
>> > >> >
>> > >> > "Bruce L-C [MVP]" wrote:
>> > >> >
>> > >> >> I just installed SP1 on a test machine and checked this out. I am
>> > >> >> getting
>> > >> >> the same results as you. I am not happy about it at all. I will
>> > >> >> use my
>> > >> >> contacts at MS to see what is happening.
>> > >> >>
>> > >> >>
>> > >> >> --
>> > >> >> Bruce Loehle-Conger
>> > >> >> MVP SQL Server Reporting Services
>> > >> >>
>> > >> >> "Tom.Rally" <TomRally@.discussions.microsoft.com> wrote in message
>> > >> >> news:AE70F684-91DB-466B-ADDA-F2F5A9D9E682@.microsoft.com...
>> > >> >> > Hi,
>> > >> >> > With SQL-Server 2005 SP1, the "Select All" in the dropdown does
>> > >> >> > not
>> > >> >> > appear.
>> > >> >> > Without SP1 the "Select All" in Multi-Select Value did appear.
>> > >> >> > How
>> > >> >> > can
>> > >> >> > I
>> > >> >> > fix
>> > >> >> > it?
>> > >> >> > Regards,
>> > >> >> > Tom
>> > >> >> >
>> > >> >> >
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >|||Has anybody used tried using this multi-select with dependent
parameters? It doesn't seem to work. For example, I have a query for
country and one for province:
SELECT '0' AS SortOrder, 'All' AS COUNTRY
UNION
SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
FROM Province
ORDER BY SortOrder
SELECT '0' AS SortOrder, 'All' AS PROVINCE
UNION
SELECT PROVINCE AS SortOrder, PROVINCE
FROM Province
WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
ORDER BY SortOrder
When the report renders, I can choose USA, and have all 50 states show
up. However, if I choose USA and Canada, the "Province" query breaks.
I also tried using the following query:
SELECT '0' AS SortOrder, 'All' AS PROVINCE
UNION
SELECT PROVINCE AS SortOrder, PROVINCE
FROM Province
WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
ORDER BY SortOrder
It didn't work any better. Without these dependent parameters, my
reports are basically useless. Any ideas? PLEASE?
Joe|||This is slightly off topic, I am MAD really MAD!
This is total BS it's akin to removing tires from a car after you purchased
it becuase some people like bigger tires!
I have lists of 200 plus selections that people are KILLING me over, because
the Default was to include all off their Units, now if they want to reduce
scope, they have to click 200 times! MICROSOFT YOU DID ME WRONG! I am very
upset, not only did I waste 3 hours trying to figure this out, I wasted other
peoples time as well. You'd think for the PRICES you charge you could figure
out something better than this...I am contacting Mr. Scoble and letting him
know what a bunch of idiots you are.
"joe nedumgottil" wrote:
> Has anybody used tried using this multi-select with dependent
> parameters? It doesn't seem to work. For example, I have a query for
> country and one for province:
> SELECT '0' AS SortOrder, 'All' AS COUNTRY
> UNION
> SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
> FROM Province
> ORDER BY SortOrder
> SELECT '0' AS SortOrder, 'All' AS PROVINCE
> UNION
> SELECT PROVINCE AS SortOrder, PROVINCE
> FROM Province
> WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
> ORDER BY SortOrder
>
> When the report renders, I can choose USA, and have all 50 states show
> up. However, if I choose USA and Canada, the "Province" query breaks.
> I also tried using the following query:
> SELECT '0' AS SortOrder, 'All' AS PROVINCE
> UNION
> SELECT PROVINCE AS SortOrder, PROVINCE
> FROM Province
> WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
> ORDER BY SortOrder
> It didn't work any better. Without these dependent parameters, my
> reports are basically useless. Any ideas? PLEASE?
> Joe
>|||Joe, You are right. I too have the same problem can you please let me know if
you slove this problem?
Thanks!
Reddy
"joe nedumgottil" wrote:
> Has anybody used tried using this multi-select with dependent
> parameters? It doesn't seem to work. For example, I have a query for
> country and one for province:
> SELECT '0' AS SortOrder, 'All' AS COUNTRY
> UNION
> SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
> FROM Province
> ORDER BY SortOrder
> SELECT '0' AS SortOrder, 'All' AS PROVINCE
> UNION
> SELECT PROVINCE AS SortOrder, PROVINCE
> FROM Province
> WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
> ORDER BY SortOrder
>
> When the report renders, I can choose USA, and have all 50 states show
> up. However, if I choose USA and Canada, the "Province" query breaks.
> I also tried using the following query:
> SELECT '0' AS SortOrder, 'All' AS PROVINCE
> UNION
> SELECT PROVINCE AS SortOrder, PROVINCE
> FROM Province
> WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
> ORDER BY SortOrder
> It didn't work any better. Without these dependent parameters, my
> reports are basically useless. Any ideas? PLEASE?
> Joe
>|||Joe, Try this it will work.
WHERE (COUNTRY IN (@.Country) or 'All' in(@.Country))
ORDER BY SortOrder
"Reddy" wrote:
> Joe, You are right. I too have the same problem can you please let me know if
> you slove this problem?
> Thanks!
> Reddy
> "joe nedumgottil" wrote:
> > Has anybody used tried using this multi-select with dependent
> > parameters? It doesn't seem to work. For example, I have a query for
> > country and one for province:
> >
> > SELECT '0' AS SortOrder, 'All' AS COUNTRY
> > UNION
> > SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
> > FROM Province
> > ORDER BY SortOrder
> >
> > SELECT '0' AS SortOrder, 'All' AS PROVINCE
> > UNION
> > SELECT PROVINCE AS SortOrder, PROVINCE
> > FROM Province
> > WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
> > ORDER BY SortOrder
> >
> >
> > When the report renders, I can choose USA, and have all 50 states show
> > up. However, if I choose USA and Canada, the "Province" query breaks.
> > I also tried using the following query:
> >
> > SELECT '0' AS SortOrder, 'All' AS PROVINCE
> > UNION
> > SELECT PROVINCE AS SortOrder, PROVINCE
> > FROM Province
> > WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
> > ORDER BY SortOrder
> >
> > It didn't work any better. Without these dependent parameters, my
> > reports are basically useless. Any ideas? PLEASE?
> >
> > Joe
> >
> >|||FYI, MSFT cosed this issue as "by design" in August.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124729
Not cool. My first disappointment with SQL Server in a long time
"Reddy" <Reddy@.discussions.microsoft.com> wrote in message
news:4466EA95-5E12-4497-BF35-2B0FC6AA9B38@.microsoft.com...
> Joe, Try this it will work.
> WHERE (COUNTRY IN (@.Country) or 'All' in(@.Country))
> ORDER BY SortOrder
>
> "Reddy" wrote:
>> Joe, You are right. I too have the same problem can you please let me
>> know if
>> you slove this problem?
>> Thanks!
>> Reddy
>> "joe nedumgottil" wrote:
>> > Has anybody used tried using this multi-select with dependent
>> > parameters? It doesn't seem to work. For example, I have a query for
>> > country and one for province:
>> >
>> > SELECT '0' AS SortOrder, 'All' AS COUNTRY
>> > UNION
>> > SELECT DISTINCT COUNTRY AS SortOrder, COUNTRY
>> > FROM Province
>> > ORDER BY SortOrder
>> >
>> > SELECT '0' AS SortOrder, 'All' AS PROVINCE
>> > UNION
>> > SELECT PROVINCE AS SortOrder, PROVINCE
>> > FROM Province
>> > WHERE (@.Country LIKE 'All%') OR (COUNTRY IN (@.Country))
>> > ORDER BY SortOrder
>> >
>> >
>> > When the report renders, I can choose USA, and have all 50 states show
>> > up. However, if I choose USA and Canada, the "Province" query breaks.
>> > I also tried using the following query:
>> >
>> > SELECT '0' AS SortOrder, 'All' AS PROVINCE
>> > UNION
>> > SELECT PROVINCE AS SortOrder, PROVINCE
>> > FROM Province
>> > WHERE ('All%' LIKE @.Country) OR (COUNTRY IN (@.Country))
>> > ORDER BY SortOrder
>> >
>> > It didn't work any better. Without these dependent parameters, my
>> > reports are basically useless. Any ideas? PLEASE?
>> >
>> > Joe
>> >
>> >sql

Multiple-resultset stored procedures

Hi,
Is it possible to use multiple-resultset stored procedures with Reporting
Services 2005?
I use SS2005, SP1.
Thanks in advance
Nikola MilicNo it is not. RS only can use one resultset. You could add a parameter and
based on the parameter return the appropriate resultset and then hardcode
that parameter in your report when call the SP. However, that does mean your
SP is called multiple times.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:%23tPpPeB0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Is it possible to use multiple-resultset stored procedures with Reporting
> Services 2005?
> I use SS2005, SP1.
> Thanks in advance
> Nikola Milic
>