Parameters...Null as an available value?!?!?

I have a hierarchy of organizations that I need to be able to filter by
in the reports...
For example, an Agency can have many Applications associated with it.
I have three stored procedures, one that gets a list of Agencies, the
other that gets a list of Applications based on the Agency that was
selected, and the third takes a bunch of other parameters and gets all
the matching orders (or whatever) associated with the Agency and
Application (Agency and Application are both input params to the third
stored proc).
What I really want is for the user to be able to not select an Agency,
basically setting it to Null, and letting the stored procedure that
does the query for the report ignore that parameter and get results for
all agencies.
If I set the parameter in the report to null, then I can't have a drop
down list with the Agency names if the user doesn't want to set the
Agency to null. But I can't have both!!!
What is the best procedure for doing this? Returning a -1 record in
the list of Agencies and using that to indicate null within the stored
proc? Any other ideas? I can't return a null record in the stored
proc because the report throws an exception. Does this make sense?
Any help would be appreciated!!! Thanks, BrianI had a similiar problem, I solved it by using 'All' as below:
Select * from MainTable
Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
@.AgencyName is a parameter, you can set the parameter's default value
to All( you can make one dataset for this parameter as below:
Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
Report Parameters dialogue,Choose available value from query,make All
as default value), then the first part of where clause will always
automatically satisfied unless you choose a different value.
This works great for me, Hope this helps.
Good luck.
Henry
Brian wrote:
> I have a hierarchy of organizations that I need to be able to filter
by
> in the reports...
> For example, an Agency can have many Applications associated with it.
> I have three stored procedures, one that gets a list of Agencies, the
> other that gets a list of Applications based on the Agency that was
> selected, and the third takes a bunch of other parameters and gets
all
> the matching orders (or whatever) associated with the Agency and
> Application (Agency and Application are both input params to the
third
> stored proc).
> What I really want is for the user to be able to not select an
Agency,
> basically setting it to Null, and letting the stored procedure that
> does the query for the report ignore that parameter and get results
for
> all agencies.
> If I set the parameter in the report to null, then I can't have a
drop
> down list with the Agency names if the user doesn't want to set the
> Agency to null. But I can't have both!!!
> What is the best procedure for doing this? Returning a -1 record in
> the list of Agencies and using that to indicate null within the
stored
> proc? Any other ideas? I can't return a null record in the stored
> proc because the report throws an exception. Does this make sense?
> Any help would be appreciated!!! Thanks, Brian|||That's exactly what I ended up doing this morning. Works great so far!
Thanks!
Brian
fanh@.tycoelectronics.com wrote:
> I had a similiar problem, I solved it by using 'All' as below:
> Select * from MainTable
> Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
> @.AgencyName is a parameter, you can set the parameter's default value
> to All( you can make one dataset for this parameter as below:
> Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
> Report Parameters dialogue,Choose available value from query,make All
> as default value), then the first part of where clause will always
> automatically satisfied unless you choose a different value.
> This works great for me, Hope this helps.
> Good luck.
> Henry
>
> Brian wrote:
> > I have a hierarchy of organizations that I need to be able to
filter
> by
> > in the reports...
> >
> > For example, an Agency can have many Applications associated with
it.
> >
> > I have three stored procedures, one that gets a list of Agencies,
the
> > other that gets a list of Applications based on the Agency that was
> > selected, and the third takes a bunch of other parameters and gets
> all
> > the matching orders (or whatever) associated with the Agency and
> > Application (Agency and Application are both input params to the
> third
> > stored proc).
> >
> > What I really want is for the user to be able to not select an
> Agency,
> > basically setting it to Null, and letting the stored procedure that
> > does the query for the report ignore that parameter and get results
> for
> > all agencies.
> >
> > If I set the parameter in the report to null, then I can't have a
> drop
> > down list with the Agency names if the user doesn't want to set the
> > Agency to null. But I can't have both!!!
> >
> > What is the best procedure for doing this? Returning a -1 record
in
> > the list of Agencies and using that to indicate null within the
> stored
> > proc? Any other ideas? I can't return a null record in the stored
> > proc because the report throws an exception. Does this make sense?
> > Any help would be appreciated!!! Thanks, Brian