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

Multiuser Databinding in SQL Server 2000?

Hi

I am developing one multi user application in windows environment using VC++ .NET with SQL Server 2000. How Can I create dataset for multi user data updating in my SQL Server 2000. Where I can get help and sample code?

Thanks

Jose

Jose,

The DataSet is an off-line data cache. The DataAdapter is used to submit the pending changes stored in the DataSet. In the DataAdapter's updating logic, you can specify your own concurrency options to control whether you want to use a "last in wins" approach with only the primary key column(s) in the WHERE clause, or use other columns in the WHERE clause to ensure you don't overwrite changes made by another user.

I hope this information proves helpful.

David Sceppa
ADO.NET Program Manager
Microsoft|||Hi

Thankyou for the nice explanation. One more question how can I write custom primary key value. for example (50710000001) like this. 5 is for year, 07 is for month, 10 is for place, and remaining is unique automatic generating number. How can I write program in SQL Server 2000.

Regards,
Jose

Multithreading in Sql Server

Hi,
Does sqlserver 2000 supports multi threading?
If so how can we implement it?
As per my knowledge it supports Symmetric multi
processing.
Can you please confirm this?
I posted this in english query section.Iam reposting it
here since this group is more relevant.
Thanks in adavance.
-SriramSQL automatically handles multiple processors... However when you go into
SQL Enterprise Manager, right click your server registration and select
properties, one of the tabs will allow you to choose how many of the
processors to use, 0 means use them all...
One of the diferences between SQL Standard and SQL Enterprise is how much
multi processing goes on during maintenance, like creating indexes... Of
course SQL Enterprise is more scalable.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>|||You don't normally control threading within an application. AFAIK - SQL
Server is a multi-threaded application; there is nothing to "implement".
Perhaps you are confusing multiple threads with multiple processors. If so,
then see Wayne's response.
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>

Multithreading in Sql Server

Hi,
Does sqlserver 2000 supports multi threading?
If so how can we implement it?
As per my knowledge it supports Symmetric multi
processing.
Can you please confirm this?
I posted this in english query section.Iam reposting it
here since this group is more relevant.
Thanks in adavance.
-Sriram
SQL automatically handles multiple processors... However when you go into
SQL Enterprise Manager, right click your server registration and select
properties, one of the tabs will allow you to choose how many of the
processors to use, 0 means use them all...
One of the diferences between SQL Standard and SQL Enterprise is how much
multi processing goes on during maintenance, like creating indexes... Of
course SQL Enterprise is more scalable.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>
|||You don't normally control threading within an application. AFAIK - SQL
Server is a multi-threaded application; there is nothing to "implement".
Perhaps you are confusing multiple threads with multiple processors. If so,
then see Wayne's response.
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>

Multithreading in Sql Server

Hi,
Does sqlserver 2000 supports multi threading?
If so how can we implement it?
As per my knowledge it supports Symmetric multi
processing.
Can you please confirm this?
I posted this in english query section.Iam reposting it
here since this group is more relevant.
Thanks in adavance.
-SriramSQL automatically handles multiple processors... However when you go into
SQL Enterprise Manager, right click your server registration and select
properties, one of the tabs will allow you to choose how many of the
processors to use, 0 means use them all...
One of the diferences between SQL Standard and SQL Enterprise is how much
multi processing goes on during maintenance, like creating indexes... Of
course SQL Enterprise is more scalable.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>|||You don't normally control threading within an application. AFAIK - SQL
Server is a multi-threaded application; there is nothing to "implement".
Perhaps you are confusing multiple threads with multiple processors. If so,
then see Wayne's response.
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>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 Parameters

I think I am really missing something here. I am trying to set up a
multi parameter dataset. I have read through all the postings and am
looking at the Wrox book SQL Server 2005 Reporting Services. It
states the following query will work
Select ProductSubcategoryId, [Name]
>From Production.ProductSubCategory
Where ProductSubCategoryID IN( @.ProductSubCategories )
so, I have created a SP on sql 2005 and set up @.ProductSubCategories as
a nvarchar(150)
I have turned the parameter on the RS side as multiselect and am
passing 2 values through to this SP
I have tried testing this just through sql management studio to no
avail.
What am I missing?What you are missing is a SQL Server thing. It is not a RS issue. You cannot
pass a string and use it in an in clause in a stored procedure. If you have
the below statement in the dataset (i.e. not calling a SP it will work).Here
is a previous post of mine on the subject:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bysnyder@.hotmail.com> wrote in message
news:1166195712.306140.100760@.j72g2000cwa.googlegroups.com...
>I think I am really missing something here. I am trying to set up a
> multi parameter dataset. I have read through all the postings and am
> looking at the Wrox book SQL Server 2005 Reporting Services. It
> states the following query will work
>
> Select ProductSubcategoryId, [Name]
>>From Production.ProductSubCategory
> Where ProductSubCategoryID IN( @.ProductSubCategories )
> so, I have created a SP on sql 2005 and set up @.ProductSubCategories as
> a nvarchar(150)
> I have turned the parameter on the RS side as multiselect and am
> passing 2 values through to this SP
> I have tried testing this just through sql management studio to no
> avail.
> What am I missing?
>

Multiple Values to select all values or deselect all values

I have 3 multi select report parameters on my report. Let's say a
user selects only a few of the values in each of the report parameter
drop downs but know they want to select all of the values with a click
of option button.
Then what I would like to do is have an option button that will allow
the user to select all of the option values or deselect all of the
values for all pararmeters by just selecting True or False from the
option button. Is there a way of doing this in Reporting Services?
Thanks for any help proved!On Jul 20, 11:01 am, trevorfuller1...@.gmail.com wrote:
> I have 3 multi select report parameters on my report. Let's say a
> user selects only a few of the values in each of the report parameter
> drop downs but know they want to select all of the values with a click
> of option button.
> Then what I would like to do is have an option button that will allow
> the user to select all of the option values or deselect all of the
> values for all pararmeters by just selecting True or False from the
> option button. Is there a way of doing this in Reporting Services?
> Thanks for any help proved!
There is not something built in to SSRS already; however, you could
create the functionality. You could have a parameter that if its value
is selected as True, you could send an 'All' type value back to the
stored procedure/query that is sourcing the report and then the stored
procedure could determine what 'All' means and select everything. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql