Null filter

I'm using a parameter for Superintendents. The parameter drives a filter is
in the dataset; the parameter is not in the sql query.
I want the user to be able to select a single Superintendent from a queried
list, and have the report return filtered data. I also want the user to be
able to select none (or null) and see a full report from an unfiltered
dataset or table.
Problem is that even though I'm allowing null in the param, the report still
demands that a Superintendent be selected.Any solution to this? I have something similar where I
have two filters on a Report and I would like to have
an "All" option on both filters where one or both are
basically ignored.
I am using parameter lists and have tried to set the
value to ALL as '%', '*', <BLANK>, <Null> etc with no
luck.
>--Original Message--
>I'm using a parameter for Superintendents. The
parameter drives a filter is
>in the dataset; the parameter is not in the sql query.
>I want the user to be able to select a single
Superintendent from a queried
>list, and have the report return filtered data. I also
want the user to be
>able to select none (or null) and see a full report from
an unfiltered
>dataset or table.
>Problem is that even though I'm allowing null in the
param, the report still
>demands that a Superintendent be selected.
>.
>|||There is a difference between a filter and a query parameter. Filters filter
the data after first bringing over all the data. If you have a query
parameter then it only brings over the data you want. If you do the latter
then you can define the query parameter like so:
select * from sometable where myfieldname like @.myparam
Bruce L-C
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:100801c48b96$8f804540$3a01280a@.phx.gbl...
> Any solution to this? I have something similar where I
> have two filters on a Report and I would like to have
> an "All" option on both filters where one or both are
> basically ignored.
> I am using parameter lists and have tried to set the
> value to ALL as '%', '*', <BLANK>, <Null> etc with no
> luck.
> >--Original Message--
> >I'm using a parameter for Superintendents. The
> parameter drives a filter is
> >in the dataset; the parameter is not in the sql query.
> >I want the user to be able to select a single
> Superintendent from a queried
> >list, and have the report return filtered data. I also
> want the user to be
> >able to select none (or null) and see a full report from
> an unfiltered
> >dataset or table.
> >Problem is that even though I'm allowing null in the
> param, the report still
> >demands that a Superintendent be selected.
> >.
> >|||Hi Bruce. I understand that. What I am looking for is
an option to basically discard the filter for that record
set. Basically the ability to ignore the filter.
So if I returned back a recordset of all people who lived
in my state, and I had a filter on Gender as well as a
filter on City, I would like to be able to select a
Gender and select all for city and then show all the men
or women in the state, or select a city, and select all
and then show all the men and women in the city.
Right now if I have a filter on city and a filter on
gender, there is no option for a union, just an
intersection.
IMHO, this is a huge shortcoming especially with snapshot
reports. For example to run a sales report for a company
and have a filter on department. As constructed, you
must have a department, so if you wanted to show for the
whole company, you either have to do this at the database
and pass a parameter, or right a separate report.
I am looking for a way to add an "All" option to the
filter which basically will treat as a wildcard or
ignore, and currently it looks like that is not possible.
>--Original Message--
>There is a difference between a filter and a query
parameter. Filters filter
>the data after first bringing over all the data. If you
have a query
>parameter then it only brings over the data you want. If
you do the latter
>then you can define the query parameter like so:
>select * from sometable where myfieldname like @.myparam
>Bruce L-C
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
>news:100801c48b96$8f804540$3a01280a@.phx.gbl...
>> Any solution to this? I have something similar where I
>> have two filters on a Report and I would like to have
>> an "All" option on both filters where one or both are
>> basically ignored.
>> I am using parameter lists and have tried to set the
>> value to ALL as '%', '*', <BLANK>, <Null> etc with no
>> luck.
>> >--Original Message--
>> >I'm using a parameter for Superintendents. The
>> parameter drives a filter is
>> >in the dataset; the parameter is not in the sql query.
>> >I want the user to be able to select a single
>> Superintendent from a queried
>> >list, and have the report return filtered data. I also
>> want the user to be
>> >able to select none (or null) and see a full report
from
>> an unfiltered
>> >dataset or table.
>> >Problem is that even though I'm allowing null in the
>> param, the report still
>> >demands that a Superintendent be selected.
>> >.
>> >
>
>.
>|||--There is a difference between a filter and a query
--parameter. Filters filter the data after first bringing
--over all the data. If you have a query parameter then
--it only brings over the data you want.
Yes and no... I understand what you are saying, but what
I am looking ofr is the option to not apply the filter
and get access to the full reocrd set that is returned.
As it looks like so far to me.. when you set up a filter
for a field, there is no Wildcard or "All" value, so with
a filter you can only see a subset of the recordset, and
never the full unfiltered recordset, which I think you
should be able to see.|||I tried a simply filter on a value with like and then put in * and it
returned everything. So it is possible to have a wildcard.
Bruce L-C
<anonymous@.discussions.microsoft.com> wrote in message
news:032d01c48f87$f9f0eed0$3501280a@.phx.gbl...
> --There is a difference between a filter and a query
> --parameter. Filters filter the data after first bringing
> --over all the data. If you have a query parameter then
> --it only brings over the data you want.
> Yes and no... I understand what you are saying, but what
> I am looking ofr is the option to not apply the filter
> and get access to the full reocrd set that is returned.
> As it looks like so far to me.. when you set up a filter
> for a field, there is no Wildcard or "All" value, so with
> a filter you can only see a subset of the recordset, and
> never the full unfiltered recordset, which I think you
> should be able to see.
>|||Was that a Textbox you entered the '*' in or did you
create a list for it.
I think we are getting close maybe.
I have set up a list for the filter to hide/make it
easier for the values. like Open for status of 'A',
Closed for status 'C', and want to add a All for all
statuses. I have tried assigning to <blank>, <null>, as
well as *, '*', %, '%', etc., and can not seem to assign
a wildcard to a labed value to creat a drop down list.
>--Original Message--
>I tried a simply filter on a value with like and then
put in * and it
>returned everything. So it is possible to have a
wildcard.
>Bruce L-C
><anonymous@.discussions.microsoft.com> wrote in message
>news:032d01c48f87$f9f0eed0$3501280a@.phx.gbl...
>> --There is a difference between a filter and a query
>> --parameter. Filters filter the data after first
bringing
>> --over all the data. If you have a query parameter then
>> --it only brings over the data you want.
>> Yes and no... I understand what you are saying, but
what
>> I am looking ofr is the option to not apply the filter
>> and get access to the full reocrd set that is returned.
>> As it looks like so far to me.. when you set up a
filter
>> for a field, there is no Wildcard or "All" value, so
with
>> a filter you can only see a subset of the recordset,
and
>> never the full unfiltered recordset, which I think you
>> should be able to see.
>
>.
>