Multi comboboxes

Hi,
My instructions are to add more functionality to current reports by adding a
second dropdown. The reports currently have one combobox that lists
Application names. When an Application is chosen the report opens with all
the data associated with that Application.
Now there is a request to add a second combo that would only be visible if
certain choices were made on the first combo. The choice from the second
combo open the report with a more limited set of values from the same source.
Currently stored procedures are used for the combos and the data returned
from choices uses stored procedures with parameters.
Is this possible to do and what is the best plan of attack?
Thanks,
--
FairfieldIf you have the first parameter in the second dataset query, it will
refresh the second list when the first parameter is changed.
So if your first parameter is called Team then the 2nd dataset query
might be something like;
EXEC dbo.sp_GetMyData2 @.Team
This should give you what you want.
Chris
Fairfield wrote:
> Hi,
> My instructions are to add more functionality to current reports by
> adding a second dropdown. The reports currently have one combobox
> that lists Application names. When an Application is chosen the
> report opens with all the data associated with that Application.
> Now there is a request to add a second combo that would only be
> visible if certain choices were made on the first combo. The choice
> from the second combo open the report with a more limited set of
> values from the same source.
> Currently stored procedures are used for the combos and the data
> returned from choices uses stored procedures with parameters.
> Is this possible to do and what is the best plan of attack?
> Thanks,|||Chris,
Thanks for the suggestion about using the first parameter in the second
query. That is a good idea.
But I think I need to clarify the situation a bit to make sure that I'm
fully explaining the situation. Normally the report is driven off of the
first query. However if certain choices are made then they want the second
combo to be visible and the report to run off of that query. Does this make
sense?
Thanks,
--
Fairfield
"|||You do not have that level of control of the parameters. It is possible to
hide parameters, it is not possible to hide/show on demand.
You can decide whether to use the second parameter or not (an easy way is to
send both to a stored procedure and make the decision there). Also, your
query can be an expression so based on the values of the parameters then
create the queries on the fly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fairfield" <Fairfield@.discussions.microsoft.com> wrote in message
news:38DD9B46-AD22-4926-B78A-1A8F3A9A2101@.microsoft.com...
> Chris,
> Thanks for the suggestion about using the first parameter in the second
> query. That is a good idea.
> But I think I need to clarify the situation a bit to make sure that I'm
> fully explaining the situation. Normally the report is driven off of the
> first query. However if certain choices are made then they want the
> second
> combo to be visible and the report to run off of that query. Does this
> make
> sense?
> Thanks,
> --
> Fairfield
>
> "|||Thanks Bruce,
My thinking here is slowly catching up to what we really need. It is 2
levels of choices. The dropdown lists all the choices but on some of them
there needs to be a second choice. There are far too many to list all in
one. An example might be listing cities in the dropdown and then having a
second dropdown open if there are multiple zip codes for that city. Smaller
towns might have 1 or 2 but large cities could have hundreds. The report
would show data on the particular zip code. In this case at the zipcode
level there could be hundreds to choose from so they can't be listed just in
the first dropdown.
Does this make sense? How can this kind of choice by made? It is something
like a treeview.
Thanks,
--
Fairfield
"Bruce L-C [MVP]" wrote:
> You do not have that level of control of the parameters. It is possible to
> hide parameters, it is not possible to hide/show on demand.
> You can decide whether to use the second parameter or not (an easy way is to
> send both to a stored procedure and make the decision there). Also, your
> query can be an expression so based on the values of the parameters then
> create the queries on the fly.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fairfield" <Fairfield@.discussions.microsoft.com> wrote in message
> news:38DD9B46-AD22-4926-B78A-1A8F3A9A2101@.microsoft.com...
> > Chris,
> >
> > Thanks for the suggestion about using the first parameter in the second
> > query. That is a good idea.
> >
> > But I think I need to clarify the situation a bit to make sure that I'm
> > fully explaining the situation. Normally the report is driven off of the
> > first query. However if certain choices are made then they want the
> > second
> > combo to be visible and the report to run off of that query. Does this
> > make
> > sense?
> > Thanks,
> >
> > --
> > Fairfield
> >
> >
> > "
>
>|||Did you try and make queries that have (with a union) a static entry item
(like "NONE"), if it doesn't result in anything, and then default it to an
equation. That equation will determine if the default should be NONE or the
1st item?
--
"Everyone knows something you don't know"
"Fairfield" wrote:
> Thanks Bruce,
> My thinking here is slowly catching up to what we really need. It is 2
> levels of choices. The dropdown lists all the choices but on some of them
> there needs to be a second choice. There are far too many to list all in
> one. An example might be listing cities in the dropdown and then having a
> second dropdown open if there are multiple zip codes for that city. Smaller
> towns might have 1 or 2 but large cities could have hundreds. The report
> would show data on the particular zip code. In this case at the zipcode
> level there could be hundreds to choose from so they can't be listed just in
> the first dropdown.
> Does this make sense? How can this kind of choice by made? It is something
> like a treeview.
> Thanks,
> --
> Fairfield
>
> "Bruce L-C [MVP]" wrote:
> > You do not have that level of control of the parameters. It is possible to
> > hide parameters, it is not possible to hide/show on demand.
> >
> > You can decide whether to use the second parameter or not (an easy way is to
> > send both to a stored procedure and make the decision there). Also, your
> > query can be an expression so based on the values of the parameters then
> > create the queries on the fly.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Fairfield" <Fairfield@.discussions.microsoft.com> wrote in message
> > news:38DD9B46-AD22-4926-B78A-1A8F3A9A2101@.microsoft.com...
> > > Chris,
> > >
> > > Thanks for the suggestion about using the first parameter in the second
> > > query. That is a good idea.
> > >
> > > But I think I need to clarify the situation a bit to make sure that I'm
> > > fully explaining the situation. Normally the report is driven off of the
> > > first query. However if certain choices are made then they want the
> > > second
> > > combo to be visible and the report to run off of that query. Does this
> > > make
> > > sense?
> > > Thanks,
> > >
> > > --
> > > Fairfield
> > >
> > >
> > > "
> >
> >
> >|||I think a combination of my original suggestion and Davids is the best
you can hope for. As Bruce said, you can't conditionally show/hide a
parameter itself at run-time. So you'll just have to limit the options
in the second parameter based on the 1st parameter and whatever other
conditions required.
Chris
David Bienstock wrote:
> Did you try and make queries that have (with a union) a static entry
> item (like "NONE"), if it doesn't result in anything, and then
> default it to an equation. That equation will determine if the
> default should be NONE or the 1st item?|||Thanks for all the help. The 2 dropdowns are now populating correctly and
I'm happy with the way they are working. However a new problem is popping
up. I know it's simply my inexperience with Reporting Services and will soon
seem quite simple.
The first combo chooses the AppID. When the user makes a choice most of the
time the 2nd combo is not filled. But on certain choices the second is
filled. The user then makes a selection on the second and a report should be
returned.
The second combo actually output an AppID, just like the first combo, only
it is different number. The problem is that the report dataset only uses the
AppID from the first combo and doesn't change to the second.
I'm trying to figure out code or a function to force it to use the new
AppID and ignore it when the 2nd combo is null. My experience is in classic
vb and vba and DTS scripts. This all just doesn't seem as intuitive. I
purchased the Hitchhiker book last night and this has helped my understanding.
Thanks
--
Fairfield
"Chris McGuigan" wrote:
> I think a combination of my original suggestion and Davids is the best
> you can hope for. As Bruce said, you can't conditionally show/hide a
> parameter itself at run-time. So you'll just have to limit the options
> in the second parameter based on the 1st parameter and whatever other
> conditions required.
> Chris
>
> David Bienstock wrote:
> > Did you try and make queries that have (with a union) a static entry
> > item (like "NONE"), if it doesn't result in anything, and then
> > default it to an equation. That equation will determine if the
> > default should be NONE or the 1st item?
>|||Fairfield,
This is an easy one;
In SQL, assuming the parameters return integer values (just change
datatype accordingly and add single quotes if a text value);
Select * From MyTable Where AppId = IsNull(@.Combo2, @.Combo1)
or you can use VB to build the query, but this is slightly less
efficient;
="Select * From MyTable Where AppId = " +
IIf(IsDBNull(Parameters!Combo2.Value), Parameters!Combo1.Value,
Parameters!Combo2.Value)
Chris
Fairfield wrote:
> Thanks for all the help. The 2 dropdowns are now populating
> correctly and I'm happy with the way they are working. However a new
> problem is popping up. I know it's simply my inexperience with
> Reporting Services and will soon seem quite simple.
> The first combo chooses the AppID. When the user makes a choice most
> of the time the 2nd combo is not filled. But on certain choices the
> second is filled. The user then makes a selection on the second and
> a report should be returned.
> The second combo actually output an AppID, just like the first combo,
> only it is different number. The problem is that the report dataset
> only uses the AppID from the first combo and doesn't change to the
> second.
> I'm trying to figure out code or a function to force it to use the
> new AppID and ignore it when the 2nd combo is null. My experience is
> in classic vb and vba and DTS scripts. This all just doesn't seem as
> intuitive. I purchased the Hitchhiker book last night and this has
> helped my understanding.
> Thanks|||Thanks for all the help. It's working now.
--
Fairfield
"Fairfield" wrote:
> Hi,
> My instructions are to add more functionality to current reports by adding a
> second dropdown. The reports currently have one combobox that lists
> Application names. When an Application is chosen the report opens with all
> the data associated with that Application.
> Now there is a request to add a second combo that would only be visible if
> certain choices were made on the first combo. The choice from the second
> combo open the report with a more limited set of values from the same source.
> Currently stored procedures are used for the combos and the data returned
> from choices uses stored procedures with parameters.
> Is this possible to do and what is the best plan of attack?
> Thanks,
> --
> Fairfield