Dropping tempe tables

My query creates a new temp table every time a report is run. I need to check
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.

Dropping labels on a report - updated.

Is there anyway to drag the fields on to a report and have it create labels
for those fields without having to create each of them individually and
without using the table or matrix?
DavidYou mean in a list control? No, this is not supported. With the nature of
lists, it would be pretty tough to tell where you wanted the labels anyway.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"CapitalEMR" <CapHS@.hvif.com> wrote in message
news:%23zmdg5MVFHA.544@.TK2MSFTNGP15.phx.gbl...
> Is there anyway to drag the fields on to a report and have it create
> labels
> for those fields without having to create each of them individually and
> without using the table or matrix?
> David
>|||No - not on a list control. Just on the report page.
Drag and drop field and label - just like VB6 used to do. I would love to
worry about where I wanted the labels instead of creating them by hand and
then placing them.
"Brian Welcker [MS]" <bwelcker@.online.microsoft.com> wrote in message
news:umOy5HjVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> You mean in a list control? No, this is not supported. With the nature of
> lists, it would be pretty tough to tell where you wanted the labels
> anyway.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "CapitalEMR" <CapHS@.hvif.com> wrote in message
> news:%23zmdg5MVFHA.544@.TK2MSFTNGP15.phx.gbl...
>> Is there anyway to drag the fields on to a report and have it create
>> labels
>> for those fields without having to create each of them individually and
>> without using the table or matrix?
>> David
>>
>

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 won't show values if only one item is available

I have a report that has 2 dropdowns, selecting from the first dropdown populates the second one. This works fine in the BI Studio.

When I deploy this report to the 'Report Manager' and make a selection from the first dropdown, the second dropdown loads (as expected). I tried to select from the second dropdown (which has only 1 item - which is correct), the dropdown does not appear correctly - as in, I can't see that item.

Since we can't attach anything here, below is the link to a screenshot of my issue:
http://docs.google.com/View?docid=ddd6j2xn_52c5qd5

If you look closely at the screenshot from the link above, you'll see that there is a value in the second dropdown - it just won't show completely - as if the dropdown is not rendering correctly. I can view source on the page and see that the dropdown has a value.

What appears to be happening is the if only 1 item is in the second dropdown and that item is longer than the size of the dropdown, the dropdown won't render.

Here is my value for the second dropdown '012 Candy Gadler David Thapero'. This is the only value in the second dropdown. - You can try 35 chars or more in the dropdown to confirm.

Notes:
+ No special chars are in either dropdown
+ I am using IE 7 (Also had someone test this on IE 6 - same problem)
+ Using Visual Studio 2005 to build report - where this works fine

Work around:
+ If I add another item to the dropdown via a UNION query, I see the original value + the new item in the dropdown #2

Please share your thoughts
Thanks,
h.

Weird, because I cannot make that happen. I created two dropdowns with items that are each way longer than yours, the second one is dependent on the first and has only one item, and I don't get that scrollbar in the dropdown, the page just becomes wide enough for the dropdowns and I have to scroll the whole page across to see the second dropdown, but it displays fine. I tried it in Report Manager, then I tried it in the Report Viewer and there it also works although it looks a little different. Instead of the whole report getting wider, just the parameters section gets wider, so I have to scroll it, but again the dropdown is full width.

I'm using Reporting Services 2005 with SP1.

|||

SNMSDN ,

Are your parameters Multi-value? Try making them multi-value. Your assistance is appreciated.

|||

Haha - that's it, if it's multivalue I cannot see the single value either.

Someone from MS - we have a bug.

|||

It is a bug. Actually the value is there, but horizontal scrollbar took too much space, and completely covered it.

As far as I remember it was fixed in Sql Server 2005 SP1

|||Thanks SNMSDN for confirming this issue.|||Thank you Lev Semenets - MSFT for the fix.|||It isn't fixed in SP1, because I'm using SP1, hopefully in the SP2?|||Sorry, I was wrong. There is a QFE for this issue. And it will be fixed in SqlServer SP2

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 menu help

I have a report with 4 parameters
2 of them are dropdown menus, one contains groups that populate the other
dropdown box with computer names. The report works great but I would like to
have an ALL function in the computer name dropdown box.
From poking around here I was able to plug a UNION SELECT 'All' AS
ComputerName into the dataset that builds the computername list but I am no
sql wiz by any stretch of the imagination and it seems that is just plugging
in All to the dropdown without any meaning behind it.
Has anyone done this sort of thing before or have any examples they could
share?
Thanks!
Kevinhttp://blogs.msdn.com/chrishays/archive/2004/07/27/199157.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Kevin Serafin" <kevin.serafinISHEREATecolabDOTcom> wrote in message
news:enxx48jeEHA.2852@.tk2msftngp13.phx.gbl...
> I have a report with 4 parameters
> 2 of them are dropdown menus, one contains groups that populate the other
> dropdown box with computer names. The report works great but I would like
to
> have an ALL function in the computer name dropdown box.
> From poking around here I was able to plug a UNION SELECT 'All' AS
> ComputerName into the dataset that builds the computername list but I am
no
> sql wiz by any stretch of the imagination and it seems that is just
plugging
> in All to the dropdown without any meaning behind it.
> Has anyone done this sort of thing before or have any examples they could
> share?
> Thanks!
> Kevin
>|||Thanks Chris, works great!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:us4dvpleEHA.140@.TK2MSFTNGP12.phx.gbl...
> http://blogs.msdn.com/chrishays/archive/2004/07/27/199157.aspx
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Kevin Serafin" <kevin.serafinISHEREATecolabDOTcom> wrote in message
> news:enxx48jeEHA.2852@.tk2msftngp13.phx.gbl...
> > I have a report with 4 parameters
> >
> > 2 of them are dropdown menus, one contains groups that populate the
other
> > dropdown box with computer names. The report works great but I would
like
> to
> > have an ALL function in the computer name dropdown box.
> >
> > From poking around here I was able to plug a UNION SELECT 'All' AS
> > ComputerName into the dataset that builds the computername list but I am
> no
> > sql wiz by any stretch of the imagination and it seems that is just
> plugging
> > in All to the dropdown without any meaning behind it.
> >
> > Has anyone done this sort of thing before or have any examples they
could
> > share?
> >
> > Thanks!
> >
> > Kevin
> >
> >
>

dropdown box ?

Is there anyway i can have a dropdown box on the report ? -
not on the parameters list - but on the report.
ThanksNope. You can do drill down (and maybe immitate one).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RP" <RP@.discussions.microsoft.com> wrote in message
news:16B29C30-C9E3-4F7A-8189-1620DCEFA15B@.microsoft.com...
> Is there anyway i can have a dropdown box on the report ? -
> not on the parameters list - but on the report.
> Thanks|||But how do i take the selected value ?
EX: if i have to select a country
i create a table with drill down
+Country
-Country
China
USA
UK
Australia
If the user clicks USA - how do i get the value ' i need to use that
value in other calulations.
Thanks
"Bruce L-C [MVP]" wrote:
> Nope. You can do drill down (and maybe immitate one).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:16B29C30-C9E3-4F7A-8189-1620DCEFA15B@.microsoft.com...
> > Is there anyway i can have a dropdown box on the report ? -
> > not on the parameters list - but on the report.
> >
> > Thanks
>
>|||You cann't do what you want. Remember, RS is a reporting solution that
renders in a variety of formats, of which HTML is just one of them. There is
some interactivity (drill down, drill through and in 2005 dynamic sorting)
but it is not a web application environment. In the end it is a report.
Now, if you want to jump to something once they click on that you can do
that, but you cann't detect elsewhere in the report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RP" <RP@.discussions.microsoft.com> wrote in message
news:5A9923A8-AED0-4EDE-998C-E6BCFD84257A@.microsoft.com...
> But how do i take the selected value ?
> EX: if i have to select a country
> i create a table with drill down
> +Country
> -Country
> China
> USA
> UK
> Australia
> If the user clicks USA - how do i get the value ' i need to use that
> value in other calulations.
> Thanks
> "Bruce L-C [MVP]" wrote:
>> Nope. You can do drill down (and maybe immitate one).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "RP" <RP@.discussions.microsoft.com> wrote in message
>> news:16B29C30-C9E3-4F7A-8189-1620DCEFA15B@.microsoft.com...
>> > Is there anyway i can have a dropdown box on the report ? -
>> > not on the parameters list - but on the report.
>> >
>> > Thanks
>>|||There are serveral solutions I think. Let say your report is named "report1"
The first is to create a parameter in your report f.e. "SelectedValue" (you
hide it from the user).
You make sure the rport loads with a default value for this parameter (f.e.
blank).
And then you create an action on your country values, that action will call
the same report "Report1" but now with the parameter "SelectedValue"
containing the name of your country.
I use this technique f.e. to highlight a matrix row that a user has selected
and at the same time show a sub-report with details of the selected row.
Another possibility, that I haven't tried yet. Is to make an action that
opens a very small report, in a small window (fe with the window.open
function you can size the window ref.
http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/methods/open_0.asp ).
The small report will only contain a few textboxes (arranged as a drop down
menu), you can even use parameters to fill them. You can than add actions to
these textboxes.
The only problem with this solution is that a selected action in the
"drop-down-menu" will have to open in another window (I thin there probably
is a solution for that to, but I haven't found it yet).
Hope this helps
"Bruce L-C [MVP]" wrote:
> You cann't do what you want. Remember, RS is a reporting solution that
> renders in a variety of formats, of which HTML is just one of them. There is
> some interactivity (drill down, drill through and in 2005 dynamic sorting)
> but it is not a web application environment. In the end it is a report.
> Now, if you want to jump to something once they click on that you can do
> that, but you cann't detect elsewhere in the report.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:5A9923A8-AED0-4EDE-998C-E6BCFD84257A@.microsoft.com...
> > But how do i take the selected value ?
> > EX: if i have to select a country
> > i create a table with drill down
> >
> > +Country
> >
> > -Country
> > China
> > USA
> > UK
> > Australia
> >
> > If the user clicks USA - how do i get the value ' i need to use that
> > value in other calulations.
> >
> > Thanks
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Nope. You can do drill down (and maybe immitate one).
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "RP" <RP@.discussions.microsoft.com> wrote in message
> >> news:16B29C30-C9E3-4F7A-8189-1620DCEFA15B@.microsoft.com...
> >> > Is there anyway i can have a dropdown box on the report ? -
> >> > not on the parameters list - but on the report.
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>

Dropdown - multi-select won't show values if only one item is available

I have a report that has 2 dropdowns, selecting from the first dropdown populates the second one. This works fine in the BI Studio.

When I deploy this report to the 'Report Manager' and make a selection from the first dropdown, the second dropdown loads (as expected). I tried to select from the second dropdown (which has only 1 item - which is correct), the dropdown does not appear correctly - as in, I can't see that item.

Since we can't attach anything here, below is the link to a screenshot of my issue:
http://docs.google.com/View?docid=ddd6j2xn_52c5qd5

If you look closely at the screenshot from the link above, you'll see that there is a value in the second dropdown - it just won't show completely - as if the dropdown is not rendering correctly. I can view source on the page and see that the dropdown has a value.

What appears to be happening is the if only 1 item is in the second dropdown and that item is longer than the size of the dropdown, the dropdown won't render.

Here is my value for the second dropdown '012 Candy Gadler David Thapero'. This is the only value in the second dropdown. - You can try 35 chars or more in the dropdown to confirm.

Notes:
+ No special chars are in either dropdown
+ I am using IE 7 (Also had someone test this on IE 6 - same problem)
+ Using Visual Studio 2005 to build report - where this works fine

Work around:
+ If I add another item to the dropdown via a UNION query, I see the original value + the new item in the dropdown #2

Please share your thoughts
Thanks,
h.

Weird, because I cannot make that happen. I created two dropdowns with items that are each way longer than yours, the second one is dependent on the first and has only one item, and I don't get that scrollbar in the dropdown, the page just becomes wide enough for the dropdowns and I have to scroll the whole page across to see the second dropdown, but it displays fine. I tried it in Report Manager, then I tried it in the Report Viewer and there it also works although it looks a little different. Instead of the whole report getting wider, just the parameters section gets wider, so I have to scroll it, but again the dropdown is full width.

I'm using Reporting Services 2005 with SP1.

|||

SNMSDN ,

Are your parameters Multi-value? Try making them multi-value. Your assistance is appreciated.

|||

Haha - that's it, if it's multivalue I cannot see the single value either.

Someone from MS - we have a bug.

|||

It is a bug. Actually the value is there, but horizontal scrollbar took too much space, and completely covered it.

As far as I remember it was fixed in Sql Server 2005 SP1

|||Thanks SNMSDN for confirming this issue.|||Thank you Lev Semenets - MSFT for the fix.|||It isn't fixed in SP1, because I'm using SP1, hopefully in the SP2?|||Sorry, I was wrong. There is a QFE for this issue. And it will be fixed in SqlServer SP2

Drop foreign keys for replication

We set up transaction replication on our production server, and have a
subsriber pulling to a report server. The problem was that I needed to drop
all the foreign keys in order for the snapshot to succeed. My question is, do
I need to add all the foreign keys back to those tables on the subscriber or
can I leave the foreign keys alone?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
If it is not immediate updating (replicating both ways) then I wouldn't
bother.
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
drop
> all the foreign keys in order for the snapshot to succeed. My question is,
do
> I need to add all the foreign keys back to those tables on the subscriber
or
> can I leave the foreign keys alone?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||That depends. You could add them through a post snapshot script. However you
will only need to add fks on your subscriber if you are doing DML there and
want to enforce them. Otherwise if its read only you really don't need them
at all.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
> drop
> all the foreign keys in order for the snapshot to succeed. My question is,
> do
> I need to add all the foreign keys back to those tables on the subscriber
> or
> can I leave the foreign keys alone?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>

Drop foreign keys for replication

We set up transaction replication on our production server, and have a
subsriber pulling to a report server. The problem was that I needed to drop
all the foreign keys in order for the snapshot to succeed. My question is, d
o
I need to add all the foreign keys back to those tables on the subscriber or
can I leave the foreign keys alone?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1If it is not immediate updating (replicating both ways) then I wouldn't
bother.
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
drop
> all the foreign keys in order for the snapshot to succeed. My question is,
do
> I need to add all the foreign keys back to those tables on the subscriber
or
> can I leave the foreign keys alone?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||That depends. You could add them through a post snapshot script. However you
will only need to add fks on your subscriber if you are doing DML there and
want to enforce them. Otherwise if its read only you really don't need them
at all.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
> drop
> all the foreign keys in order for the snapshot to succeed. My question is,
> do
> I need to add all the foreign keys back to those tables on the subscriber
> or
> can I leave the foreign keys alone?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>

Drop foreign keys for replication

We set up transaction replication on our production server, and have a
subsriber pulling to a report server. The problem was that I needed to drop
all the foreign keys in order for the snapshot to succeed. My question is, do
I need to add all the foreign keys back to those tables on the subscriber or
can I leave the foreign keys alone?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1If it is not immediate updating (replicating both ways) then I wouldn't
bother.
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
drop
> all the foreign keys in order for the snapshot to succeed. My question is,
do
> I need to add all the foreign keys back to those tables on the subscriber
or
> can I leave the foreign keys alone?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||That depends. You could add them through a post snapshot script. However you
will only need to add fks on your subscriber if you are doing DML there and
want to enforce them. Otherwise if its read only you really don't need them
at all.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
> drop
> all the foreign keys in order for the snapshot to succeed. My question is,
> do
> I need to add all the foreign keys back to those tables on the subscriber
> or
> can I leave the foreign keys alone?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

Drop Down lists

I have a report that uses a drop down for the user to make a choice. The
report works fine when choosing only one selection but I need the user to be
able to choose one or all choices. Can someone please point me in the right
direction?If it just 'one' choice and or 'All' choice then you can add 'All' option
to the returned resultset and in your query you can check using IIF clause
the parameter value and create query accordingly.
="SELECT * FROM tableName "
& IIF(Parameters!CNO.Value="ALL",""," WHERE (NO IN(" & Parameters!CNO.Value
& "))")
"Zach" <Zach@.discussions.microsoft.com> wrote in message
news:53EF814E-8A45-4326-89E4-7F4E06D0496E@.microsoft.com...
>I have a report that uses a drop down for the user to make a choice. The
> report works fine when choosing only one selection but I need the user to
> be
> able to choose one or all choices. Can someone please point me in the
> right
> direction?

Drop Down list with many elements

Hello,

I am using SSRS 2005 SP2 and I need to allow my users to select the products they want in the report.
I created a data set to get the list of the products and then used it in a multivalue parameter to generate a drop down list.
My problem is that the list is too long (more than 2000 elements) and thus, it takes 10 sec to collapse the drop down list after tickinging the products.

Does anyone have an miraculous solution?
Thanks in advance for your answers.

Zoz

Did you create the drop down list using visual studio 2005?

You may want to divide this HUGE dropdownlist into maybe 26 dropdown lists -- categorize them by first letter in the product.

Only populate the A dropdown list with products that start with A, etc.

Then, handle each dropdown list's selection changed event separately. (I would imagine that each event would have the same code though)

Or maybe 26 is too many. Just do one dropdown for the first half of the alphabet and then another for the second half.

You get the idea though: divide and conquer.

|||I like the idea of conquering :-)
Your idea is good but I alreday have many drop down lists because I use cascading parameters (8!). Then it would become too heavy visually...

Any other solution?
Thanks.

PS: Yes, I used Visual Studio 2005.
|||

You can have a muti-page report.

The idea is to create a parameter called "Search Product" which takes string as an argument.

Then output products based on the value of the parameter.

Then click on the product which will take you to your current report.

Thanks,

-Rohit

|||Hi,

Can you detail a little bit more? I am not sure to understand what you mean...
Do you mean that I should create a search field?

Thanks.
Zoz
|||

See what I am trying to say is:

1. Create a summary page where you have a parameter which will search for productName (say we call it @.searchProduct) and generate a list of products related to that name. In SQL you can do (where productName like '% + @.searchProduct + %')

2. Then if the user click on a product it will take you to another report where you have all other parameters.

But the good thing is you don't have to worry about the product because you already selected your product and passed the productID from report1 to report2. Report2 is basically the same report you are working on but without the parameter for productID. You can make it hidden.

Feel free to ask me more questions...

|||

I really like your idea and I'm very curious to see how to implement it. Would you mind sending me an example by email (an rdl file for example)? I' ve tried something like that in the past, but I've never succeeded to do it... :-(

For the problem I popsted previously, your solution doesn't meet the requirements because users need to choose several products...

I tried to implement cascading parameters although it is not the best one in terms of performance... So I'm still looking for a better idea.

Thanks

|||

It is very simple....

For exmple if you are using table to output the products then right click on the detail textbox and go to properties and then click on navigation. There you can select jump to a report, select the report you want to jump to and pass productId as a parameter. Now when you run the report you will be able to click on the products and it will take you to the next report that you specified in the properties.

-Rohit

|||

Thanks for your answer! Looks like it is a great idea! I'll try it!

And is there any way to have a "select all" ? ( it is also requiered by my users...)

Thanks

Zoz

Drop Down list with many elements

Hello,

I am using SSRS 2005 SP2 and I need to allow my users to select the products they want in the report.
I created a data set to get the list of the products and then used it in a multivalue parameter to generate a drop down list.
My problem is that the list is too long (more than 2000 elements) and thus, it takes 10 sec to collapse the drop down list after tickinging the products.

Does anyone have an miraculous solution?
Thanks in advance for your answers.

Zoz

Did you create the drop down list using visual studio 2005?

You may want to divide this HUGE dropdownlist into maybe 26 dropdown lists -- categorize them by first letter in the product.

Only populate the A dropdown list with products that start with A, etc.

Then, handle each dropdown list's selection changed event separately. (I would imagine that each event would have the same code though)

Or maybe 26 is too many. Just do one dropdown for the first half of the alphabet and then another for the second half.

You get the idea though: divide and conquer.

|||I like the idea of conquering :-)
Your idea is good but I alreday have many drop down lists because I use cascading parameters (8!). Then it would become too heavy visually...

Any other solution?
Thanks.

PS: Yes, I used Visual Studio 2005.
|||

You can have a muti-page report.

The idea is to create a parameter called "Search Product" which takes string as an argument.

Then output products based on the value of the parameter.

Then click on the product which will take you to your current report.

Thanks,

-Rohit

|||Hi,

Can you detail a little bit more? I am not sure to understand what you mean...
Do you mean that I should create a search field?

Thanks.
Zoz
|||

See what I am trying to say is:

1. Create a summary page where you have a parameter which will search for productName (say we call it @.searchProduct) and generate a list of products related to that name. In SQL you can do (where productName like '% + @.searchProduct + %')

2. Then if the user click on a product it will take you to another report where you have all other parameters.

But the good thing is you don't have to worry about the product because you already selected your product and passed the productID from report1 to report2. Report2 is basically the same report you are working on but without the parameter for productID. You can make it hidden.

Feel free to ask me more questions...

|||

I really like your idea and I'm very curious to see how to implement it. Would you mind sending me an example by email (an rdl file for example)? I' ve tried something like that in the past, but I've never succeeded to do it... :-(

For the problem I popsted previously, your solution doesn't meet the requirements because users need to choose several products...

I tried to implement cascading parameters although it is not the best one in terms of performance... So I'm still looking for a better idea.

Thanks

|||

It is very simple....

For exmple if you are using table to output the products then right click on the detail textbox and go to properties and then click on navigation. There you can select jump to a report, select the report you want to jump to and pass productId as a parameter. Now when you run the report you will be able to click on the products and it will take you to the next report that you specified in the properties.

-Rohit

|||

Thanks for your answer! Looks like it is a great idea! I'll try it!

And is there any way to have a "select all" ? ( it is also requiered by my users...)

Thanks

Zoz

Drop Down List Prompt (Report Parameters)

I have 15 Report Parameters. And I have all of them set up as drop downs.
There is going to be times when the user selects all 15 and there are going
to be times when the user might only select 5 or 6 Report Parameters.
I want the parameters to be optional but I also want the parameters to be in
a drop down list.
I tried checking both allow null values and allow blank values in report
parameters.
This works for text box prompts but not for drop down list prompts. I am
being forced to select something from the list even though I do not want to
select anything.
Thanks
PeterI solve this by adding an value to the drop down list "All values".
If your list is based on a query this would be something like (I use oracle,
syntax in SQL-Server might be different):
select Listelement_name, Listelement_value from listelements
union
select "<All Values>", -1 from dual
This way <All values> will always be the first element in the drop down
list, and I put default value = -1.
If you use the parameter as a query parameter you will put:
...and (xxx = @.parameter1 or @.parameter1 = -1) and ...
in the where clause
If it is used for a filter you can f.e. use the switch command:
Switch(Parameters!parameter.value = -1, 1, Parameters!parameter.value =Fields!xxx.value, 1, TRUE, 0) = 1
"Peter" wrote:
> I have 15 Report Parameters. And I have all of them set up as drop downs.
> There is going to be times when the user selects all 15 and there are going
> to be times when the user might only select 5 or 6 Report Parameters.
> I want the parameters to be optional but I also want the parameters to be in
> a drop down list.
> I tried checking both allow null values and allow blank values in report
> parameters.
> This works for text box prompts but not for drop down list prompts. I am
> being forced to select something from the list even though I do not want to
> select anything.
> Thanks
> Peter
>|||That worked Thanks
"Antoon" wrote:
> I solve this by adding an value to the drop down list "All values".
> If your list is based on a query this would be something like (I use oracle,
> syntax in SQL-Server might be different):
> select Listelement_name, Listelement_value from listelements
> union
> select "<All Values>", -1 from dual
> This way <All values> will always be the first element in the drop down
> list, and I put default value = -1.
> If you use the parameter as a query parameter you will put:
> ...and (xxx = @.parameter1 or @.parameter1 = -1) and ...
> in the where clause
> If it is used for a filter you can f.e. use the switch command:
> Switch(Parameters!parameter.value = -1, 1, Parameters!parameter.value => Fields!xxx.value, 1, TRUE, 0) = 1
> "Peter" wrote:
> > I have 15 Report Parameters. And I have all of them set up as drop downs.
> > There is going to be times when the user selects all 15 and there are going
> > to be times when the user might only select 5 or 6 Report Parameters.
> >
> > I want the parameters to be optional but I also want the parameters to be in
> > a drop down list.
> >
> > I tried checking both allow null values and allow blank values in report
> > parameters.
> >
> > This works for text box prompts but not for drop down list prompts. I am
> > being forced to select something from the list even though I do not want to
> > select anything.
> >
> > Thanks
> >
> > Peter
> >

drop down calendar

Has anyone come up with a way to get a drop down calendar
when entering date ranges for a report? TIA RonTake a look related previous post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=76d653b6-5b05-4cc1-8cae-64c9c410c41a&sloc=en-us
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:5c2001c48142$6d8da300$a601280a@.phx.gbl...
> Has anyone come up with a way to get a drop down calendar
> when entering date ranges for a report? TIA Ron

Drop Down Box with edit/input by user

We have a report which is based on Schemes which have pre-defined
start and end dates. I have set up report parameters which are queried
and
the user can select these pre-determined values.. (The user choose a
"Scheme" and both dates are set).
However, there are occasions when the users want to enter a date other
than
one of these values.( by the date control). This I cant get RS to do?
How can I best achieve this switch from selection of a value in a drop
down
list to typing in a value in a text box. If the parameter is queried, a
drop
down list is displayed and the user cannot type in a value. There is no
option to use a "Combo" box and use the display value.
Any help appreciated.
Deepan (This question is takin from another question that was not
answered)I'm looking to do the same thing...
-Ben
Deepan wrote:
> We have a report which is based on Schemes which have pre-defined
> start and end dates. I have set up report parameters which are queried
> and
> the user can select these pre-determined values.. (The user choose a
> "Scheme" and both dates are set).
> However, there are occasions when the users want to enter a date other
> than
> one of these values.( by the date control). This I cant get RS to do?
>
> How can I best achieve this switch from selection of a value in a drop
> down
> list to typing in a value in a text box. If the parameter is queried, a
> drop
> down list is displayed and the user cannot type in a value. There is no
> option to use a "Combo" box and use the display value.
>
> Any help appreciated.
> Deepan (This question is takin from another question that was not
> answered)

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.

Drop a Role in Report Sever

I create a new Role "TestRole"under Security/ Roles in the Report Server.
I i want to drop this Role, i get the Error that the Role does not exist ?
But the Role exist !!
Anyone can help ?

For UI operations, please post to SQL Server Tools General.

Thanks
Laurentiu

Drop a Role in Report Sever

I create a new Role "TestRole"under Security/ Roles in the Report Server.
I i want to drop this Role, i get the Error that the Role does not exist ?
But the Role exist !!
Anyone can help ?

For UI operations, please post to SQL Server Tools General.

Thanks
Laurentiu