DropDownList instead of Textbox in Reporting Services

Hello,
I have a report that uses a stored procedure. The parameter from the stored procedure is a name field defined nvarchar(50). But I have a list of values for this field in another table; is there anyway to use the values from the other table to populate a dropdownlist of these values, instead of prompting the user for a textbox?
Thanks.Hi ,
for populating drop down list instead of default text box, you have to create another dataset which will fetch fields from another table.
while in layout, go to Report --> Report Parameters..
click on the parameter on left and on right side, select radio btn From query and specify dataset and select fields.
you can also select default value also. i think this is what u want right?
Thanks
Rohit
|||

Hey,

Yes, that is exactly what I was looking to do. Thanks a lot.

|||That's good for designing reports on the server (RDL). I'm using Visual Web Developer Express and cliking on Report-Report Parameters it seems there's no option for configuring parameters to come from a dataset. Is there a way around this?|||No takers so far. So let me re-phrase the question. Is there any way by which I can move parametrized reports from the server (RDL) to the client (RDLC)?|||Sure you can. You need to use the parameter objects to pass parameters to the report.

Dropdown parameter in Report Builder

Hi, Is it possible to create a dropdown parameter in Report Builder?
The result I want to get is (for example):

The parameter is "Project". Next to the parameter should be a dropdown box from where I can select a project (for example: project1, project2, ...). Once the parameter is selected, and I click "view report", the report should only display data from the selected project.

The only other options available in the Filter dialog screen are:
"Field" is in list
"Field" contains
"Field" equals

But none of these methods of creating a parameter generate a dropdownbox from where items can be selected, equal to those in the database.

So ... is it even possible to create a dropdown parameter with values out of the db? Or are there any workarounds?

You could create a second dataset for the paramaters. Then in that data set something like this to call the data from the db:

Select project_name
from project_name_table
Group by project_name

Then set your paramater equal to the new dataset.

|||

rs12345 wrote:

You could create a second dataset for the paramaters. Then in that data set something like this to call the data from the db:

Select project_name
from project_name_table
Group by project_name

Then set your paramater equal to the new dataset.

You are talking about reports generated through a Report Server Project. But I created a model (from a Report Model Project) and then I want to generate a report on that model through the Report Builder! There it is not possible to create datasets.

Aren't there any MS people on here who can confirm if it is possible or not? (Possible to work with a parameter displayed as a dropdownbox containing values from the database)

|||

To create a filter condition based on a report parameter, open the Filter dialog, add a filter condition based on the appropriate field, then click on the field name (not the operator) and choose "Prompt" from the menu that appears.

Note that you will only get a dropdown when running the report if you see a dropdown in the Filter dialog as well. The presence of the dropdown in both cases is determined by the Entity.InstanceSelection or Attribute.ValueSelection property in the report model (whichever applies).

Hope that helps!

|||Thanks!

Changing those properties did the trick!

Dropdown list width

Hi everybody,

I created a multivalue parameter in SSRS 2005 SP2, but the problem is that the width of the dropdown list is too small to fit my data (I'd like to avoid scrolling horizontally).

Is there any way to configure this?

Thanks in advance for your help.
Zoz

By converting the display parameter to a char value using convert(char(50),myvalue) as displayparam you should be able to make the field fixed width.

If you want to shrink the size of the box, you would need to limit the display output by using the substring function.

cheers,

Andrew

|||

Thanks for your answer Andrew.

Unfortunately, it doesn't work... I still need to scroll horizontally even when converting data to char...

May be there is some way to specify the width of the drop down list, but I have no idea where I could do that...

|||

there is a way to do this

-open up the dataset (assuming there is one) that returns values to your report params by clicking the ...

-now select the fields tab

-next edit the value column(by using an expression) for the field that is being displayed for your report parameter. you should be able to set a specified length of characters that you would like. this in turn will limit the width of the report parameter

sql

Dropdown list for multi-select does not dynamically size to accomodate the data

I've noticed that the dropdown list for the multi-select parameter does not
dynamically size so that it is not possible to read all the data in the
dropdown lists. It does seem to work with a single-select however. Does
anyone know if this gets fixed with VS2008?No replies? Anyone? I'm having the same issue... If I can find something
I'll post it here.
"Susan" wrote:
> I've noticed that the dropdown list for the multi-select parameter does not
> dynamically size so that it is not possible to read all the data in the
> dropdown lists. It does seem to work with a single-select however. Does
> anyone know if this gets fixed with VS2008?
>
>

DROP ot TRUNCATE a table

How do I DROP or TRUNCATE a SQL Server's table in a Stored Procedure by
passing a parameter value for the tabble name; something like the following:
CREATE PROCEDURE [dbo].[MyTestTable]
@.MyTable nvarchar(50)=''
AS
If @.MyTable<>''
BEGIN
IF OBJECT_ID(@.MyTable) IS NOT NULL
TRUNCATE TABLE @.MyTable --DROP TABLE @.MyTable
END
GO
The code above returns error message on line:
TRUNCATE TABLE @.MyTableSaima wrote:
> How do I DROP or TRUNCATE a SQL Server's table in a Stored Procedure
> by passing a parameter value for the tabble name; something like the
> following:
> CREATE PROCEDURE [dbo].[MyTestTable]
> @.MyTable nvarchar(50)=''
> AS
> If @.MyTable<>''
> BEGIN
> IF OBJECT_ID(@.MyTable) IS NOT NULL
> TRUNCATE TABLE @.MyTable --DROP TABLE @.MyTable
> END
> GO
> The code above returns error message on line:
> TRUNCATE TABLE @.MyTable
Declare @.sql nvarchar(100)
Set @.sql = N'Truncate Table [' + @.MyTable + N']'
EXEC (@.sql)
David Gugick - SQL Server MVP
Quest Software

drop down parameter question.

We have a bunch of reports that take a parameter from outside the normal
reporting services parameter window.
I understand that for security reason we cannot pass that parameter on the
querystring unless a reportparameter is defined. I therefore had to display a
drop down box with all the possible values.
Question is can I hide that box and its label?
I have managed to hide it from reportingservices manager, but not in a
browser whn just passing a url.
e.g.
http://dsdev2/Reports/Pages/Report.aspx?ItemPath= Works
but http://dsdev2/reportserver?...&rs:Command=Render&SurveyId=1
does not.
The way I got this to work is by changing ReportingService.css and adding a
behaviour.
e.g:
table.msrs-contentFrame IFRAME {
behavior: url(../js/CustomParameters.htc);
}
Any Idea anyone.Remove the Prompt for the parameter.
--
| Thread-Topic: drop down parameter question.
| thread-index: AcUgvQSDe1jZy1lbQDy/S6JkRqOmEw==| X-WBNR-Posting-Host: 62.188.58.81
| From: "=?Utf-8?B?RnJlZA==?=" <Fred@.discussions.microsoft.com>
| Subject: drop down parameter question.
| Date: Fri, 4 Mar 2005 05:21:04 -0800
| Lines: 28
| Message-ID: <84E65958-73A2-4F98-88B3-17B1E4D920B4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA
03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:44592
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| We have a bunch of reports that take a parameter from outside the normal
| reporting services parameter window.
|
| I understand that for security reason we cannot pass that parameter on
the
| querystring unless a reportparameter is defined. I therefore had to
display a
| drop down box with all the possible values.
|
| Question is can I hide that box and its label?
|
| I have managed to hide it from reportingservices manager, but not in a
| browser whn just passing a url.
| e.g.
| http://dsdev2/Reports/Pages/Report.aspx?ItemPath=
| Works
| but http://dsdev2/reportserver?...&rs:Command=Render&SurveyId=1
| does not.
|
| The way I got this to work is by changing ReportingService.css and adding
a
| behaviour.
| e.g:
| table.msrs-contentFrame IFRAME {
| behavior: url(../js/CustomParameters.htc);
| }
|
| Any Idea anyone.
|
|
|
|

Drop Down Parameter List

I have a string with values that are separated by a space. It looks like this:DAY MICS SUN FIX STD.I would like to take the string and make it a drop down list in a report parameter.I have tried the split function but it errors on me.How can I accomplish this?

Is the value dynamic?
If not, could have manually put into parameter list

If it's dynamic, I think best way is to use a Query for the parameters
and do the split in T-SQL

split function
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp|||Thanks for responding. Yes the value is dynamic. When you say do the split in T-SQL (Transaction SQL?) how do you do that? I dropped the code from the article link you sent me into the code section under report properties. I get an error "There is an error on line 0 of custom code: [BC30188] Declaration expected." Maybe that is not where it goes? My query looks like this so far: SELECT ImplodedsubKeywordsList WHERE FORMULA("@.Contains(keywords;\"KI Product Demand Category\")" ) Do I just tack that code onto the end of it somehow? Thank you.

Drop down list for parameters

Say I wanted to have a parameter for clients...
Is there a way for me to have a dropdown list of all the clients' firstnames and lastnames to choose from but, have it bind the clients id ?
thanks
-Jimand this list be genereted dynamically from the database..instead of a static default list..thanks

-Jim

Drop Down box not showing option

I'm using a drop down list for a parameter in RS2005. The issue comes when there is only one option. When I render the report inside the microsoft studio it works fine, but when I render the report from the Report Server (web browser) if there is only one option in the dropdown menu then it seems that it is there but I can't see it. It is a multivalued parameter. Is there a way to resize the dropdown list or any way I can fix this? Any help would be greatly appreciated.

This issue was fixed in a QFE. It will be available in SP2. The QFE is not publicly available, but if you need it you can contact Microsoft Customer support and they should be able to give you access to the QFE.|||

Thanks for your response. I contacted Microsoft Support and they told me I needed an article Number. Do you know that number?, If so, can you please send it to me?

Thanks again,

Pilar

|||Ok, here is the KB # for them 919556.

Drillthrough parameters

I've set a report that drills through to another report.
There is a parameter on the second report (sales orders)
called 'order_no'.
I can set up the drill through so clicking on an order
number on the first report links to the sales order
report, with the appropriate order number selected. What I
would like to be able to do is populate the parameter
values list from a query, so once the user has drilled-
through, they can browse through other sales orders using
the paramater.
Assuming the order_no parameter '000118' is sent through
to the sales orders report, I can display the correct
sales order. However, if I populate the paramater from a
query, I lose that value. Ideally I would like the default
parameter value to be the one passed from the first
report, but of course, you cannot set a parameter to
itself. Should I pass a second parameter to use for
setting the default value? It doesn't seem like a logical
way of doing things.
I hope this makes sense, and that someone can shed some
light on the issue.
Thanks,
Chris Ashworth.Chris,
I hope you don't mind if I piggy-back onto your inquiry. I'm looking for an
answer as well. I have several drill down reports where I'd like the user to
be able to see the parameter dropdowns, but have noticed that when a report
is drilled to, the parameters no longer show. Is there any kind of toggle to
show parameters, even after a drill down?

Drilldown in parameter pick lists.

Is it possible to get drilldown functionallity in a parameter pick
list?
So that it is possible to expand/collapse items just like in e.g pivot
table?
Like:
+ All Time
- 2000
- Jan
- Feb
- Mar
etc
- 2001
- Jan
- Feb
- Mar
etcHi,
No, you need to expand all the values in a single query for drop down. May
be you can have two parameters
one for year and second for month. or otherwise list all the year and
months in one query.
Amarnath
"Jan" wrote:
> Is it possible to get drilldown functionallity in a parameter pick
> list?
> So that it is possible to expand/collapse items just like in e.g pivot
> table?
> Like:
> + All Time
> - 2000
> - Jan
> - Feb
> - Mar
> etc
> - 2001
> - Jan
> - Feb
> - Mar
> etc
>|||Ok, thanks.
But it would had been a great feature though :-)|||If you really need that functionality, you can always roll your own asp.net
page with your server controls the way you want, then set up the selection or
button click event to call the report and pass in the parameters to the
ReportServer via URL api, or web service.
--
Regards,
"Jan" wrote:
> Is it possible to get drilldown functionallity in a parameter pick
> list?
> So that it is possible to expand/collapse items just like in e.g pivot
> table?
> Like:
> + All Time
> - 2000
> - Jan
> - Feb
> - Mar
> etc
> - 2001
> - Jan
> - Feb
> - Mar
> etc
>

Drilldown Error

Hi,
I've one report with single level drilldown.
I've set this functionality through Detail/Summary parameter.
Now when i run the report by selecting Summary option,
reports run fine with showing collpased (+) sign against
toggle field.
Now when i try to drill down the report by clicking the (+) sign,
it gives following error
"One or more datasource credentials required to run the report have not been
specified. (rsCredentialsNotSpecified)"
I haven't set the credential during design time, report ask the user
name/password during run time.
Now what is the reason behind that error ? And what is the solution ?
--
Thanks & Regards
MalkeshI have the same problem, but only if the report is rendered from a snap shot.
"Malkesh" wrote:
> Hi,
> I've one report with single level drilldown.
> I've set this functionality through Detail/Summary parameter.
> Now when i run the report by selecting Summary option,
> reports run fine with showing collpased (+) sign against
> toggle field.
> Now when i try to drill down the report by clicking the (+) sign,
> it gives following error
> "One or more datasource credentials required to run the report have not been
> specified. (rsCredentialsNotSpecified)"
> I haven't set the credential during design time, report ask the user
> name/password during run time.
> Now what is the reason behind that error ? And what is the solution ?
> --
> Thanks & Regards
> Malkesh

Drilldown doesn't work on wildcard search!

I have a report parameter â'spare part numberâ' thatâ's configured like this:
Data type: string, non-queried (empty list), no default value. This field is
rendered as a text box in preview. I also have other parameters but these are
all based on queries and datasets and are therefore rendered as drop downs.
What I really want is the user to be able to ask for a report on many spare
part numbers in one go. I canâ't get this to work, so if you know how, please
let me know.
What works is to leave this field empty and have a filter like:
=Fields! PART_NR.Value Like =iif(trim( Parameters!SparePart.Value
)="","*",Parameters!SparePart.Value )
When I enter a valid spare part number (e.g.: 3215323) or do a wildcard
search (e.g.: 32*) The correct result is shown, but my drilldown no longer
works. Clicking the â'+â' sends the report back to me collapsed (This is in
preview mode).
Anyone know anything about this?
Regards
AK411MultiValued parameters are being added to the Yukon version of RS. There
are other posts in this newsgroup that may have a solution, but I do not
have one off of the top of my head.
--
| Thread-Topic: Drilldown doesn't work on wildcard search!!!
| thread-index: AcTMqhu37sydctEqQ0aX2mQTQ1wFQA==| X-WBNR-Posting-Host: 193.212.219.1
| From: =?Utf-8?B?Si4gS2FuZG8gNDEx?= <JKando411@.discussions.microsoft.com>
| Subject: Drilldown doesn't work on wildcard search!!!
| Date: Wed, 17 Nov 2004 05:34:05 -0800
| Lines: 23
| Message-ID: <03E96D3C-A721-435D-881D-4AB818C8B6EB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:35151
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a report parameter â'spare part numberâ' thatâ's configured
like this:
| Data type: string, non-queried (empty list), no default value. This field
is
| rendered as a text box in preview. I also have other parameters but these
are
| all based on queries and datasets and are therefore rendered as drop
downs.
|
| What I really want is the user to be able to ask for a report on many
spare
| part numbers in one go. I canâ't get this to work, so if you know how,
please
| let me know.
|
| What works is to leave this field empty and have a filter like:
| =Fields! PART_NR.Value Like =iif(trim( Parameters!SparePart.Value
| )="","*",Parameters!SparePart.Value )
|
| When I enter a valid spare part number (e.g.: 3215323) or do a wildcard
| search (e.g.: 32*) The correct result is shown, but my drilldown no
longer
| works. Clicking the â'+â' sends the report back to me collapsed (This
is in
| preview mode).
|
| Anyone know anything about this?
|
| Regards
| AK411
|
|