Dynamic Parameter Question

I have a report that has different layers of resolution: Global, Region, State, Facility and Issue. For different each level
chosen I want to display a different set of parameters and execute a different SP for my dataset (which will return the same rows).
I've seen posts where I can do the dynamic SP calls but I'm stumped on the showing and hiding of the parameters that I need. I
tried putting a =IIF(blah... as the prompt but that just displayed the expression and didn't evaluate it.
ANY help is appreciated to keep me from having to do 5 different reports :)
Thanks
ScottI think to do this you would want cascading parameters. Then when you call
your stored procedure you will need a wrapper sp that determines which to
call.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> I have a report that has different layers of resolution: Global, Region,
State, Facility and Issue. For different each level
> chosen I want to display a different set of parameters and execute a
different SP for my dataset (which will return the same rows).
> I've seen posts where I can do the dynamic SP calls but I'm stumped on the
showing and hiding of the parameters that I need. I
> tried putting a =IIF(blah... as the prompt but that just displayed the
expression and didn't evaluate it.
> ANY help is appreciated to keep me from having to do 5 different reports
:)
> Thanks
> Scott
>|||Or you create 1 report with all the parameters and then use linked reports
in which you enable specific parameters.
It's like creating 5 reports, but way faster :-)
Hth,
Tom
"Bruce L-C [MVP]" wrote:
> I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> > I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
> > chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
> > I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
> > tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
> >
> > ANY help is appreciated to keep me from having to do 5 different reports
> :)
> >
> > Thanks
> > Scott
> >
> >
>
>|||Yeah, but how can I set up my parameters to be cascading like that?
IE
Global requires no parameters
Region requires a FK of a region
State requires a two letter state
Facility requires a facilities key value (int)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:Ofr5IHTvEHA.1300@.TK2MSFTNGP14.phx.gbl...
>I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
>> I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
>> chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
>> I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
>> tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
>> ANY help is appreciated to keep me from having to do 5 different reports
> :)
>> Thanks
>> Scott
>>
>|||even if you have cascading paprameters, it would still show up on the report
though greyed out, making the report parameter section really cluttered.
Having the IIF at the prompt of the parameters is a real cool add on. Should
be added to the wish list.
I would just create subreports, as suggested later
"Bruce L-C [MVP]" wrote:
> I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> > I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
> > chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
> > I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
> > tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
> >
> > ANY help is appreciated to keep me from having to do 5 different reports
> :)
> >
> > Thanks
> > Scott
> >
> >
>
>|||I was thinking that these were somehow related. Only certain regions based
on Global, only certain states based on region, only certain facilities
based on State. If so, they you have each of them based on a dataset where
the dataset query parameter is based on the preceding parameter.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:Oez$zzbvEHA.716@.TK2MSFTNGP10.phx.gbl...
> Yeah, but how can I set up my parameters to be cascading like that?
> IE
> Global requires no parameters
> Region requires a FK of a region
> State requires a two letter state
> Facility requires a facilities key value (int)
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Ofr5IHTvEHA.1300@.TK2MSFTNGP14.phx.gbl...
> >I think to do this you would want cascading parameters. Then when you
call
> > your stored procedure you will need a wrapper sp that determines which
to
> > call.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in
message
> > news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> >> I have a report that has different layers of resolution: Global,
Region,
> > State, Facility and Issue. For different each level
> >> chosen I want to display a different set of parameters and execute a
> > different SP for my dataset (which will return the same rows).
> >> I've seen posts where I can do the dynamic SP calls but I'm stumped on
the
> > showing and hiding of the parameters that I need. I
> >> tried putting a =IIF(blah... as the prompt but that just displayed the
> > expression and didn't evaluate it.
> >>
> >> ANY help is appreciated to keep me from having to do 5 different
reports
> > :)
> >>
> >> Thanks
> >> Scott
> >>
> >>
> >
> >
>