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 Lists

I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".

Employees(ID,FirstName,LastName)
Biotechnology(ID,EmployeeID)

**assuming that EmployeeID in "Biotechnology" is the ID from "Employees" table

Example: We add Employees.ID(23, 33) to Biotechnology.EmployeeID

would be

Biotechnology:
[ID] [EmployeeID]
01 - 23
02 - 33Looks like an INSERT statement to me, possibly complicated by your user interface. What's the problem?

Drop down list for columns

What is the best practice to build drop down lists for a database?

for what?

for the login

|||You know a drop down list so on the front-end a user has predefined choices instead of having to type everything in each time.|||

You're asking about the best practices for the UI on the client side, then?

I think that depends on a coulpe of different things. If the query to generate the list is very simple, then I'd probably write code to retreive the data and populate the list each time the UI is shown.

But there are some exceptions. If the data is likely to change (that is, other users are adding or removing things that would show up in the list) I would end up adding a "Refresh" pushbutton next to the dropdown. That way, the user can refresh the content of the list without leaving and re-opening the form or dialog where the control lives. If the control actually is a part of more stable UI (like in a toolbar that's alive as long as the application is alive) you'll certainly want to provdie a refresh button. In places where screen area is limited, you might let the user hold CTRL while clicking the drop-down arrow to cause a refresh.

If the query is complicated and has a long run time, I'd look at ways to either cache the result (by storing it locally with the app; in a config file, or the registry, or so on) or by storing the user's most recent choice. (And I'd see if there was a way to tune the query!) This might no work if there's also lots of changes to the list.

If there are lots of results in the list, you should be sure to implement word wheeling for the control.

And that's all that I can think of for now. I hope it helps; if you have further questions, please do give us some more details about what it is you're doing and we'll see if we can help.

drill-through does not work ?

Hi friends
I have a simple report that lists summary of transactions made by each customer.
when i click on no of transactions ,as you know, it would display invidual transactions automatically.
It all working fine with VS2005 RC1 but recently we bought VS2005 standard edition (Nov release).

with this licensed version i cant drill thru report !! i mean i can run my report ok but if i click on no of transactions it would not display any details !!
is it limitation of standard edition ? is it not default in released version ?
Thank you very much for your help.I suppose you are asking about "infinite clickthrough" (i.e. automatically generated drillthrough links based on the semantic model) in report builder reports, correct?
This feature is only available in the Enterprise Edition (or Developer Edition) of Reporting Services 2005, but not in Standard Edition. Please check: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert|||Thanks Robert.
Thats what i just found !! damn ,thats a nice feature to loose :(
http://msdn2.microsoft.com/en-us/library/ms345252(en-US,SQL.90).aspx

Drilling Down On Totals

Hi all!

I have created a report that lists a number of machine units by machine_description. There's a total at the bottom that can let you see the total number of machine units. You can also drill down on one of the units and see what stores those units are coming from. For example

No. of Units

Axles | 6

Sprockets | 5

Total | 11

You can click on the values (6 or 5) and it will return store the items and what stores they come from.

However, when you click on the total value (in this case 11) it only drills-down to the item that has been LAST returned (in this case it'll be like drillin down on the sprockets value). How can I adapt this drill-down so that it returns the units for all the parts from all the stores in the report?

Any thoughts would be greatly appreciated!

I believe INSCOPE is the way to go, although I've never conquered this beast myself. If someone could give a concrete solution, that would be great.

|||

Yea,

I was JUST looking at this post a little bit later, but implementing is a little tricky.

If anyone can give a little more detail about the guys explanation that'd be great.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1169787&SiteID=1

Thanks gregsql.

PS: If I do find a simpler way to explain a solution to this situation, I'll post it ASAP!

|||

I finally solved my problem. The previous posts about the INSCOPE function were very insightful. The basic breakdown is to use the INSCOPE in an IIF statement such as below -

=IIF(InScope("matrix_name"), Fields!category.Value,"ALL")

After you setup the default values for a multi-valued field for the parameters in your subreport, you can use the drill-down action from the main report to either choose a specific field/category (if TRUE parameter) and IIF this not chosen, then you can ask it to pass the parameter "ALL" (if FALSE parameter) which when passed, selects all the default values for the corresponding parameter in the subreport. Remember to setup the default values for your subreport parameters.

I hope this clears things up for a lot of people in this problem. Good luck!

|||

How does this work if it isn't a multi-valued parameter?

Something like this:

=IIF(InScope("matrix_name"), Fields!category.Value,"")

If the matrix isn't in scope then you should pass nothing to the subreport? Or what should you pass if it is just a regular parameter?

|||

Can you tell me if I am doing this correctly?

I put this in the expression for navigation:

=IIf(InScope("matrix1"),"Rev1drilldown", "Rev1")

However, it appears that regardless of whether I click the subtotal or the field in the matrix, it navigates to Rev1drilldown.

|||

I think I might know what you mean but I'm not sure. What I've done for my parameters that are not multi-valued is used an expression like this

=IIF(Parameters!category.Value = "", "ALL", Parameters!category.Value)

Of course this is assuming that this is not a dependent cascading parameter.

so if nothing is passed to the subreport, it'll default to ALL. I think you can use this WITHIN an INSCOPE function as well... something like:

=IIF(INSCOPE("matrix_name"), Fields!category.Value, IIF(Fields!category.Value = "", "ALL", Parameters!category.Value))

so this way you could pass the parameters instead or pass ALL if you needed to.

I hope I answered your question.

Drilling Down On Totals

Hi all!

I have created a report that lists a number of machine units by machine_description. There's a total at the bottom that can let you see the total number of machine units. You can also drill down on one of the units and see what stores those units are coming from. For example

No. of Units

Axles | 6

Sprockets | 5

Total | 11

You can click on the values (6 or 5) and it will return store the items and what stores they come from.

However, when you click on the total value (in this case 11) it only drills-down to the item that has been LAST returned (in this case it'll be like drillin down on the sprockets value). How can I adapt this drill-down so that it returns the units for all the parts from all the stores in the report?

Any thoughts would be greatly appreciated!

I believe INSCOPE is the way to go, although I've never conquered this beast myself. If someone could give a concrete solution, that would be great.

|||

Yea,

I was JUST looking at this post a little bit later, but implementing is a little tricky.

If anyone can give a little more detail about the guys explanation that'd be great.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1169787&SiteID=1

Thanks gregsql.

PS: If I do find a simpler way to explain a solution to this situation, I'll post it ASAP!

|||

I finally solved my problem. The previous posts about the INSCOPE function were very insightful. The basic breakdown is to use the INSCOPE in an IIF statement such as below -

=IIF(InScope("matrix_name"), Fields!category.Value,"ALL")

After you setup the default values for a multi-valued field for the parameters in your subreport, you can use the drill-down action from the main report to either choose a specific field/category (if TRUE parameter) and IIF this not chosen, then you can ask it to pass the parameter "ALL" (if FALSE parameter) which when passed, selects all the default values for the corresponding parameter in the subreport. Remember to setup the default values for your subreport parameters.

I hope this clears things up for a lot of people in this problem. Good luck!

|||

How does this work if it isn't a multi-valued parameter?

Something like this:

=IIF(InScope("matrix_name"), Fields!category.Value,"")

If the matrix isn't in scope then you should pass nothing to the subreport? Or what should you pass if it is just a regular parameter?

|||

Can you tell me if I am doing this correctly?

I put this in the expression for navigation:

=IIf(InScope("matrix1"),"Rev1drilldown", "Rev1")

However, it appears that regardless of whether I click the subtotal or the field in the matrix, it navigates to Rev1drilldown.

|||

I think I might know what you mean but I'm not sure. What I've done for my parameters that are not multi-valued is used an expression like this

=IIF(Parameters!category.Value = "", "ALL", Parameters!category.Value)

Of course this is assuming that this is not a dependent cascading parameter.

so if nothing is passed to the subreport, it'll default to ALL. I think you can use this WITHIN an INSCOPE function as well... something like:

=IIF(INSCOPE("matrix_name"), Fields!category.Value, IIF(Fields!category.Value = "", "ALL", Parameters!category.Value))

so this way you could pass the parameters instead or pass ALL if you needed to.

I hope I answered your question.

Drilling Down On Totals

Hi all!

I have created a report that lists a number of machine units by machine_description. There's a total at the bottom that can let you see the total number of machine units. You can also drill down on one of the units and see what stores those units are coming from. For example

No. of Units

Axles | 6

Sprockets | 5

Total | 11

You can click on the values (6 or 5) and it will return store the items and what stores they come from.

However, when you click on the total value (in this case 11) it only drills-down to the item that has been LAST returned (in this case it'll be like drillin down on the sprockets value). How can I adapt this drill-down so that it returns the units for all the parts from all the stores in the report?

Any thoughts would be greatly appreciated!

I believe INSCOPE is the way to go, although I've never conquered this beast myself. If someone could give a concrete solution, that would be great.

|||

Yea,

I was JUST looking at this post a little bit later, but implementing is a little tricky.

If anyone can give a little more detail about the guys explanation that'd be great.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1169787&SiteID=1

Thanks gregsql.

PS: If I do find a simpler way to explain a solution to this situation, I'll post it ASAP!

|||

I finally solved my problem. The previous posts about the INSCOPE function were very insightful. The basic breakdown is to use the INSCOPE in an IIF statement such as below -

=IIF(InScope("matrix_name"), Fields!category.Value,"ALL")

After you setup the default values for a multi-valued field for the parameters in your subreport, you can use the drill-down action from the main report to either choose a specific field/category (if TRUE parameter) and IIF this not chosen, then you can ask it to pass the parameter "ALL" (if FALSE parameter) which when passed, selects all the default values for the corresponding parameter in the subreport. Remember to setup the default values for your subreport parameters.

I hope this clears things up for a lot of people in this problem. Good luck!

|||

How does this work if it isn't a multi-valued parameter?

Something like this:

=IIF(InScope("matrix_name"), Fields!category.Value,"")

If the matrix isn't in scope then you should pass nothing to the subreport? Or what should you pass if it is just a regular parameter?

|||

Can you tell me if I am doing this correctly?

I put this in the expression for navigation:

=IIf(InScope("matrix1"),"Rev1drilldown", "Rev1")

However, it appears that regardless of whether I click the subtotal or the field in the matrix, it navigates to Rev1drilldown.

|||

I think I might know what you mean but I'm not sure. What I've done for my parameters that are not multi-valued is used an expression like this

=IIF(Parameters!category.Value = "", "ALL", Parameters!category.Value)

Of course this is assuming that this is not a dependent cascading parameter.

so if nothing is passed to the subreport, it'll default to ALL. I think you can use this WITHIN an INSCOPE function as well... something like:

=IIF(INSCOPE("matrix_name"), Fields!category.Value, IIF(Fields!category.Value = "", "ALL", Parameters!category.Value))

so this way you could pass the parameters instead or pass ALL if you needed to.

I hope I answered your question.

Drilling Down On Totals

Hi all!

I have created a report that lists a number of machine units by machine_description. There's a total at the bottom that can let you see the total number of machine units. You can also drill down on one of the units and see what stores those units are coming from. For example

No. of Units

Axles | 6

Sprockets | 5

Total | 11

You can click on the values (6 or 5) and it will return store the items and what stores they come from.

However, when you click on the total value (in this case 11) it only drills-down to the item that has been LAST returned (in this case it'll be like drillin down on the sprockets value). How can I adapt this drill-down so that it returns the units for all the parts from all the stores in the report?

Any thoughts would be greatly appreciated!

I believe INSCOPE is the way to go, although I've never conquered this beast myself. If someone could give a concrete solution, that would be great.

|||

Yea,

I was JUST looking at this post a little bit later, but implementing is a little tricky.

If anyone can give a little more detail about the guys explanation that'd be great.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1169787&SiteID=1

Thanks gregsql.

PS: If I do find a simpler way to explain a solution to this situation, I'll post it ASAP!

|||

I finally solved my problem. The previous posts about the INSCOPE function were very insightful. The basic breakdown is to use the INSCOPE in an IIF statement such as below -

=IIF(InScope("matrix_name"), Fields!category.Value,"ALL")

After you setup the default values for a multi-valued field for the parameters in your subreport, you can use the drill-down action from the main report to either choose a specific field/category (if TRUE parameter) and IIF this not chosen, then you can ask it to pass the parameter "ALL" (if FALSE parameter) which when passed, selects all the default values for the corresponding parameter in the subreport. Remember to setup the default values for your subreport parameters.

I hope this clears things up for a lot of people in this problem. Good luck!

|||

How does this work if it isn't a multi-valued parameter?

Something like this:

=IIF(InScope("matrix_name"), Fields!category.Value,"")

If the matrix isn't in scope then you should pass nothing to the subreport? Or what should you pass if it is just a regular parameter?

|||

Can you tell me if I am doing this correctly?

I put this in the expression for navigation:

=IIf(InScope("matrix1"),"Rev1drilldown", "Rev1")

However, it appears that regardless of whether I click the subtotal or the field in the matrix, it navigates to Rev1drilldown.

|||

I think I might know what you mean but I'm not sure. What I've done for my parameters that are not multi-valued is used an expression like this

=IIF(Parameters!category.Value = "", "ALL", Parameters!category.Value)

Of course this is assuming that this is not a dependent cascading parameter.

so if nothing is passed to the subreport, it'll default to ALL. I think you can use this WITHIN an INSCOPE function as well... something like:

=IIF(INSCOPE("matrix_name"), Fields!category.Value, IIF(Fields!category.Value = "", "ALL", Parameters!category.Value))

so this way you could pass the parameters instead or pass ALL if you needed to.

I hope I answered your question.

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
>