Multi-Value Parameter Width

Good afternoon,

I've designed a Reporting Services report that has a multi-value parameter. The report works just great. The only issue I'm running into is that the users are complaining that when they want to select the values, within the multi-value parameter box, they have to scroll it to the sides way too much. This also makes them select values by mistake.

Is there away to inscrease the width of the multi-value parameter box?

Best regards.

I guess you can do that by changing the stylesheet provided for controlling the layout and style of report manager. If your clients are using report manager, then read on:

There is a file called ReportingServices.css in Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager\Styles.

Edit that in notepad and locate a class called "msrs-multiValueDropDown" and in that class add width property as follows and save the file:

.msrs-multiValueDropDown

{

border: 1px DarkGray solid;

width: 200px;

}

Shyam

|||

That could work, yes. I'll sure try it tomorrow when I get to work.

My only problem with that approach is that it's an horizontal change. All reports will be affected unless theres a way to tell that report, or parameter, to use a specific sheet or value from the default sheet.|||

No, it cannot be done on a report by report basis.

So, can you please mark my post as answer?

Multi-Value Parameter Width

Good afternoon,

I've designed a Reporting Services report that has a multi-value parameter. The report works just great. The only issue I'm running into is that the users are complaining that when they want to select the values, within the multi-value parameter box, they have to scroll it to the sides way too much. This also makes them select values by mistake.

Is there away to inscrease the width of the multi-value parameter box?

Best regards.

I guess you can do that by changing the stylesheet provided for controlling the layout and style of report manager. If your clients are using report manager, then read on:

There is a file called ReportingServices.css in Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager\Styles.

Edit that in notepad and locate a class called "msrs-multiValueDropDown" and in that class add width property as follows and save the file:

.msrs-multiValueDropDown

{

border: 1px DarkGray solid;

width: 200px;

}

Shyam

|||

That could work, yes. I'll sure try it tomorrow when I get to work.

My only problem with that approach is that it's an horizontal change. All reports will be affected unless theres a way to tell that report, or parameter, to use a specific sheet or value from the default sheet.|||

No, it cannot be done on a report by report basis.

So, can you please mark my post as answer?

sql

Multivalue parameter textbox size

Hi,

In my report,I have multivalue parameter for this parameter the Available values are from the Dataset.Suppose the dataset contains Names like this:

'AL - Alabama Center (Tuscaloosa)',

'AL - Regional Control Center (Birmingham)',

'AR - Arkansas & Rock)',

'Arizona Department of Health'

But my problem is the Multivalue parameter TextBox is of fixed size.Now i want the size of the text box to the size of the name which is of length long in the Name.

Whether it is possible or not.

How to achieve this.

The size of the parameter area is not configurable. You can modify some of the style properties of the report viewer by following the instructions here: http://msdn2.microsoft.com/en-us/library/ms345247.aspx, there is just no way to set the size of the input boxes.|||

Sorry for high jacking this post but I can’t find any other reference to this issue.

I heard a rumour that this would be fixer in SQL Server 2005 SP2, is this the case?This issue is a real thorn in my side with constant user moaning.

Multivalue parameter textbox size

Hi,

In my report,I have multivalue parameter for this parameter the Available values are from the Dataset.Suppose the dataset contains Names like this:

'AL - Alabama Center (Tuscaloosa)',

'AL - Regional Control Center (Birmingham)',

'AR - Arkansas & Rock)',

'Arizona Department of Health'

But my problem is the Multivalue parameter TextBox is of fixed size.Now i want the size of the text box to the size of the name which is of length long in the Name.

Whether it is possible or not.

How to achieve this.

The size of the parameter area is not configurable. You can modify some of the style properties of the report viewer by following the instructions here: http://msdn2.microsoft.com/en-us/library/ms345247.aspx, there is just no way to set the size of the input boxes.|||

Sorry for high jacking this post but I can’t find any other reference to this issue.

I heard a rumour that this would be fixer in SQL Server 2005 SP2, is this the case?This issue is a real thorn in my side with constant user moaning.

Multi-Value parameter syntax error

I am new to reporting services...
I've created a report that uses a multi-value parameter based ona dataset
usign a simple select statement. It works fine when a single value is
selected, but when multiple values are selected returns the error Incorrect
Syntax near ','.
How can the report code be modified to pass multiple values with the correct
syntax?See my response to your other posting (which is basically, show us what you
did). My guess is you have an incorrect SQL statement OR you are not going
against SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:7342117C-9715-477F-A9F4-E748C2EA3A77@.microsoft.com...
>I am new to reporting services...
> I've created a report that uses a multi-value parameter based ona dataset
> usign a simple select statement. It works fine when a single value is
> selected, but when multiple values are selected returns the error
> Incorrect
> Syntax near ','.
> How can the report code be modified to pass multiple values with the
> correct
> syntax?
>

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 Select All

Is there a way to tell if a user has checked Select All for a multi-valued
parameter? I'd like my query to be different if they have.I recall from a previous post that this was not possible.
here is the link to the post.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?&query=tell+if+user+selected+all&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=e86a166d-61fa-44d8-867c-fc4f29781b5b&mid=e86a166d-61fa-44d8-867c-fc4f29781b5b
"Michelle" wrote:
> Is there a way to tell if a user has checked Select All for a multi-valued
> parameter? I'd like my query to be different if they have.

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 Parameter Problem

I'm creating a report that selects data from one table. I'm trying to
add a parameter that will allow the user to select the data based on a
selection from one column (SpindleName). I've added this column in the
report parameters, checked the multi-value box, set the available
values to "from query" to the dataset & field, set the default values
to "from query" to the same dataset & field. When I execute the query,
I get asked for the SpindleName, but the box contains no values other
than "NULL" and "BLANK". If I type the spindle name into the box, I
get the results. Why isn't the list being populated with the values
from the spindlename column ?
Here is the query:
SELECT StationName, SpindleName, PartId, TimeStamp
FROM dbo.Readings
WHERE SpindleName IN (@.SpindleName)
I'm using SQL 2005 Express & VS 2005
Any advice would be appreciated.
Thank-you
JeffWhat is the source query for the parameter?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||I may be wrong but it seems to me there is some confusion here. Several
things:
1. Unless you have cascading parameters (the list to select from parameter B
is dependent on the selection from parameter A) then you should have a query
that returns the value you want to select from.
2. Default means a single value. If you point to a dataset with multiple
values I don't know what it does except that is incorrect.
So, for instance by my take on this you want to this.
Have two datasets. One dataset that returns the list of SpindleName. The
other that retrieves the data you desire.
Select distinct SpindleName from Readings
Set both your label and value for the SpindleName parameter to the field
SpindleName from the above query.
Then your second query you have below will return what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||For your point #2, if your parameter's default value points to a dataset
("From Query") with multiple values, they will show up with all of them
selected in the drop down list. This is desirable in some cases.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23P5CZRGrIHA.4376@.TK2MSFTNGP06.phx.gbl...
>I may be wrong but it seems to me there is some confusion here. Several
>things:
> 1. Unless you have cascading parameters (the list to select from parameter
> B is dependent on the selection from parameter A) then you should have a
> query that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvica1@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>> I'm creating a report that selects data from one table. I'm trying to
>> add a parameter that will allow the user to select the data based on a
>> selection from one column (SpindleName). I've added this column in the
>> report parameters, checked the multi-value box, set the available
>> values to "from query" to the dataset & field, set the default values
>> to "from query" to the same dataset & field. When I execute the query,
>> I get asked for the SpindleName, but the box contains no values other
>> than "NULL" and "BLANK". If I type the spindle name into the box, I
>> get the results. Why isn't the list being populated with the values
>> from the spindlename column ?
>> Here is the query:
>> SELECT StationName, SpindleName, PartId, TimeStamp
>> FROM dbo.Readings
>> WHERE SpindleName IN (@.SpindleName)
>> I'm using SQL 2005 Express & VS 2005
>> Any advice would be appreciated.
>> Thank-you
>> Jeff
>|||On May 2, 10:53=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I may be wrong but it seems to me there is some confusion here. Several
> things:
> 1. Unless you have cascading parameters (the list to select from parameter= B
> is dependent on the selection from parameter A) then you should have a que=ry
> that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvi...@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>
> > I'm creating a report that selects data from one table. I'm trying to
> > add a parameter that will allow the user to select the data based on a
> > selection from one column (SpindleName). I've added this column in the
> > report parameters, checked the multi-value box, set the available
> > values to "from query" to the dataset & field, set the default values
> > to "from query" to the same dataset & field. When I execute the query,
> > I get asked for the SpindleName, but the box contains no values other
> > than "NULL" and "BLANK". If I type the spindle name into the box, I
> > get the results. Why isn't the list being populated with the values
> > from the spindlename column ?
> > Here is the query:
> > SELECT StationName, SpindleName, PartId, TimeStamp
> > FROM dbo.Readings
> > WHERE SpindleName IN (@.SpindleName)
> > I'm using SQL 2005 Express & VS 2005
> > Any advice would be appreciated.
> > Thank-you
> > Jeff- Hide quoted text -
> - Show quoted text -
Thanks Bruce - it worked perfectly!
Regards
Jeff

multi-value parameter in Master report passing to single param sub-report in a list.

Here's tricky one.

I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.

Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?

I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.

Essentially, I want to create a for each loop.

Any ideas?

I have the same problem where I need to run a subreport for each value in a multivalued paramter. Does anyone have a solution?
|||

Here is one way to accomplish this if your parameter values come from a query:

In your main report, add a list to the body bound to the same data set as the multi-value parameter.

On the list, add a Filter with the following properties:

Expression: The same as used to populate the parameter value.

Operator: In

Value: Use the parameter value (i.e. =Fields!MyMultiValueParam.Value) Because the parameter is a multi-value parameter, this will evaluate to an array.

sql

MultiValue Parameter Help

I have passed a list of parameters to a multivalue parameter in a SQL Report.

I can click the drop down and see the list of parameters that were passed.

In my select statement for the report, here is the condition I am using in the where clause.

AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN (@.RUs))

Nothing is returned in the result set if more than one value is passed to the multivalue parameter.

Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.

For example, in the multivalue dropdown list, there are the following values:

211

212

If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.

what is the value type for your MV paramater? Is it the same as the sql value type?

|||

>>Shouldn't the above conidition have the effect that no matter how many account numbers there are

You might think so, but no <g>. You have to build the SQL, there's no magic here that allows the SQL to automatically understand a collection of values -- which is what the multiselect parameter is.

I'll assume that your values for the dropdown list are string-type, because that is the hardest case usually and because you're using SUBSTRING() on your data column...

You need to construct your SQL something like this:

="SELECT " & blah blah blah & "WHERE " & blah blah &

" AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN ('" &

JOIN(Parameters!RUs.Value,"','") & "') "

... the use of red in the expression above is just to make sure you can see the single quotes versus the double quotes. I don't want to add spaces between the characters because those spaces would destroy the syntax we're trying to build.

What I'm trying to show you is that you need to end up with a SQL expression that has the delimited strings in an IN clause -- IN ('211','212') -- and that, while annoying when expressed as above, it is possible to do it <s>. This is because the JOIN function will take the various values for each of the parameter values in the collection and concatenate them with a delimiter that, in this case, is three characters long: ',' .

We add the outside or "embracing" single quote delimiters for the first and last items in the collection manually.

If there is only one item in the dropdown selected, the JOIN doesn't add anything but the one value, and the result is (say) IN ('211'), perfectly valid.

If you find it annoying to look at the SQL this way (I do) you can do what I've advised others to do several times in similar situations: just write a query that looks like this:

=Code.GetSQL()

... and write a little embedded Function GetSQL() AS String in your report, that returns the query as a string. You'll write a more maintainable version of the parsing code if you don't have to do it all in-line as above. If you need to do any type conversions or iterate through the parameter collection for any other reason, it will be easier.

HTH,

>L<

|||

Lisa:

Thank you so much for such an involved reply. I like that answer and feel that you are most likely correct. However, I'm having trouble getting that to work for me.

Is the JOIN function that you are using in your code the one that can be used by right clicking a field on the layout tab of the report and selecting expression?

I'm not sure how I can use that JOIN in the SQL select statement on the Data tab. It doesn't seem to me that the code you have shown can be used in a standard select statement. How would I do that? As far as I know, JOIN in "natural SQL" can be INNER JOIN, OUTER JOIN, etc. However, I've never seen just JOIN used as you have here.

Also you've used the Parameters!RUs.Value syntax here. Shouldn't that be @.RUs as it is the data portion of the report and not the layout of the report?

Mainiac007:

The RUs parameter in my stored procedure is varchar(1000).

The multivalue report parameter is of type String.

The ChartOfAccountsAccountNo is of type varchar(24).

|||

the JOIN she is referring to is not a sql command but a vb function.

|||

HI there,

I'm sorry the reply was involved -- unfortunately it has to be <s>. I've seen a bunch of people saying you can't do this, which is why I bothered to write out the whole thing <s>. And it seems I guessed right about your data types, so you did need the full information.

To answer your question(s), and I promise it really does work <s>.... Look really carefully at the expression I wrote:

I didn't write a SQL statement. I wrote an EXPRESSION which, when evaluated, is a SQL statement.

Notice the = at the beginning. Notice the double quotes -- that's VB expression quotes, not SQL delimiters -- around the parts of the expression.

Notice, also, that I said in my reply that you could do the same thing by using the expression =Code.GetSql(), where GetSql is an embedded function in the Report Code. Obviously the SQL engine wouldn't understand Code.GetSql() either, but that makes no difference <s>.

Either way, in-line expression or function, the *returned result* of the expression is a valid SQL statement, which has been dynamically built up from strings to meet your requirements. The string which has been built up is what is going to get sent to the SQL engine, not the expression that created it.

As for why I used Parameters!RUs.Value rather than @.RUs, I think that will be obvious when you grok what I just said <s>. I'm not talking SQL when I'm creating the string. I'm talking report-scoped object syntax, and I'm talking to the collection of parameter objects that represents your multi-select parameter. The JOIN function is VB, and I'm using it in a way that is entirely appropriate to the problem at hand. (Considering the number of times I have seen people say "you can't do that with string values", I flatter myself that it's a pretty cool idea, too <g>.)

Does this make more sense now?

>L<

|||I could not have said it better myself.|||

>> I could not have said it better myself

gee thanks <rofl> No offense, Maniac007, but ...

you were one of the people who appeared to need help on this exact issue and hence one of the reasons I posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012961&SiteID=1) .

>L<

|||yup, that was LAST year.... since then 6 months of working with ssrs has taught me a lot.|||

Sorry, Mainiac007, I didn't mean you didn't *know* this. I meant it was obviously hard to write out and explain -- otherwise, obviously, you or many other people who post here would have done it <s>.

I've avoided explaining it many times as well, this time I just decided to bite the bullet. Let's hope the explanation is sufficiently clear and that people link to this thread as necessary.

>L<

|||

My wife would kill me for saying this....but,,,,

"Just like a women, to have the last word."

|||

Lisa,

Thank you so much for your help. I think I'm at least following you now, but I still have an error in my SELECT statement and I can't seem to figure out what it is.

The error I get when running the report is: Incorrect syntax near 'GL_TRX'.

|||

what are you summing this as...

SUM(GL_TRX.GLTRXAMOUNT) as what?|||

There are a couple of things that I see...

1. As Mainiac007 has already told you, you probably need an alias on your calculated (SUM(GL_TRX.GLTRXAMOUNT)) field.

2. Remember what I told you about those extra spaces you can't put in? Well, you put them in <g>.

" AND (SUBSTRING(CHART_OF_ACCOUNTS.CHARTOFACCTSACCOUNTNO,9,3) IN (' " &

JOIN(Parameters!RUs.Value," ',' ") & " ') " ...

you need to remove the extra spaces you put in, in all the places I marked in red where you have a single and double quote next to each other. Do you see why? Let's say your parameter value is XYZ. You want the chart of accts substring to match 'XYZ', you don't want it to match SPACE(1) + 'XYZ' + SPACE(1). Or, you could leave the spaces in but then you would have to change your substring expression to delimit with a space on either side.

3. I don't think that, when you're using a dynamically constructed query like this, you should use explicit params the way you're doing it embedded in the strings. It may work in some instances, it doesn't work out too well for me. So, instead of

"GL_TRX.GLTRXDATE BETWEEN @.StartDate AND @.EndDate"

... I would write:

"GL_TRX.GLTRXDATE BETWEEN '" & CSTR(@.StartDate) & "' AND '" & CSTR(@.EndDate) & "' "

... or something like that. Notice I've put the single quotes in red again so you can see them next to the double quotes.

4. I have two suggestions for you that might help you debug and also make the code less messy to maintain:

The first, I've already suggested (put the sql creation into an embedded proc so you can break it up and read what you are doing, append into a StringBuilder or whatever and then return the full statement.ToString() when you're done.)

The second is to create a simple report with the same parameters and have a text box that has the same expression building syntax you're using here. It doesn't matter what the rest of the report looks like, you just want one big textbox so that you can read it. Here's why this is a good idea:

* -- if you have basic VB-syntax problems, you'll know because the report won't be able to parse what you did when you try to preview it

* -- if you don't have a VB-syntax problem, you'll see what you did in the body of the report. You should be able to copy and paste it somewhere else and run it as sql. If it's correct, it will run. If not, you'll be able to see why <g>.

* -- once you get it right, you can move that same expression-building code into the "real" report.

HTH,

>L<

|||

I have just run into a similiar situation. Where I have to pass a parameter (multi value) to a stored procedure.

The sproc use a where filed in(@.Centers) command.

When I select 1 value it works fine, when I select all or more then one, then no data is returned.

It seems the value returned from the MV is one,two,three. etc...

where to accurately pass this it should be 'one',two','three'

a hint on how to get it done this way?

MultiValue Parameter Help

I have passed a list of parameters to a multivalue parameter in a SQL Report.

I can click the drop down and see the list of parameters that were passed.

In my select statement for the report, here is the condition I am using in the where clause.

AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN (@.RUs))

Nothing is returned in the result set if more than one value is passed to the multivalue parameter.

Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.

For example, in the multivalue dropdown list, there are the following values:

211

212

If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.

what is the value type for your MV paramater? Is it the same as the sql value type?

|||

>>Shouldn't the above conidition have the effect that no matter how many account numbers there are

You might think so, but no <g>. You have to build the SQL, there's no magic here that allows the SQL to automatically understand a collection of values -- which is what the multiselect parameter is.

I'll assume that your values for the dropdown list are string-type, because that is the hardest case usually and because you're using SUBSTRING() on your data column...

You need to construct your SQL something like this:

="SELECT " & blah blah blah & "WHERE " & blah blah &

" AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN ('" &

JOIN(Parameters!RUs.Value,"','") & "') "

... the use of red in the expression above is just to make sure you can see the single quotes versus the double quotes. I don't want to add spaces between the characters because those spaces would destroy the syntax we're trying to build.

What I'm trying to show you is that you need to end up with a SQL expression that has the delimited strings in an IN clause -- IN ('211','212') -- and that, while annoying when expressed as above, it is possible to do it <s>. This is because the JOIN function will take the various values for each of the parameter values in the collection and concatenate them with a delimiter that, in this case, is three characters long: ',' .

We add the outside or "embracing" single quote delimiters for the first and last items in the collection manually.

If there is only one item in the dropdown selected, the JOIN doesn't add anything but the one value, and the result is (say) IN ('211'), perfectly valid.

If you find it annoying to look at the SQL this way (I do) you can do what I've advised others to do several times in similar situations: just write a query that looks like this:

=Code.GetSQL()

... and write a little embedded Function GetSQL() AS String in your report, that returns the query as a string. You'll write a more maintainable version of the parsing code if you don't have to do it all in-line as above. If you need to do any type conversions or iterate through the parameter collection for any other reason, it will be easier.

HTH,

>L<

|||

Lisa:

Thank you so much for such an involved reply. I like that answer and feel that you are most likely correct. However, I'm having trouble getting that to work for me.

Is the JOIN function that you are using in your code the one that can be used by right clicking a field on the layout tab of the report and selecting expression?

I'm not sure how I can use that JOIN in the SQL select statement on the Data tab. It doesn't seem to me that the code you have shown can be used in a standard select statement. How would I do that? As far as I know, JOIN in "natural SQL" can be INNER JOIN, OUTER JOIN, etc. However, I've never seen just JOIN used as you have here.

Also you've used the Parameters!RUs.Value syntax here. Shouldn't that be @.RUs as it is the data portion of the report and not the layout of the report?

Mainiac007:

The RUs parameter in my stored procedure is varchar(1000).

The multivalue report parameter is of type String.

The ChartOfAccountsAccountNo is of type varchar(24).

|||

the JOIN she is referring to is not a sql command but a vb function.

|||

HI there,

I'm sorry the reply was involved -- unfortunately it has to be <s>. I've seen a bunch of people saying you can't do this, which is why I bothered to write out the whole thing <s>. And it seems I guessed right about your data types, so you did need the full information.

To answer your question(s), and I promise it really does work <s>.... Look really carefully at the expression I wrote:

I didn't write a SQL statement. I wrote an EXPRESSION which, when evaluated, is a SQL statement.

Notice the = at the beginning. Notice the double quotes -- that's VB expression quotes, not SQL delimiters -- around the parts of the expression.

Notice, also, that I said in my reply that you could do the same thing by using the expression =Code.GetSql(), where GetSql is an embedded function in the Report Code. Obviously the SQL engine wouldn't understand Code.GetSql() either, but that makes no difference <s>.

Either way, in-line expression or function, the *returned result* of the expression is a valid SQL statement, which has been dynamically built up from strings to meet your requirements. The string which has been built up is what is going to get sent to the SQL engine, not the expression that created it.

As for why I used Parameters!RUs.Value rather than @.RUs, I think that will be obvious when you grok what I just said <s>. I'm not talking SQL when I'm creating the string. I'm talking report-scoped object syntax, and I'm talking to the collection of parameter objects that represents your multi-select parameter. The JOIN function is VB, and I'm using it in a way that is entirely appropriate to the problem at hand. (Considering the number of times I have seen people say "you can't do that with string values", I flatter myself that it's a pretty cool idea, too <g>.)

Does this make more sense now?

>L<

|||I could not have said it better myself.|||

>> I could not have said it better myself

gee thanks <rofl> No offense, Maniac007, but ...

you were one of the people who appeared to need help on this exact issue and hence one of the reasons I posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012961&SiteID=1) .

>L<

|||yup, that was LAST year.... since then 6 months of working with ssrs has taught me a lot.|||

Sorry, Mainiac007, I didn't mean you didn't *know* this. I meant it was obviously hard to write out and explain -- otherwise, obviously, you or many other people who post here would have done it <s>.

I've avoided explaining it many times as well, this time I just decided to bite the bullet. Let's hope the explanation is sufficiently clear and that people link to this thread as necessary.

>L<

|||

My wife would kill me for saying this....but,,,,

"Just like a women, to have the last word."

|||

Lisa,

Thank you so much for your help. I think I'm at least following you now, but I still have an error in my SELECT statement and I can't seem to figure out what it is.

The error I get when running the report is: Incorrect syntax near 'GL_TRX'.

|||

what are you summing this as...

SUM(GL_TRX.GLTRXAMOUNT) as what?

|||

There are a couple of things that I see...

1. As Mainiac007 has already told you, you probably need an alias on your calculated (SUM(GL_TRX.GLTRXAMOUNT)) field.

2. Remember what I told you about those extra spaces you can't put in? Well, you put them in <g>.

" AND (SUBSTRING(CHART_OF_ACCOUNTS.CHARTOFACCTSACCOUNTNO,9,3) IN (' " &

JOIN(Parameters!RUs.Value," ',' ") & " ') " ...

you need to remove the extra spaces you put in, in all the places I marked in red where you have a single and double quote next to each other. Do you see why? Let's say your parameter value is XYZ. You want the chart of accts substring to match 'XYZ', you don't want it to match SPACE(1) + 'XYZ' + SPACE(1). Or, you could leave the spaces in but then you would have to change your substring expression to delimit with a space on either side.

3. I don't think that, when you're using a dynamically constructed query like this, you should use explicit params the way you're doing it embedded in the strings. It may work in some instances, it doesn't work out too well for me. So, instead of

"GL_TRX.GLTRXDATE BETWEEN @.StartDate AND @.EndDate"

... I would write:

"GL_TRX.GLTRXDATE BETWEEN '" & CSTR(@.StartDate) & "' AND '" & CSTR(@.EndDate) & "' "

... or something like that. Notice I've put the single quotes in red again so you can see them next to the double quotes.

4. I have two suggestions for you that might help you debug and also make the code less messy to maintain:

The first, I've already suggested (put the sql creation into an embedded proc so you can break it up and read what you are doing, append into a StringBuilder or whatever and then return the full statement.ToString() when you're done.)

The second is to create a simple report with the same parameters and have a text box that has the same expression building syntax you're using here. It doesn't matter what the rest of the report looks like, you just want one big textbox so that you can read it. Here's why this is a good idea:

* -- if you have basic VB-syntax problems, you'll know because the report won't be able to parse what you did when you try to preview it

* -- if you don't have a VB-syntax problem, you'll see what you did in the body of the report. You should be able to copy and paste it somewhere else and run it as sql. If it's correct, it will run. If not, you'll be able to see why <g>.

* -- once you get it right, you can move that same expression-building code into the "real" report.

HTH,

>L<

|||

I have just run into a similiar situation. Where I have to pass a parameter (multi value) to a stored procedure.

The sproc use a where filed in(@.Centers) command.

When I select 1 value it works fine, when I select all or more then one, then no data is returned.

It seems the value returned from the MV is one,two,three. etc...

where to accurately pass this it should be 'one',two','three'

a hint on how to get it done this way?

MultiValue Parameter Help

I have passed a list of parameters to a multivalue parameter in a SQL Report.

I can click the drop down and see the list of parameters that were passed.

In my select statement for the report, here is the condition I am using in the where clause.

AND(SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3)IN(@.RUs))

Nothing is returned in the result set if more than one value is passed to the multivalue parameter.

Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.

For example, in the multivalue dropdown list, there are the following values:

211

212

If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.

what is the value type for your MV paramater? Is it the same as the sql value type?

|||

>>Shouldn't the above conidition have the effect that no matter how many account numbers there are

You might think so, but no <g>. You have to build the SQL, there's no magic here that allows the SQL to automatically understand a collection of values -- which is what the multiselect parameter is.

I'll assume that your values for the dropdown list are string-type, because that is the hardest case usually and because you're using SUBSTRING() on your data column...

You need to construct your SQL something like this:

="SELECT " & blah blah blah & "WHERE " & blah blah &

" AND(SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN ('" &

JOIN(Parameters!RUs.Value,"','") & "') "

... the use of red in the expression above is just to make sure you can see the single quotes versus the double quotes. I don't want to add spaces between the characters because those spaces would destroy the syntax we're trying to build.

What I'm trying to show you is that you need to end up with a SQL expression that has the delimited strings in an IN clause -- IN ('211','212') -- and that, while annoying when expressed as above, it is possible to do it <s>. This is because the JOIN function will take the various values for each of the parameter values in the collection and concatenate them with a delimiter that, in this case, is three characters long: ',' .

We add the outside or "embracing" single quote delimiters for the first and last items in the collection manually.

If there is only one item in the dropdown selected, the JOIN doesn't add anything but the one value, and the result is (say) IN ('211'), perfectly valid.

If you find it annoying to look at the SQL this way (I do) you can do what I've advised others to do several times in similar situations: just write a query that looks like this:

=Code.GetSQL()

... and write a little embedded Function GetSQL() AS String in your report, that returns the query as a string. You'll write a more maintainable version of the parsing code if you don't have to do it all in-line as above. If you need to do any type conversions or iterate through the parameter collection for any other reason, it will be easier.

HTH,

>L<

|||

Lisa:

Thank you so much for such an involved reply. I like that answer and feel that you are most likely correct. However, I'm having trouble getting that to work for me.

Is the JOIN function that you are using in your code the one that can be used by right clicking a field on the layout tab of the report and selecting expression?

I'm not sure how I can use that JOIN in the SQL select statement on the Data tab. It doesn't seem to me that the code you have shown can be used in a standard select statement. How would I do that? As far as I know, JOIN in "natural SQL" can be INNER JOIN, OUTER JOIN, etc. However, I've never seen just JOIN used as you have here.

Also you've used the Parameters!RUs.Value syntax here. Shouldn't that be @.RUs as it is the data portion of the report and not the layout of the report?

Mainiac007:

The RUs parameter in my stored procedure is varchar(1000).

The multivalue report parameter is of type String.

The ChartOfAccountsAccountNo is of type varchar(24).

|||

the JOIN she is referring to is not a sql command but a vb function.

|||

HI there,

I'm sorry the reply was involved -- unfortunately it has to be <s>. I've seen a bunch of people saying you can't do this, which is why I bothered to write out the whole thing <s>. And it seems I guessed right about your data types, so you did need the full information.

To answer your question(s), and I promise it really does work <s>.... Look really carefully at the expression I wrote:

I didn't write a SQL statement. I wrote an EXPRESSION which, when evaluated, is a SQL statement.

Notice the = at the beginning. Notice the double quotes -- that's VB expression quotes, not SQL delimiters -- around the parts of the expression.

Notice, also, that I said in my reply that you could do the same thing by using the expression =Code.GetSql(), where GetSql is an embedded function in the Report Code. Obviously the SQL engine wouldn't understand Code.GetSql() either, but that makes no difference <s>.

Either way, in-line expression or function, the *returned result* of the expression is a valid SQL statement, which has been dynamically built up from strings to meet your requirements. The string which has been built up is what is going to get sent to the SQL engine, not the expression that created it.

As for why I used Parameters!RUs.Value rather than @.RUs, I think that will be obvious when you grok what I just said <s>. I'm not talking SQL when I'm creating the string. I'm talking report-scoped object syntax, and I'm talking to the collection of parameter objects that represents your multi-select parameter. The JOIN function is VB, and I'm using it in a way that is entirely appropriate to the problem at hand. (Considering the number of times I have seen people say "you can't do that with string values", I flatter myself that it's a pretty cool idea, too <g>.)

Does this make more sense now?

>L<

|||I could not have said it better myself.|||

>> I could not have said it better myself

gee thanks <rofl> No offense, Maniac007, but ...

you were one of the people who appeared to need help on this exact issue and hence one of the reasons I posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012961&SiteID=1) .

>L<

|||yup, that was LAST year.... since then 6 months of working with ssrs has taught me a lot.|||

Sorry, Mainiac007, I didn't mean you didn't *know* this. I meant it was obviously hard to write out and explain -- otherwise, obviously, you or many other people who post here would have done it <s>.

I've avoided explaining it many times as well, this time I just decided to bite the bullet. Let's hope the explanation is sufficiently clear and that people link to this thread as necessary.

>L<

|||

My wife would kill me for saying this....but,,,,

"Just like a women, to have the last word."

|||

Lisa,

Thank you so much for your help. I think I'm at least following you now, but I still have an error in my SELECT statement and I can't seem to figure out what it is.

The error I get when running the report is: Incorrect syntax near 'GL_TRX'.

|||

what are you summing this as...

SUM(GL_TRX.GLTRXAMOUNT) as what?|||

There are a couple of things that I see...

1. As Mainiac007 has already told you, you probably need an alias on your calculated (SUM(GL_TRX.GLTRXAMOUNT)) field.

2. Remember what I told you about those extra spaces you can't put in? Well, you put them in <g>.

" AND (SUBSTRING(CHART_OF_ACCOUNTS.CHARTOFACCTSACCOUNTNO,9,3) IN (' " &

JOIN(Parameters!RUs.Value," ',' ") & " ') " ...

you need to remove the extra spaces you put in, in all the places I marked in red where you have a single and double quote next to each other. Do you see why? Let's say your parameter value is XYZ. You want the chart of accts substring to match 'XYZ', you don't want it to match SPACE(1) + 'XYZ' + SPACE(1). Or, you could leave the spaces in but then you would have to change your substring expression to delimit with a space on either side.

3. I don't think that, when you're using a dynamically constructed query like this, you should use explicit params the way you're doing it embedded in the strings. It may work in some instances, it doesn't work out too well for me. So, instead of

"GL_TRX.GLTRXDATE BETWEEN @.StartDate AND @.EndDate"

... I would write:

"GL_TRX.GLTRXDATE BETWEEN '" & CSTR(@.StartDate) & "' AND '" & CSTR(@.EndDate) & "' "

... or something like that. Notice I've put the single quotes in red again so you can see them next to the double quotes.

4. I have two suggestions for you that might help you debug and also make the code less messy to maintain:

The first, I've already suggested (put the sql creation into an embedded proc so you can break it up and read what you are doing, append into a StringBuilder or whatever and then return the full statement.ToString() when you're done.)

The second is to create a simple report with the same parameters and have a text box that has the same expression building syntax you're using here. It doesn't matter what the rest of the report looks like, you just want one big textbox so that you can read it. Here's why this is a good idea:

* -- if you have basic VB-syntax problems, you'll know because the report won't be able to parse what you did when you try to preview it

* -- if you don't have a VB-syntax problem, you'll see what you did in the body of the report. You should be able to copy and paste it somewhere else and run it as sql. If it's correct, it will run. If not, you'll be able to see why <g>.

* -- once you get it right, you can move that same expression-building code into the "real" report.

HTH,

>L<

|||

I have just run into a similiar situation. Where I have to pass a parameter (multi value) to a stored procedure.

The sproc use a where filed in(@.Centers) command.

When I select 1 value it works fine, when I select all or more then one, then no data is returned.

It seems the value returned from the MV is one,two,three. etc...

where to accurately pass this it should be 'one',two','three'

a hint on how to get it done this way?

MultiValue Parameter Help

I have passed a list of parameters to a multivalue parameter in a SQL Report.

I can click the drop down and see the list of parameters that were passed.

In my select statement for the report, here is the condition I am using in the where clause.

AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN (@.RUs))

Nothing is returned in the result set if more than one value is passed to the multivalue parameter.

Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.

For example, in the multivalue dropdown list, there are the following values:

211

212

If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.

what is the value type for your MV paramater? Is it the same as the sql value type?

|||

>>Shouldn't the above conidition have the effect that no matter how many account numbers there are

You might think so, but no <g>. You have to build the SQL, there's no magic here that allows the SQL to automatically understand a collection of values -- which is what the multiselect parameter is.

I'll assume that your values for the dropdown list are string-type, because that is the hardest case usually and because you're using SUBSTRING() on your data column...

You need to construct your SQL something like this:

="SELECT " & blah blah blah & "WHERE " & blah blah &

" AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN ('" &

JOIN(Parameters!RUs.Value,"','") & "') "

... the use of red in the expression above is just to make sure you can see the single quotes versus the double quotes. I don't want to add spaces between the characters because those spaces would destroy the syntax we're trying to build.

What I'm trying to show you is that you need to end up with a SQL expression that has the delimited strings in an IN clause -- IN ('211','212') -- and that, while annoying when expressed as above, it is possible to do it <s>. This is because the JOIN function will take the various values for each of the parameter values in the collection and concatenate them with a delimiter that, in this case, is three characters long: ',' .

We add the outside or "embracing" single quote delimiters for the first and last items in the collection manually.

If there is only one item in the dropdown selected, the JOIN doesn't add anything but the one value, and the result is (say) IN ('211'), perfectly valid.

If you find it annoying to look at the SQL this way (I do) you can do what I've advised others to do several times in similar situations: just write a query that looks like this:

=Code.GetSQL()

... and write a little embedded Function GetSQL() AS String in your report, that returns the query as a string. You'll write a more maintainable version of the parsing code if you don't have to do it all in-line as above. If you need to do any type conversions or iterate through the parameter collection for any other reason, it will be easier.

HTH,

>L<

|||

Lisa:

Thank you so much for such an involved reply. I like that answer and feel that you are most likely correct. However, I'm having trouble getting that to work for me.

Is the JOIN function that you are using in your code the one that can be used by right clicking a field on the layout tab of the report and selecting expression?

I'm not sure how I can use that JOIN in the SQL select statement on the Data tab. It doesn't seem to me that the code you have shown can be used in a standard select statement. How would I do that? As far as I know, JOIN in "natural SQL" can be INNER JOIN, OUTER JOIN, etc. However, I've never seen just JOIN used as you have here.

Also you've used the Parameters!RUs.Value syntax here. Shouldn't that be @.RUs as it is the data portion of the report and not the layout of the report?

Mainiac007:

The RUs parameter in my stored procedure is varchar(1000).

The multivalue report parameter is of type String.

The ChartOfAccountsAccountNo is of type varchar(24).

|||

the JOIN she is referring to is not a sql command but a vb function.

|||

HI there,

I'm sorry the reply was involved -- unfortunately it has to be <s>. I've seen a bunch of people saying you can't do this, which is why I bothered to write out the whole thing <s>. And it seems I guessed right about your data types, so you did need the full information.

To answer your question(s), and I promise it really does work <s>.... Look really carefully at the expression I wrote:

I didn't write a SQL statement. I wrote an EXPRESSION which, when evaluated, is a SQL statement.

Notice the = at the beginning. Notice the double quotes -- that's VB expression quotes, not SQL delimiters -- around the parts of the expression.

Notice, also, that I said in my reply that you could do the same thing by using the expression =Code.GetSql(), where GetSql is an embedded function in the Report Code. Obviously the SQL engine wouldn't understand Code.GetSql() either, but that makes no difference <s>.

Either way, in-line expression or function, the *returned result* of the expression is a valid SQL statement, which has been dynamically built up from strings to meet your requirements. The string which has been built up is what is going to get sent to the SQL engine, not the expression that created it.

As for why I used Parameters!RUs.Value rather than @.RUs, I think that will be obvious when you grok what I just said <s>. I'm not talking SQL when I'm creating the string. I'm talking report-scoped object syntax, and I'm talking to the collection of parameter objects that represents your multi-select parameter. The JOIN function is VB, and I'm using it in a way that is entirely appropriate to the problem at hand. (Considering the number of times I have seen people say "you can't do that with string values", I flatter myself that it's a pretty cool idea, too <g>.)

Does this make more sense now?

>L<

|||I could not have said it better myself.|||

>> I could not have said it better myself

gee thanks <rofl> No offense, Maniac007, but ...

you were one of the people who appeared to need help on this exact issue and hence one of the reasons I posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012961&SiteID=1) .

>L<

|||yup, that was LAST year.... since then 6 months of working with ssrs has taught me a lot.|||

Sorry, Mainiac007, I didn't mean you didn't *know* this. I meant it was obviously hard to write out and explain -- otherwise, obviously, you or many other people who post here would have done it <s>.

I've avoided explaining it many times as well, this time I just decided to bite the bullet. Let's hope the explanation is sufficiently clear and that people link to this thread as necessary.

>L<

|||

My wife would kill me for saying this....but,,,,

"Just like a women, to have the last word."

|||

Lisa,

Thank you so much for your help. I think I'm at least following you now, but I still have an error in my SELECT statement and I can't seem to figure out what it is.

The error I get when running the report is: Incorrect syntax near 'GL_TRX'.

|||

what are you summing this as...

SUM(GL_TRX.GLTRXAMOUNT) as what?|||

There are a couple of things that I see...

1. As Mainiac007 has already told you, you probably need an alias on your calculated (SUM(GL_TRX.GLTRXAMOUNT)) field.

2. Remember what I told you about those extra spaces you can't put in? Well, you put them in <g>.

" AND (SUBSTRING(CHART_OF_ACCOUNTS.CHARTOFACCTSACCOUNTNO,9,3) IN (' " &

JOIN(Parameters!RUs.Value," ',' ") & " ') " ...

you need to remove the extra spaces you put in, in all the places I marked in red where you have a single and double quote next to each other. Do you see why? Let's say your parameter value is XYZ. You want the chart of accts substring to match 'XYZ', you don't want it to match SPACE(1) + 'XYZ' + SPACE(1). Or, you could leave the spaces in but then you would have to change your substring expression to delimit with a space on either side.

3. I don't think that, when you're using a dynamically constructed query like this, you should use explicit params the way you're doing it embedded in the strings. It may work in some instances, it doesn't work out too well for me. So, instead of

"GL_TRX.GLTRXDATE BETWEEN @.StartDate AND @.EndDate"

... I would write:

"GL_TRX.GLTRXDATE BETWEEN '" & CSTR(@.StartDate) & "' AND '" & CSTR(@.EndDate) & "' "

... or something like that. Notice I've put the single quotes in red again so you can see them next to the double quotes.

4. I have two suggestions for you that might help you debug and also make the code less messy to maintain:

The first, I've already suggested (put the sql creation into an embedded proc so you can break it up and read what you are doing, append into a StringBuilder or whatever and then return the full statement.ToString() when you're done.)

The second is to create a simple report with the same parameters and have a text box that has the same expression building syntax you're using here. It doesn't matter what the rest of the report looks like, you just want one big textbox so that you can read it. Here's why this is a good idea:

* -- if you have basic VB-syntax problems, you'll know because the report won't be able to parse what you did when you try to preview it

* -- if you don't have a VB-syntax problem, you'll see what you did in the body of the report. You should be able to copy and paste it somewhere else and run it as sql. If it's correct, it will run. If not, you'll be able to see why <g>.

* -- once you get it right, you can move that same expression-building code into the "real" report.

HTH,

>L<

|||

I have just run into a similiar situation. Where I have to pass a parameter (multi value) to a stored procedure.

The sproc use a where filed in(@.Centers) command.

When I select 1 value it works fine, when I select all or more then one, then no data is returned.

It seems the value returned from the MV is one,two,three. etc...

where to accurately pass this it should be 'one',two','three'

a hint on how to get it done this way?

MultiValue Parameter Help

I have passed a list of parameters to a multivalue parameter in a SQL Report.

I can click the drop down and see the list of parameters that were passed.

In my select statement for the report, here is the condition I am using in the where clause.

AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN (@.RUs))

Nothing is returned in the result set if more than one value is passed to the multivalue parameter.

Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.

For example, in the multivalue dropdown list, there are the following values:

211

212

If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.

what is the value type for your MV paramater? Is it the same as the sql value type?

|||

>>Shouldn't the above conidition have the effect that no matter how many account numbers there are

You might think so, but no <g>. You have to build the SQL, there's no magic here that allows the SQL to automatically understand a collection of values -- which is what the multiselect parameter is.

I'll assume that your values for the dropdown list are string-type, because that is the hardest case usually and because you're using SUBSTRING() on your data column...

You need to construct your SQL something like this:

="SELECT " & blah blah blah & "WHERE " & blah blah &

" AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN ('" &

JOIN(Parameters!RUs.Value,"','") & "') "

... the use of red in the expression above is just to make sure you can see the single quotes versus the double quotes. I don't want to add spaces between the characters because those spaces would destroy the syntax we're trying to build.

What I'm trying to show you is that you need to end up with a SQL expression that has the delimited strings in an IN clause -- IN ('211','212') -- and that, while annoying when expressed as above, it is possible to do it <s>. This is because the JOIN function will take the various values for each of the parameter values in the collection and concatenate them with a delimiter that, in this case, is three characters long: ',' .

We add the outside or "embracing" single quote delimiters for the first and last items in the collection manually.

If there is only one item in the dropdown selected, the JOIN doesn't add anything but the one value, and the result is (say) IN ('211'), perfectly valid.

If you find it annoying to look at the SQL this way (I do) you can do what I've advised others to do several times in similar situations: just write a query that looks like this:

=Code.GetSQL()

... and write a little embedded Function GetSQL() AS String in your report, that returns the query as a string. You'll write a more maintainable version of the parsing code if you don't have to do it all in-line as above. If you need to do any type conversions or iterate through the parameter collection for any other reason, it will be easier.

HTH,

>L<

|||

Lisa:

Thank you so much for such an involved reply. I like that answer and feel that you are most likely correct. However, I'm having trouble getting that to work for me.

Is the JOIN function that you are using in your code the one that can be used by right clicking a field on the layout tab of the report and selecting expression?

I'm not sure how I can use that JOIN in the SQL select statement on the Data tab. It doesn't seem to me that the code you have shown can be used in a standard select statement. How would I do that? As far as I know, JOIN in "natural SQL" can be INNER JOIN, OUTER JOIN, etc. However, I've never seen just JOIN used as you have here.

Also you've used the Parameters!RUs.Value syntax here. Shouldn't that be @.RUs as it is the data portion of the report and not the layout of the report?

Mainiac007:

The RUs parameter in my stored procedure is varchar(1000).

The multivalue report parameter is of type String.

The ChartOfAccountsAccountNo is of type varchar(24).

|||

the JOIN she is referring to is not a sql command but a vb function.

|||

HI there,

I'm sorry the reply was involved -- unfortunately it has to be <s>. I've seen a bunch of people saying you can't do this, which is why I bothered to write out the whole thing <s>. And it seems I guessed right about your data types, so you did need the full information.

To answer your question(s), and I promise it really does work <s>.... Look really carefully at the expression I wrote:

I didn't write a SQL statement. I wrote an EXPRESSION which, when evaluated, is a SQL statement.

Notice the = at the beginning. Notice the double quotes -- that's VB expression quotes, not SQL delimiters -- around the parts of the expression.

Notice, also, that I said in my reply that you could do the same thing by using the expression =Code.GetSql(), where GetSql is an embedded function in the Report Code. Obviously the SQL engine wouldn't understand Code.GetSql() either, but that makes no difference <s>.

Either way, in-line expression or function, the *returned result* of the expression is a valid SQL statement, which has been dynamically built up from strings to meet your requirements. The string which has been built up is what is going to get sent to the SQL engine, not the expression that created it.

As for why I used Parameters!RUs.Value rather than @.RUs, I think that will be obvious when you grok what I just said <s>. I'm not talking SQL when I'm creating the string. I'm talking report-scoped object syntax, and I'm talking to the collection of parameter objects that represents your multi-select parameter. The JOIN function is VB, and I'm using it in a way that is entirely appropriate to the problem at hand. (Considering the number of times I have seen people say "you can't do that with string values", I flatter myself that it's a pretty cool idea, too <g>.)

Does this make more sense now?

>L<

|||I could not have said it better myself.|||

>> I could not have said it better myself

gee thanks <rofl> No offense, Maniac007, but ...

you were one of the people who appeared to need help on this exact issue and hence one of the reasons I posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012961&SiteID=1) .

>L<

|||yup, that was LAST year.... since then 6 months of working with ssrs has taught me a lot.|||

Sorry, Mainiac007, I didn't mean you didn't *know* this. I meant it was obviously hard to write out and explain -- otherwise, obviously, you or many other people who post here would have done it <s>.

I've avoided explaining it many times as well, this time I just decided to bite the bullet. Let's hope the explanation is sufficiently clear and that people link to this thread as necessary.

>L<

|||

My wife would kill me for saying this....but,,,,

"Just like a women, to have the last word."

|||

Lisa,

Thank you so much for your help. I think I'm at least following you now, but I still have an error in my SELECT statement and I can't seem to figure out what it is.

The error I get when running the report is: Incorrect syntax near 'GL_TRX'.

|||

what are you summing this as...

SUM(GL_TRX.GLTRXAMOUNT) as what?

|||

There are a couple of things that I see...

1. As Mainiac007 has already told you, you probably need an alias on your calculated (SUM(GL_TRX.GLTRXAMOUNT)) field.

2. Remember what I told you about those extra spaces you can't put in? Well, you put them in <g>.

" AND (SUBSTRING(CHART_OF_ACCOUNTS.CHARTOFACCTSACCOUNTNO,9,3) IN (' " &

JOIN(Parameters!RUs.Value," ',' ") & " ') " ...

you need to remove the extra spaces you put in, in all the places I marked in red where you have a single and double quote next to each other. Do you see why? Let's say your parameter value is XYZ. You want the chart of accts substring to match 'XYZ', you don't want it to match SPACE(1) + 'XYZ' + SPACE(1). Or, you could leave the spaces in but then you would have to change your substring expression to delimit with a space on either side.

3. I don't think that, when you're using a dynamically constructed query like this, you should use explicit params the way you're doing it embedded in the strings. It may work in some instances, it doesn't work out too well for me. So, instead of

"GL_TRX.GLTRXDATE BETWEEN @.StartDate AND @.EndDate"

... I would write:

"GL_TRX.GLTRXDATE BETWEEN '" & CSTR(@.StartDate) & "' AND '" & CSTR(@.EndDate) & "' "

... or something like that. Notice I've put the single quotes in red again so you can see them next to the double quotes.

4. I have two suggestions for you that might help you debug and also make the code less messy to maintain:

The first, I've already suggested (put the sql creation into an embedded proc so you can break it up and read what you are doing, append into a StringBuilder or whatever and then return the full statement.ToString() when you're done.)

The second is to create a simple report with the same parameters and have a text box that has the same expression building syntax you're using here. It doesn't matter what the rest of the report looks like, you just want one big textbox so that you can read it. Here's why this is a good idea:

* -- if you have basic VB-syntax problems, you'll know because the report won't be able to parse what you did when you try to preview it

* -- if you don't have a VB-syntax problem, you'll see what you did in the body of the report. You should be able to copy and paste it somewhere else and run it as sql. If it's correct, it will run. If not, you'll be able to see why <g>.

* -- once you get it right, you can move that same expression-building code into the "real" report.

HTH,

>L<

|||

I have just run into a similiar situation. Where I have to pass a parameter (multi value) to a stored procedure.

The sproc use a where filed in(@.Centers) command.

When I select 1 value it works fine, when I select all or more then one, then no data is returned.

It seems the value returned from the MV is one,two,three. etc...

where to accurately pass this it should be 'one',two','three'

a hint on how to get it done this way?

Multi-value parameter dropdown horizontal scrollbar problem

Hi,

I wonder if anyone ecountered (and successfully solved) the following problem:

I have a query-based multi-valued parameter (let's call it "A"). When query that populates the parameter returns only one value and that value is a long text, then it's almost impossible to select this value through the Report Manager UI. It's because the horizontal scrollbar covers the value.

For now I have found two workarounds to this problem:

1. Cut the lenght of the Parameter Label value (Instead of 'Large Power Transformer", I show "Large Power Tran...")

2. Make the label in the multi-value dropdown smaller by using custom style sheets for report manager. I added a "LABEL { font-size: 7pt; }" section to the Htmlviewer.css and modified the RSReportServer.config file to point to the Htmlviewer.css (for info on how to do this please see: http://msdn2.microsoft.com/en-us/library/ms345247.aspx)

So far I am using the No 2. workaround. Any other suggestions on final solution to that matter would be highly appreciated.

Hi,

This is a bug, which was fixed in SQL Server 2005 in a post-SP1 QFE, and in Visual Studio controls in VS 2005 SP1. The fix will also ship in the upcoming SQL Server 2005 SP2.

Multi-value parameter default values not working

I have a parameter that gets it's available values from a dataset.

I use this exact same data to populate the default values.

When I run the report, the available values get populated; however the default values are not being selected.

This works on other parameters on the same report. However, on this parameter, it is not working.

I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...

Here is my query:

Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@.ClientID)

Order by CityName

The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.

I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.

Any ideas?

Thanks!!

Thanks

Ok, this is weird...

If I change the query to:

Code Snippet

Select distinct
Replace(CityName,'','') as CityName

It works fine.

If I do a lower or upper, it works fine.

Convert or l/rtrim do not work.

Anyone have any ideas?

At this point I am thinking that there is a character in one of the fields that shouldnt be in there... I am looking at that now.

BobP

|||

Ok, it's an intermittent problem.

Sometimes the replace works, sometimes it doesn't.

Still trying to figure out why...

Does anyone know if there are any characters that could be in the data field, and prevent the default parameters from populating?

Thanks

|||

This same query populates a multi value drop down on a totally different report and it does not work there, either.

I am thinking it is data related.

Can anyone think of a reason why the multi select drop down would be populated, but the defaults not be set?

There are only 360 rows, so it's not a row number issue.

Any ideas would be very welcomed.

Thanks

BobP