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

Multi-value parameter returns syntax error

I've created a report that includes a multi-value parameter, however when
more than one value is seelcted from the list the report produces an error
that references "Incorrect Syntax near ",".
How do I correct this?Show how you are using the parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:A4B88054-4FAE-4D62-BEF0-8B282C787E9E@.microsoft.com...
> I've created a report that includes a multi-value parameter, however when
> more than one value is seelcted from the list the report produces an error
> that references "Incorrect Syntax near ",".
> How do I correct this?
>|||Send the Query where you are using this parametre.For Multivalue you
should use "in" keyword instead of "="
hope this might be your problem
Bruce L-C [MVP] wrote:
> Show how you are using the parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "StMaas" <StMaas@.discussions.microsoft.com> wrote in message
> news:A4B88054-4FAE-4D62-BEF0-8B282C787E9E@.microsoft.com...
> > I've created a report that includes a multi-value parameter, however when
> > more than one value is seelcted from the list the report produces an error
> > that references "Incorrect Syntax near ",".
> > How do I correct this?
> >sql

multi-value parameter problem - please help

I get an error (Incorrect syntax near ','.) when I choose more than one value
for any of the multi-valued report parameters in the below query. All
parameters, except scheduled_date and tracking_number, are defined as
multi-value. It may be a problem with strings appending. Any suggestions?
SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
D.tracking_number, B.alternate_id, D.name,
CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
status, D.sent_date, D.scheduled_date, D.owner_user_id
FROM o_dpl_deployment AS D INNER JOIN
o_bas_brand AS B ON D.environment_id = B.environment_id
INNER JOIN
o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
AND B.environment_id = DT.environment_id
WHERE ((D.name IN (@.name)) OR (@.name = '-1'))
AND ((D.tracking_number LIKE @.tracking_number) OR
(@.tracking_number = ''))
AND ((D.promo_code IN (@.promo_code)) or (@.promo_code = '-1'))
AND ((D.scheduled_date >= @.scheduled_date AND
D.scheduled_date < DATEADD(month,1,@.scheduled_date))
OR (@.scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
>= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
OR (@.scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
>= GETDATE() - 1 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
>= GETDATE() - 3 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
>= GETDATE() - 7 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
>= GETDATE() - 14 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
>= GETDATE() - 30 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
>= GETDATE() - 60 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9599-12-31')))
AND ((DT.name IN (@.deployment_type_name)) OR
(@.deployment_type_name = '-1'))
AND ((B.name IN (@.brand_name)) OR (@.brand_name = '-1'))
AND ((D.owner_user_id IN (@.owner_user_id)) OR
(@.owner_user_id = '-1'))
AND ((D.created_by IN (@.created_by)) OR (@.created_by = '-1'))
AND ((D.status IN (@.status)) OR (@.status = '-1'))
AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
UNION
SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
ORDER BY D.scheduled_date DESCYou may want to search in the programming discussion group too. I had a
similar problem.
"Stephanie" wrote:
> I get an error (Incorrect syntax near ','.) when I choose more than one value
> for any of the multi-valued report parameters in the below query. All
> parameters, except scheduled_date and tracking_number, are defined as
> multi-value. It may be a problem with strings appending. Any suggestions?
> SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
> D.tracking_number, B.alternate_id, D.name,
> CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
> status, D.sent_date, D.scheduled_date, D.owner_user_id
> FROM o_dpl_deployment AS D INNER JOIN
> o_bas_brand AS B ON D.environment_id = B.environment_id
> INNER JOIN
> o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
> AND B.environment_id = DT.environment_id
> WHERE ((D.name IN (@.name)) OR (@.name = '-1'))
> AND ((D.tracking_number LIKE @.tracking_number) OR
> (@.tracking_number = ''))
> AND ((D.promo_code IN (@.promo_code)) or (@.promo_code = '-1'))
> AND ((D.scheduled_date >= @.scheduled_date AND
> D.scheduled_date < DATEADD(month,1,@.scheduled_date))
> OR (@.scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
> >= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
> OR (@.scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
> >= GETDATE() - 1 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
> >= GETDATE() - 3 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
> >= GETDATE() - 7 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
> >= GETDATE() - 14 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
> >= GETDATE() - 30 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
> >= GETDATE() - 60 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9599-12-31')))
> AND ((DT.name IN (@.deployment_type_name)) OR
> (@.deployment_type_name = '-1'))
> AND ((B.name IN (@.brand_name)) OR (@.brand_name = '-1'))
> AND ((D.owner_user_id IN (@.owner_user_id)) OR
> (@.owner_user_id = '-1'))
> AND ((D.created_by IN (@.created_by)) OR (@.created_by = '-1'))
> AND ((D.status IN (@.status)) OR (@.status = '-1'))
> AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
> UNION
> SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
> ORDER BY D.scheduled_date DESC
>

multi-value paramater from query: dynamically filtered

Hi,
I have a single value paramater that with a list of choices. I have a
second parameter, that is a mulit-value paramater with the choices derived
from a query. What I would lilke to do is filter the choice of the values
in the mult-value paramter by the value selected in the first single value
paramater. Is this possible. I know this can done on a web page, but so
far as I am aware the code behind these reports is XML and I don't know
enough about it to tell it to requery the multi-value parameter's query when
the sinlge value paramater's value is selected.
Can anyone explain how to do this, or let me know of any other way of
achieving this?
TIA,
JarrydHi Jarryd,
From your description, I understnad that you would like to implement
cascading parameters selection in your report.
If I have misunderstood, please let me know.
This seems to be a typical application scenario. I recommend that you refer
to this article to see if it helps:
Lesson 2: Adding Cascading Parameters to a Report
http://msdn2.microsoft.com/en-us/library/aa337426.aspx
If you have any other questioins or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
===========================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Charles my man, you have done it again! Genius!
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:07X9SNFoIHA.10116@.TK2MSFTNGHUB02.phx.gbl...
> Hi Jarryd,
> From your description, I understnad that you would like to implement
> cascading parameters selection in your report.
> If I have misunderstood, please let me know.
> This seems to be a typical application scenario. I recommend that you
> refer
> to this article to see if it helps:
> Lesson 2: Adding Cascading Parameters to a Report
> http://msdn2.microsoft.com/en-us/library/aa337426.aspx
> If you have any other questioins or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> ===========================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for
> non-urgent issues where an initial response from the community
> or a Microsoft Support Engineer within 1 business day is acceptable.
> Please note that each follow up response may take approximately
> 2 business days as the support professional working with you may
> need further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by
> contacting Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ============================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> =========================================================>|||:) My pleasure!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================sql

Multi-Select Value dropdown "Select All" Problem

Hi,
I hava report (RDL) which is having a parameter called Plan Owner, i
want the facality of "Select All" in the dropdown, this i can achive
through setting the parameter as Multi-Value. But the problem is if the
user select "select all" from dropdown, i uses the IN claues, i want to
avoid the use of IN clause if the "select all" is selected in
Multi-value dropdown. How can i programatically write the dataset to
avoid "select all"?
What is the internal value of "select all" which it added automatically
in the multi-value parameter dropdown.
I can provide more info if needed.
Regards,
SumitThis is my interpretation of the multivalue parameter.
The internal representation of a multivalue parameters is an array of
all the possible values for the parameter. So if your parameter has 3
values (A,B,C) if you do 'Select All', RS sees an array with values
(A,B,C)
The only way I know of to determine if someone has picked 'Select All'
is to test the array against the # of rows in the dataset populating
the parameters. It would look something like this:
=iif(Parameters!MultiValParam.Count = Count("datasetformvparam"), "",
"FIELD IN (" & join(Parameters!MultiValParam.Value, ", ") & ")")
Andy Potter|||Hi,
I tried the above solution but the count of my dataset always returns 0
even though there are records in there. Any suggestions?
=iif(Parameters!bus_group.Count = Count("business_group"),
"ALL",Join(Parameters!bus_group.Label, ", ") )
Thanks,
Melissa|||I ran into a similar problem in wanting to know if they had selected all
possible options. See the following for a further discussion, a feature
suggestion, and a workaround:
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=dfec87e1-a34d-4ff8-a480-fb1f8d4160ea
"Melissa" wrote:
> Hi,
> I tried the above solution but the count of my dataset always returns 0
> even though there are records in there. Any suggestions?
> =iif(Parameters!bus_group.Count = Count("business_group"),
> "ALL",Join(Parameters!bus_group.Label, ", ") )
> Thanks,
> Melissa
>

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

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:

that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:

hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:

that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:

hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multi-select Parameter - Single Quote Problem

Hi,
I am using SQL Server 2005 Reporting Services. I am trying to pass a
multi-select string value to my query but having difficulties with
single quotes. My query is:
SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
(@.CustomerType)
@.CustomerType is a multi-select parameter in my report. The value of
this parameter should be passed as 'Commercial','Residential' BUT I
think it is being passed as 'Commercial, Residential'
Please help.
Thanks
SajjadAre you putting in the single quotes in your selection? You shouldn't.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<sajjad.mussani@.gmail.com> wrote in message
news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
> Hi,
> I am using SQL Server 2005 Reporting Services. I am trying to pass a
> multi-select string value to my query but having difficulties with
> single quotes. My query is:
> SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> (@.CustomerType)
> @.CustomerType is a multi-select parameter in my report. The value of
> this parameter should be passed as 'Commercial','Residential' BUT I
> think it is being passed as 'Commercial, Residential'
> Please help.
> Thanks
> Sajjad
>|||I am not putting single quote in my selection. My selection is done
using a Multi-select drop down box. What I was trying to say is that,
I think the parameter should be passed with single quotes around each
selection, but it is not.
Sajjad
Bruce L-C [MVP] wrote:
> Are you putting in the single quotes in your selection? You shouldn't.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <sajjad.mussani@.gmail.com> wrote in message
> news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
> > Hi,
> >
> > I am using SQL Server 2005 Reporting Services. I am trying to pass a
> > multi-select string value to my query but having difficulties with
> > single quotes. My query is:
> >
> > SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> > (@.CustomerType)
> >
> > @.CustomerType is a multi-select parameter in my report. The value of
> > this parameter should be passed as 'Commercial','Residential' BUT I
> > think it is being passed as 'Commercial, Residential'
> >
> > Please help.
> > Thanks
> > Sajjad
> >|||I don't think that is the issue. I do this all the time. Is your query in a
stored procedure?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sajjad" <sajjad.mussani@.gmail.com> wrote in message
news:1158158039.291968.300150@.m73g2000cwd.googlegroups.com...
>I am not putting single quote in my selection. My selection is done
> using a Multi-select drop down box. What I was trying to say is that,
> I think the parameter should be passed with single quotes around each
> selection, but it is not.
> Sajjad
> Bruce L-C [MVP] wrote:
>> Are you putting in the single quotes in your selection? You shouldn't.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <sajjad.mussani@.gmail.com> wrote in message
>> news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
>> > Hi,
>> >
>> > I am using SQL Server 2005 Reporting Services. I am trying to pass a
>> > multi-select string value to my query but having difficulties with
>> > single quotes. My query is:
>> >
>> > SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
>> > (@.CustomerType)
>> >
>> > @.CustomerType is a multi-select parameter in my report. The value of
>> > this parameter should be passed as 'Commercial','Residential' BUT I
>> > think it is being passed as 'Commercial, Residential'
>> >
>> > Please help.
>> > Thanks
>> > Sajjad
>> >
>|||> SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> (@.CustomerType)
From SQL perspective, this won't work. You cannot pass such parameter. You
must either use dynamic sql ('... cust in ''' + @.cust ...) or parse this
multiparameter into table variable and use this table in subsequent query.
Radim Hampel

Multipurpose Foreign Key

I've got a field [user.unitID] that can be a foreign key on the table
[Base] *OR* the table [Command] depending on the value of [User.role]
(see "Structure," below).

Question 1: Is this poor DB design?
Question2: If this is okay DB design (or, if I can't change this DB),
how do I perform a join?

I started writing a sproc (see "Beginnings of sproc," below), which
will definitely work, once I get it set up, but when I do these sorts
of things, I later come to find that there was a straight SQL way to
do it. So, is there a straight SQL way to do this, building joins with
CASEs, or something like that?

Thanks,
Jamie

## Structure (simplified) ##

[USER]
userID
unitID
role -- values can be 'B' or 'C' referring to [base] or [command] tbl

[BASE]
ID
NAME

[COMMAND]
ID
NAME

## Beginnings of a sproc ##

GO
USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'getSessionInfo' AND type = 'P')
DROP PROCEDURE getSessionInfo
GO
USE myDB
GO
CREATE PROCEDURE getSessionInfo
@.userID varchar(50),
@.password varchar(50)
AS

DECLARE @.myUnitType varchar(2);

SELECT @.myUnitType = unitType
FROM
[user]
WHERE userID = @.userID
AND [password] = @.password

... blah blah blahJamie Jackson (wasteNOSPAMbasket@.bigfoot.com) writes:
> I've got a field [user.unitID] that can be a foreign key on the table
> [Base] *OR* the table [Command] depending on the value of [User.role]
> (see "Structure," below).
> Question 1: Is this poor DB design?

It is certainly not the plain standard design. And the method has the
apparent advantage that you can use FOREIGN-KEY constraint to enforce
the integrity, but you need to rely on triggers.

But since I know very little of your business problem, I am hesitant
to label the design as outright bad, or even poor. What I can say, is
that had I had the problem, I would definitely have looked into a solution
that would have permitted me to use DRI, but that would definitely have
been a case of fitting the solution to the tool.

> Question2: If this is okay DB design (or, if I can't change this DB),
> how do I perform a join?

Depends a little on the output, but say you want user and name of
base or command:

SELECT u.name, u.role, rolename = coalece(b.name, c.name)
FROM users u
LEFT JOIN base b ON u.role = 'B'
AND u.unitid = b.unitid
LEFT JOIN command c ON u.role = 'C'
AND u.unitid = c.unitid

An alternative is to introduce a basecommand table, to gather common
information, but I don't know enough about your business problem to
say whether this is a good idea or not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Jamie Jackson wrote:
> I've got a field [user.unitID] that can be a foreign key on the table
> [Base] *OR* the table [Command] depending on the value of [User.role]
> (see "Structure," below).
> Question 1: Is this poor DB design?
<snip
Yes. A table represents something. In your case a User, a Base or a
Command. In your design you specify whether a User can have a relation
with a Base or not. If it can have a relation, then this means there is
a foreign key column (or a relation table) for this specific relation.
For Command you make the same decision.

So in your case, User should have one column with a foreign key
constraint to Base, and another column with a foreign key constraint to
Command. You make the relations optional by allowing NULL values in the
foreign key columns.

If this is an Object Oriented design (for example, when Command extends
from Base), then you are in trouble, because OO and RDBMS don't map very
well.

Hope this helps,
Gert-Jan|||Thanks, Erland, that SQL was a nice tutorial on filtered joins and the
coalesce function, neither of which I've ever used.

Thanks,
Jamie

On Wed, 16 Jul 2003 21:54:51 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>Jamie Jackson (wasteNOSPAMbasket@.bigfoot.com) writes:
>> I've got a field [user.unitID] that can be a foreign key on the table
>> [Base] *OR* the table [Command] depending on the value of [User.role]
>> (see "Structure," below).
>>
>> Question 1: Is this poor DB design?
>It is certainly not the plain standard design. And the method has the
>apparent advantage that you can use FOREIGN-KEY constraint to enforce
>the integrity, but you need to rely on triggers.
>But since I know very little of your business problem, I am hesitant
>to label the design as outright bad, or even poor. What I can say, is
>that had I had the problem, I would definitely have looked into a solution
>that would have permitted me to use DRI, but that would definitely have
>been a case of fitting the solution to the tool.
>> Question2: If this is okay DB design (or, if I can't change this DB),
>> how do I perform a join?
>Depends a little on the output, but say you want user and name of
>base or command:
>
> SELECT u.name, u.role, rolename = coalece(b.name, c.name)
> FROM users u
> LEFT JOIN base b ON u.role = 'B'
> AND u.unitid = b.unitid
> LEFT JOIN command c ON u.role = 'C'
> AND u.unitid = c.unitid
>An alternative is to introduce a basecommand table, to gather common
>information, but I don't know enough about your business problem to
>say whether this is a good idea or not.

Multiply record fields with same mainID.

Hi everybody,
What's the most efficient way to get the following result:
I have a number of records with a MainID and a Value fields.
eg:
SubID MainID Value
1 1 1
2 1 1
3 1 2
4 1 3
5 2 1
6 2 1
7 3 2
8 3 2
I need the product of the Value field for each MainID.
The result has to be like this:
MainID PrValue
1 6 (1*1*2*3)
2 1 (1*1)
3 4 (2*2)
TIA,
Martin.select MainID, sum(value) as PrValue
from table
group by MainID
"martin" <kashaan007@.hotmail.com> wrote in message
news:Of%23nDPnRFHA.3144@.tk2msftngp13.phx.gbl...
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>|||Try,
use northwind
go
create table t (
SubID int,
MainID int,
Value int
)
go
insert into t values(1, 1, 1)
insert into t values(2, 1, 1)
insert into t values(3, 1, 2)
insert into t values(4, 1, 3)
insert into t values(5, 2, 1)
insert into t values(6, 2, 1)
insert into t values(7, 3, 2)
insert into t values(8, 3, 2)
go
select
MainID,
POWER(10, SUM(LOG10(Value)))
from
t
group by
MainID
drop table t
go
I took the idea from:
The T-SQL Banker
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
AMB
"martin" wrote:
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>|||Hi,AMB
It gives me a wrong output for the col1=2
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,4)
INSERT INTO #Test VALUES (2,2)
SELECT col1,POWER(10, SUM(LOG10(col2)))
FROM #Test GROUP BY col1
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
>
> AMB
> "martin" wrote:
> > Hi everybody,
> >
> > What's the most efficient way to get the following result:
> >
> > I have a number of records with a MainID and a Value fields.
> >
> > eg:
> >
> > SubID MainID Value
> > 1 1 1
> > 2 1 1
> > 3 1 2
> > 4 1 3
> > 5 2 1
> > 6 2 1
> > 7 3 2
> > 8 3 2
> >
> > I need the product of the Value field for each MainID.
> >
> > The result has to be like this:
> >
> > MainID PrValue
> > 1 6 (1*1*2*3)
> > 2 1 (1*1)
> > 3 4 (2*2)
> >
> > TIA,
> >
> > Martin.
> >
> >
> >
> >|||Alejandro's solution is good for values greater than zero. Here's a
more general solution for all integers (this one adapted from Celko and
others):
SELECT mainid,
CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM T
GROUP BY mainid
--
David Portas
SQL Server MVP
--|||Thanks a lot.
Works great.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
html/TheT-SQLBanker.asp
>
> AMB
> "martin" wrote:
> > Hi everybody,
> >
> > What's the most efficient way to get the following result:
> >
> > I have a number of records with a MainID and a Value fields.
> >
> > eg:
> >
> > SubID MainID Value
> > 1 1 1
> > 2 1 1
> > 3 1 2
> > 4 1 3
> > 5 2 1
> > 6 2 1
> > 7 3 2
> > 8 3 2
> >
> > I need the product of the Value field for each MainID.
> >
> > The result has to be like this:
> >
> > MainID PrValue
> > 1 6 (1*1*2*3)
> > 2 1 (1*1)
> > 3 4 (2*2)
> >
> > TIA,
> >
> > Martin.
> >
> >
> >
> >|||If we add +0.001 it works fine
SELECT col1,POWER(10, SUM(LOG10(col2))+0.001)
FROM #Test GROUP BY col1
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYVS5cnRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>|||Cast 10 to float.
SELECT
col1,
cast(POWER(cast(10 as float), SUM(LOG10(col2))) as decimal(5))
FROM #Test GROUP BY col1
go
AMB
"Uri Dimant" wrote:
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||Good catch!!!
AMB
"Uri Dimant" wrote:
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||David,
This is really a good one.
AMB
"David Portas" wrote:
> Alejandro's solution is good for values greater than zero. Here's a
> more general solution for all integers (this one adapted from Celko and
> others):
> SELECT mainid,
> CAST(ROUND(
> COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
> * SIGN(MIN(ABS(value)))
> * (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
> ,0) AS INTEGER) AS product
> FROM T
> GROUP BY mainid
> --
> David Portas
> SQL Server MVP
> --
>|||Use better the one posted by David Portas, and if you decide to use this one,
read the correction in my answer to Uri.
AMB
"martin" wrote:
> Thanks a lot.
> Works great.
> Martin.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
> html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||Ok thanks.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:210DC41A-BF9B-4211-9E16-995A554C0374@.microsoft.com...
> Use better the one posted by David Portas, and if you decide to use this
one,
> read the correction in my answer to Uri.
>
> AMB
> "martin" wrote:
> > Thanks a lot.
> >
> > Works great.
> >
> > Martin.
> >
> >
> > "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> > news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > > Try,
> > >
> > > use northwind
> > > go
> > >
> > > create table t (
> > > SubID int,
> > > MainID int,
> > > Value int
> > > )
> > > go
> > >
> > > insert into t values(1, 1, 1)
> > > insert into t values(2, 1, 1)
> > > insert into t values(3, 1, 2)
> > > insert into t values(4, 1, 3)
> > > insert into t values(5, 2, 1)
> > > insert into t values(6, 2, 1)
> > > insert into t values(7, 3, 2)
> > > insert into t values(8, 3, 2)
> > > go
> > >
> > > select
> > > MainID,
> > > POWER(10, SUM(LOG10(Value)))
> > > from
> > > t
> > > group by
> > > MainID
> > >
> > > drop table t
> > > go
> > >
> > > I took the idea from:
> > >
> > > The T-SQL Banker
> > >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
> > html/TheT-SQLBanker.asp
> > >
> > >
> > > AMB
> > >
> > > "martin" wrote:
> > >
> > > > Hi everybody,
> > > >
> > > > What's the most efficient way to get the following result:
> > > >
> > > > I have a number of records with a MainID and a Value fields.
> > > >
> > > > eg:
> > > >
> > > > SubID MainID Value
> > > > 1 1 1
> > > > 2 1 1
> > > > 3 1 2
> > > > 4 1 3
> > > > 5 2 1
> > > > 6 2 1
> > > > 7 3 2
> > > > 8 3 2
> > > >
> > > > I need the product of the Value field for each MainID.
> > > >
> > > > The result has to be like this:
> > > >
> > > > MainID PrValue
> > > > 1 6 (1*1*2*3)
> > > > 2 1 (1*1)
> > > > 3 4 (2*2)
> > > >
> > > > TIA,
> > > >
> > > > Martin.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >