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

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?
>

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

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

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

sql

Multi-value parameter cannot be passed to SQL where clause (Oracle db)

Greetings!!

I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.

PROBLEM Definition

We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@.', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully?

Sample Query used in sample SSRS data-set when SQL server was at back end:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN (@.p))

The report worked fine in this case when the user selected certain values in multi-select list.

Thank you,

Karthik

Oracle can not support the query parameter with a name as your case "@.p". You may change your query into:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN ?)

Then set the parameter ? to one of your report parameter.

It should be work.

|||

Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)

-- Robert

|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?

Multi-value parameter cannot be passed to SQL where clause (Oracle db)

Greetings!!

I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.

PROBLEM Definition

We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@.', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully?

Sample Query used in sample SSRS data-set when SQL server was at back end:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN (@.p))

The report worked fine in this case when the user selected certain values in multi-select list.

Thank you,

Karthik

Oracle can not support the query parameter with a name as your case "@.p". You may change your query into:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN ?)

Then set the parameter ? to one of your report parameter.

It should be work.

|||

Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)

-- Robert

|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?

Multi-value Parameter Calculation

OK Guys....

If you answer this one, you will save my life...and you WILL be the MAN or WOMAN!!!!

Problem: I have a set of 24 matrix's that need to calculate the difference between the last two years and display in a field to the right of the last rendered column. Since I have been struggling with this, let's just assume there is no better way than how I currently have it set up. (one table that does the calculations for me and I set one field on the report to display the most recent two columns difference in my report)

what I can not figure out is: when I choose one of my parameters the report displays the information I want...but when I choose more than one...well there is the problem....

In order to obtain the most help for myself I will ask this in the most general way possible so as not to get bogged down into my specific solution...

Desired Result: How to pass all my parameter values from my multi-value parameter during runtime to a SQL Stored proc from my dataset within Reporting Services at runtime, Match the parameter to the field, get the result and store it in a variable, then do it again and add the second to the first within the variable, and so on and so on , until all of the parameters are used. Then sum the values and display in a field.

HELP, HELP, HELP Please....

c-mon anyone know how to do this?|||

Well, I think you might have gone a bit too general for me to really follow what you're asking.

Have you narrowed down at all where the problem lies?

As far as Multi-valued parameters and Stored Procs,

You may have noticed that if you use a text query instead of a stored procedure, you can use the syntax:

Code Snippet

select * from foo where bar in (@.MultiValuedParameter)

And you are looking for a way to do the same, but use a stored procedure. Is that about right, or are you wanting me to comment on your 24 matrices?

If you are using sql2005, you can get very close if you use a table-valued function instead of a stored procedure, as long as your stored procedure does not modify the database in any way.

The way you would do that by creating a text dataset that looked something like this:

Code Snippet

SELECT *

FROM foo CROSS APPLY fnNewTVF(foo.bar, ...)

WHERE foo.bar IN (@.multiValuedParameter)

Cross Apply passes each value for foo.bar to your function in turn, as you described.

If you must use a stored proc, you can either use JOIN to make a comma-delimeted string value to your stored procedure, or you can use some custom code that will generate the query you need on the fly.

Does that answer your question?

|||Thank You....

Multi-Value Parameter and FireFox

I am experiencing a problem with the multi-value parameter dropdown in the ReportViewer when using FireFox, but in IE it seems to work fine. The symptom is when I click on the dropdown the list of options appears and then quickly disappears before I can select anything and I only experience the problem with multi-value parameters. I also have noticed that the dropdown is grayed out but I can still click it. Also, the position of the dropdown options are different in FireFox (pushed all the way to the left). I have created a very basic example at https://www.register4this.com/testreport.aspx to demonstrate the problem. There is no report, only the single "Orgs" parameter to keep things as simple as possible.

I suspect that I am missing something obvious because I haven't been able to find anyone else reporting this issue so any help would be appreciated. My ReportView control is configured as follows:

Code Snippet

<rsweb:ReportViewer ID="ReportViewer1" runat="server" AsyncRendering="False" Font-Names="Verdana" Font-Size="8pt" Height="400px" ProcessingMode="Remote" Width="400px">

<ServerReport ReportPath="/REG_Report/testreport" ReportServerUrl="http://reports.register4this.com/reportserver" />

</< FONT>rsweb:ReportViewer>

The report code is as follows:

Code Snippet

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="Members">

<DataSourceReference>Members</DataSourceReference>

<rd:DataSourceID>54f7f4de-9fe5-44e9-bcf0-467d024725cb</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<ReportParameters>

<ReportParameter Name="Organizations2">

<DataType>String</DataType>

<Prompt>Orgs:</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>Organizations</DataSetName>

<ValueField>orgID</ValueField>

<LabelField>orgName</LabelField>

</DataSetReference>

</ValidValues>

<MultiValue>true</MultiValue>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Height>2in</Height>

</Body>

<rd:ReportID>504c91c0-4beb-421e-bae0-4d581303b3e2</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="Organizations">

<Query>

<CommandType>StoredProcedure</CommandType>

<CommandText>REG_spGetOrganizations_org</CommandText>

<QueryParameters>

<QueryParameter Name="@.orgID">

<Value>=System.DBNull.Value</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>Members</DataSourceName>

</Query>

<Fields>

<Field Name="orgID">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>orgID</DataField>

</Field>

<Field Name="orgName">

<rd:TypeName>System.String</rd:TypeName>

<DataField>orgName</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>6.5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

Any help wpuld be greatly appreciated.

Anyone have any insight on this? I'm having trouble finding others with the same problem so it must be something I'm doing.|||

We are currently experiencing this same exact problem in many of our reports.

Any help on this would be great

|||I also experiance the same problem in Safari for Windows. As soon as I remove the multivalue attribute it works as expected.|||Another clue! If I navigate to the report server (~/reportserver) and view the report there it works fine. So it definatly seems to have something to do with the ReportViewer control.

|||

I found the solution! You have to delete the line in the .aspx file that sets the doctype to XHTML.

|||Ok the problem has been fixed with the multi-value combo but now i seem to have a problem with navigation from one report to another. First click does not take you to the next report but the second click does and also when trying to return to the starting report i get "Execution '0qnexc55tnshwsy0afjnaw55' cannot be found" with the ID changing of course. Do you have the same problem? I have not been able to find anyone report this problem and i havnt found a workaround...

Thanks for the combo problem fix!
|||

I have not experienced the issue you are describing. Did it start after you made the change I suggested or was it happening all along? I would recommend trying to run the report directly from the report server (http://domainname/reportserver) and see if it works there. If it does work then view the source and compare it to the case that does not work. That's how I figured out what was causing this last issue. Hope that helps!

Multi-Value Parameter and FireFox

I am experiencing a problem with the multi-value parameter dropdown in the ReportViewer when using FireFox, but in IE it seems to work fine. The symptom is when I click on the dropdown the list of options appears and then quickly disappears before I can select anything and I only experience the problem with multi-value parameters. I also have noticed that the dropdown is grayed out but I can still click it. Also, the position of the dropdown options are different in FireFox (pushed all the way to the left). I have created a very basic example at https://www.register4this.com/testreport.aspx to demonstrate the problem. There is no report, only the single "Orgs" parameter to keep things as simple as possible.

I suspect that I am missing something obvious because I haven't been able to find anyone else reporting this issue so any help would be appreciated. My ReportView control is configured as follows:

Code Snippet

<rsweb:ReportViewer ID="ReportViewer1" runat="server" AsyncRendering="False" Font-Names="Verdana" Font-Size="8pt" Height="400px" ProcessingMode="Remote" Width="400px">

<ServerReport ReportPath="/REG_Report/testreport" ReportServerUrl="http://reports.register4this.com/reportserver" />

</< FONT>rsweb:ReportViewer>

The report code is as follows:

Code Snippet

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="Members">

<DataSourceReference>Members</DataSourceReference>

<rd:DataSourceID>54f7f4de-9fe5-44e9-bcf0-467d024725cb</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<ReportParameters>

<ReportParameter Name="Organizations2">

<DataType>String</DataType>

<Prompt>Orgs:</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>Organizations</DataSetName>

<ValueField>orgID</ValueField>

<LabelField>orgName</LabelField>

</DataSetReference>

</ValidValues>

<MultiValue>true</MultiValue>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Height>2in</Height>

</Body>

<rd:ReportID>504c91c0-4beb-421e-bae0-4d581303b3e2</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="Organizations">

<Query>

<CommandType>StoredProcedure</CommandType>

<CommandText>REG_spGetOrganizations_org</CommandText>

<QueryParameters>

<QueryParameter Name="@.orgID">

<Value>=System.DBNull.Value</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>Members</DataSourceName>

</Query>

<Fields>

<Field Name="orgID">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>orgID</DataField>

</Field>

<Field Name="orgName">

<rd:TypeName>System.String</rd:TypeName>

<DataField>orgName</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>6.5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

Any help wpuld be greatly appreciated.

Anyone have any insight on this? I'm having trouble finding others with the same problem so it must be something I'm doing.|||

We are currently experiencing this same exact problem in many of our reports.

Any help on this would be great

|||I also experiance the same problem in Safari for Windows. As soon as I remove the multivalue attribute it works as expected.|||Another clue! If I navigate to the report server (~/reportserver) and view the report there it works fine. So it definatly seems to have something to do with the ReportViewer control.

|||

I found the solution! You have to delete the line in the .aspx file that sets the doctype to XHTML.

|||Ok the problem has been fixed with the multi-value combo but now i seem to have a problem with navigation from one report to another. First click does not take you to the next report but the second click does and also when trying to return to the starting report i get "Execution '0qnexc55tnshwsy0afjnaw55' cannot be found" with the ID changing of course. Do you have the same problem? I have not been able to find anyone report this problem and i havnt found a workaround...

Thanks for the combo problem fix!
|||

I have not experienced the issue you are describing. Did it start after you made the change I suggested or was it happening all along? I would recommend trying to run the report directly from the report server (http://domainname/reportserver) and see if it works there. If it does work then view the source and compare it to the case that does not work. That's how I figured out what was causing this last issue. Hope that helps!

sql

Multi-Value Parameter

I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:

Code Snippet

SE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

what am i doing wrong?

You can concatenate the multi values in a comma delimited string and pass the string to the stored procedure. you can create a function in SS to split the string and return a table.

Expression used to populate the parameter

=Join(Parameters! <ParameterName> .Value,", ")

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

Multi-Value Parameter

Hi
I have created a very simple report in SSRS, which counts the number of
Supplier transactions.
I have added a Multi-Value parameter with two values, (I or C) to filter
either Invoices or Credits or both.
If I query Invoice only, I get the correct result.
If I query Credit only, I get the correct result.
If I select all, the result is the same as Invoice. i.e. it is not adding
Invoice and Credit together.
Can anyone please help?
Thanks
ShodmanSo how you have given in your query to "select all" ?
Amarnath
"Shodman" wrote:
> Hi
> I have created a very simple report in SSRS, which counts the number of
> Supplier transactions.
> I have added a Multi-Value parameter with two values, (I or C) to filter
> either Invoices or Credits or both.
> If I query Invoice only, I get the correct result.
> If I query Credit only, I get the correct result.
> If I select all, the result is the same as Invoice. i.e. it is not adding
> Invoice and Credit together.
> Can anyone please help?
> Thanks
> Shodman|||Please note that I am not using any syntax or trying to write code - I am not
that clever!!
From the layout tab, I am adding a parameter through Report > Report
Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
When I Preview the report, the parameter shows "select all" as on option.
Thanks for your time
Shodman
"Amarnath" wrote:
> So how you have given in your query to "select all" ?
> Amarnath
> "Shodman" wrote:
> > Hi
> >
> > I have created a very simple report in SSRS, which counts the number of
> > Supplier transactions.
> >
> > I have added a Multi-Value parameter with two values, (I or C) to filter
> > either Invoices or Credits or both.
> >
> > If I query Invoice only, I get the correct result.
> > If I query Credit only, I get the correct result.
> >
> > If I select all, the result is the same as Invoice. i.e. it is not adding
> > Invoice and Credit together.
> >
> > Can anyone please help?
> >
> > Thanks
> > Shodman|||Hey I am asking whether you have given any if condition in your data tab,
otherwise how you intend to display and select the correct query to display
your results. In otherwords you need to tell RS that when you select "Select
All" what should be done to display the correct results.
Amarnath
"Shodman" wrote:
> Please note that I am not using any syntax or trying to write code - I am not
> that clever!!
> From the layout tab, I am adding a parameter through Report > Report
> Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> When I Preview the report, the parameter shows "select all" as on option.
> Thanks for your time
> Shodman
>
> "Amarnath" wrote:
> > So how you have given in your query to "select all" ?
> >
> > Amarnath
> >
> > "Shodman" wrote:
> >
> > > Hi
> > >
> > > I have created a very simple report in SSRS, which counts the number of
> > > Supplier transactions.
> > >
> > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > either Invoices or Credits or both.
> > >
> > > If I query Invoice only, I get the correct result.
> > > If I query Credit only, I get the correct result.
> > >
> > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > Invoice and Credit together.
> > >
> > > Can anyone please help?
> > >
> > > Thanks
> > > Shodman|||I believe that Amarnath is saying that you have to provide some code in your
query to tell the database what to do when the "Select All" option is
selected.
So for example:
Select c.customer_name, p.product_type
From customer c, product p
Where c.customer_id = p.customer_id
and (p.product_type IN(@.param_product_type))
So in your report parameters you check the Multi-Value check box for the
"param_product_type" parameter. So in the above code, if you select 1 type,
the SQL query will return the correct type and if you select ALL, then the
query will return ALL product types.
Hope this helps.
Rob Cuscaden
"Amarnath" wrote:
> Hey I am asking whether you have given any if condition in your data tab,
> otherwise how you intend to display and select the correct query to display
> your results. In otherwords you need to tell RS that when you select "Select
> All" what should be done to display the correct results.
> Amarnath
> "Shodman" wrote:
> > Please note that I am not using any syntax or trying to write code - I am not
> > that clever!!
> >
> > From the layout tab, I am adding a parameter through Report > Report
> > Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> >
> > When I Preview the report, the parameter shows "select all" as on option.
> >
> > Thanks for your time
> > Shodman
> >
> >
> > "Amarnath" wrote:
> >
> > > So how you have given in your query to "select all" ?
> > >
> > > Amarnath
> > >
> > > "Shodman" wrote:
> > >
> > > > Hi
> > > >
> > > > I have created a very simple report in SSRS, which counts the number of
> > > > Supplier transactions.
> > > >
> > > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > > either Invoices or Credits or both.
> > > >
> > > > If I query Invoice only, I get the correct result.
> > > > If I query Credit only, I get the correct result.
> > > >
> > > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > > Invoice and Credit together.
> > > >
> > > > Can anyone please help?
> > > >
> > > > Thanks
> > > > Shodman|||You are right, I am asking exactly the same.
Amarnath
"Rob" wrote:
> I believe that Amarnath is saying that you have to provide some code in your
> query to tell the database what to do when the "Select All" option is
> selected.
> So for example:
> Select c.customer_name, p.product_type
> From customer c, product p
> Where c.customer_id = p.customer_id
> and (p.product_type IN(@.param_product_type))
> So in your report parameters you check the Multi-Value check box for the
> "param_product_type" parameter. So in the above code, if you select 1 type,
> the SQL query will return the correct type and if you select ALL, then the
> query will return ALL product types.
> Hope this helps.
> Rob Cuscaden
>
> "Amarnath" wrote:
> > Hey I am asking whether you have given any if condition in your data tab,
> > otherwise how you intend to display and select the correct query to display
> > your results. In otherwords you need to tell RS that when you select "Select
> > All" what should be done to display the correct results.
> >
> > Amarnath
> >
> > "Shodman" wrote:
> >
> > > Please note that I am not using any syntax or trying to write code - I am not
> > > that clever!!
> > >
> > > From the layout tab, I am adding a parameter through Report > Report
> > > Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> > >
> > > When I Preview the report, the parameter shows "select all" as on option.
> > >
> > > Thanks for your time
> > > Shodman
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > So how you have given in your query to "select all" ?
> > > >
> > > > Amarnath
> > > >
> > > > "Shodman" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > I have created a very simple report in SSRS, which counts the number of
> > > > > Supplier transactions.
> > > > >
> > > > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > > > either Invoices or Credits or both.
> > > > >
> > > > > If I query Invoice only, I get the correct result.
> > > > > If I query Credit only, I get the correct result.
> > > > >
> > > > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > > > Invoice and Credit together.
> > > > >
> > > > > Can anyone please help?
> > > > >
> > > > > Thanks
> > > > > Shodman

multi-value paramater from query: dynamically filtered

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

multi-value dropdownlist as a .net control?

Does anyone know if I can utilize the same multi-value dropdownlist control that is available in Report Manager and the ReportViewer parameters section, in my own ASPX pages? This is the control that has checkboxes in a dropdown list.

If not, can anyone point me to a third party?

Thanks,

Brian

Brian,
know exactly what you are talking about as I was searching for an item like this myself. I have not found any similar control yet but managed to seperate the client side code that is driving the control in the ReportViewer. Did not have time to write a custom control that renders out this client side code but this is definitely doable. Let me know if you want to have the commented client side code that drives the dropdown (it does not work in Firefox though but should be good enough as an example).

Cheers,
Niels