Frustrating Newbie Parameter Question

Is it possible to create a parameter where a NULL value returns ALL records
and entering a value returns the filtered data? I am filtering a date field
"ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user a
choice of filtering the dataset with a paramater or leaving it blank and
returning records with all years.
There must be an easy way to do this. I am aware of placing an iif
statement in the dataset paramater
(i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
Parameters!Parameter1.Value)),
but if the parameter is empty, how will this return ALL records? I am
confused and would appreciate any help.
My query looks like this...
SELECT OrderDetail.*
FROM OrderDetail
WHERE (fkCategoryID = 4) AND (ConvYear = ?)
Thanks!Two points, unless you have to use a filter, don't (hard for me to tell if
you are using a filter or not). A filter first retrieves all the data
defined in the dataset and then filters it. So for instance with what you
have below, you should use that query as the definition for your dataset.
Also note that for a query parameter you use @.whateveryouwanttocallit if you
are going against SQL Server and ? if going against oledb or odbc datasourc.
OK, the trick here is to use like. Then for your parameters have an All that
returns a %. To test this out first try have your labels and values be a
list that you input on the report parameters screen (you can also use a
Union query to do this as well). What I like about this solution is you
don't have to use the generic query screen plus you don't lose having a
field list. Anyway, give it a try.
SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID =4) AND (ConvYear like ?)
Bruce L-C
"Brian" <bcovington@.hotmail.com> wrote in message
news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a parameter where a NULL value returns ALL
records
> and entering a value returns the filtered data? I am filtering a date
field
> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user
a
> choice of filtering the dataset with a paramater or leaving it blank and
> returning records with all years.
> There must be an easy way to do this. I am aware of placing an iif
> statement in the dataset paramater
> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
> Parameters!Parameter1.Value)),
> but if the parameter is empty, how will this return ALL records? I am
> confused and would appreciate any help.
> My query looks like this...
> SELECT OrderDetail.*
> FROM OrderDetail
> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> Thanks!
>
>
>
>|||This works great! Would this be a good solution if I wanted say 5 optional
parameters in a report, or is the LIKE clause too slow? I have about 20,000
records to search on, but the report isn't run very often.
Also, I have seen others discuss using dynamic SQL with iff statements to
obtain the same results. Would this possibly be a more efficient solution
than the LIKE clause?
Thanks,
Brian
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> Two points, unless you have to use a filter, don't (hard for me to tell if
> you are using a filter or not). A filter first retrieves all the data
> defined in the dataset and then filters it. So for instance with what you
> have below, you should use that query as the definition for your dataset.
> Also note that for a query parameter you use @.whateveryouwanttocallit if
> you
> are going against SQL Server and ? if going against oledb or odbc
> datasourc.
> OK, the trick here is to use like. Then for your parameters have an All
> that
> returns a %. To test this out first try have your labels and values be a
> list that you input on the report parameters screen (you can also use a
> Union query to do this as well). What I like about this solution is you
> don't have to use the generic query screen plus you don't lose having a
> field list. Anyway, give it a try.
> SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID => 4) AND (ConvYear like ?)
> Bruce L-C
> "Brian" <bcovington@.hotmail.com> wrote in message
> news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to create a parameter where a NULL value returns ALL
> records
>> and entering a value returns the filtered data? I am filtering a date
> field
>> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user
> a
>> choice of filtering the dataset with a paramater or leaving it blank and
>> returning records with all years.
>> There must be an easy way to do this. I am aware of placing an iif
>> statement in the dataset paramater
>> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
>> Parameters!Parameter1.Value)),
>> but if the parameter is empty, how will this return ALL records? I am
>> confused and would appreciate any help.
>> My query looks like this...
>> SELECT OrderDetail.*
>> FROM OrderDetail
>> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
>> Thanks!
>>
>>
>>
>|||I prefer the method I showed you because it is soooo much easier to develop,
you get the list of fields, you can use the normal query designer. Whether
it is slow or not depends on the database you are going against. If it is
SQL Server 2000 and you have 20,000 records it won't even breath hard.
20,000 is nothing. I suggest trying it this way, if performance is a problem
you can always go to dynamic sql.
Bruce L-C
"Brian" <bcovington@.hotmail.com> wrote in message
news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
> This works great! Would this be a good solution if I wanted say 5
optional
> parameters in a report, or is the LIKE clause too slow? I have about
20,000
> records to search on, but the report isn't run very often.
> Also, I have seen others discuss using dynamic SQL with iff statements to
> obtain the same results. Would this possibly be a more efficient solution
> than the LIKE clause?
> Thanks,
> Brian
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> > Two points, unless you have to use a filter, don't (hard for me to tell
if
> > you are using a filter or not). A filter first retrieves all the data
> > defined in the dataset and then filters it. So for instance with what
you
> > have below, you should use that query as the definition for your
dataset.
> >
> > Also note that for a query parameter you use @.whateveryouwanttocallit if
> > you
> > are going against SQL Server and ? if going against oledb or odbc
> > datasourc.
> >
> > OK, the trick here is to use like. Then for your parameters have an All
> > that
> > returns a %. To test this out first try have your labels and values be a
> > list that you input on the report parameters screen (you can also use a
> > Union query to do this as well). What I like about this solution is you
> > don't have to use the generic query screen plus you don't lose having a
> > field list. Anyway, give it a try.
> > SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID
=> > 4) AND (ConvYear like ?)
> >
> > Bruce L-C
> >
> > "Brian" <bcovington@.hotmail.com> wrote in message
> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> Is it possible to create a parameter where a NULL value returns ALL
> > records
> >> and entering a value returns the filtered data? I am filtering a date
> > field
> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
user
> > a
> >> choice of filtering the dataset with a paramater or leaving it blank
and
> >> returning records with all years.
> >>
> >> There must be an easy way to do this. I am aware of placing an iif
> >> statement in the dataset paramater
> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
> >> Parameters!Parameter1.Value)),
> >> but if the parameter is empty, how will this return ALL records? I am
> >> confused and would appreciate any help.
> >>
> >> My query looks like this...
> >>
> >> SELECT OrderDetail.*
> >> FROM OrderDetail
> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> >>
> >> Thanks!
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Bruce,
Your time was very much appreciated. Thank you!
Brian
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ueS7sG1nEHA.3216@.tk2msftngp13.phx.gbl...
>I prefer the method I showed you because it is soooo much easier to
>develop,
> you get the list of fields, you can use the normal query designer. Whether
> it is slow or not depends on the database you are going against. If it is
> SQL Server 2000 and you have 20,000 records it won't even breath hard.
> 20,000 is nothing. I suggest trying it this way, if performance is a
> problem
> you can always go to dynamic sql.
> Bruce L-C
> "Brian" <bcovington@.hotmail.com> wrote in message
> news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> This works great! Would this be a good solution if I wanted say 5
> optional
>> parameters in a report, or is the LIKE clause too slow? I have about
> 20,000
>> records to search on, but the report isn't run very often.
>> Also, I have seen others discuss using dynamic SQL with iff statements to
>> obtain the same results. Would this possibly be a more efficient
>> solution
>> than the LIKE clause?
>> Thanks,
>> Brian
>>
>>
>> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
>> > Two points, unless you have to use a filter, don't (hard for me to tell
> if
>> > you are using a filter or not). A filter first retrieves all the data
>> > defined in the dataset and then filters it. So for instance with what
> you
>> > have below, you should use that query as the definition for your
> dataset.
>> >
>> > Also note that for a query parameter you use @.whateveryouwanttocallit
>> > if
>> > you
>> > are going against SQL Server and ? if going against oledb or odbc
>> > datasourc.
>> >
>> > OK, the trick here is to use like. Then for your parameters have an All
>> > that
>> > returns a %. To test this out first try have your labels and values be
>> > a
>> > list that you input on the report parameters screen (you can also use a
>> > Union query to do this as well). What I like about this solution is you
>> > don't have to use the generic query screen plus you don't lose having a
>> > field list. Anyway, give it a try.
>> > SELECT OrderDetail.* FROM OrderDetail WHERE
>> > (fkCategoryID
> =>> > 4) AND (ConvYear like ?)
>> >
>> > Bruce L-C
>> >
>> > "Brian" <bcovington@.hotmail.com> wrote in message
>> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> >> Is it possible to create a parameter where a NULL value returns ALL
>> > records
>> >> and entering a value returns the filtered data? I am filtering a date
>> > field
>> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
> user
>> > a
>> >> choice of filtering the dataset with a paramater or leaving it blank
> and
>> >> returning records with all years.
>> >>
>> >> There must be an easy way to do this. I am aware of placing an iif
>> >> statement in the dataset paramater
>> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = "
>> >> +
>> >> Parameters!Parameter1.Value)),
>> >> but if the parameter is empty, how will this return ALL records? I am
>> >> confused and would appreciate any help.
>> >>
>> >> My query looks like this...
>> >>
>> >> SELECT OrderDetail.*
>> >> FROM OrderDetail
>> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
>> >>
>> >> Thanks!
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||We've used a statement in the where clause like :
where (ssn.Item_Number = IsNull(@.Stock_Code, ssn.Item_Number))
which returns all items if the sp parameter is null of uses the parameter
value to filter.
Julian
"Brian" <bcovington@.hotmail.com> wrote in message
news:uJyf4T1nEHA.132@.TK2MSFTNGP09.phx.gbl...
> Bruce,
> Your time was very much appreciated. Thank you!
> Brian
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ueS7sG1nEHA.3216@.tk2msftngp13.phx.gbl...
> >I prefer the method I showed you because it is soooo much easier to
> >develop,
> > you get the list of fields, you can use the normal query designer.
Whether
> > it is slow or not depends on the database you are going against. If it
is
> > SQL Server 2000 and you have 20,000 records it won't even breath hard.
> > 20,000 is nothing. I suggest trying it this way, if performance is a
> > problem
> > you can always go to dynamic sql.
> >
> > Bruce L-C
> >
> > "Brian" <bcovington@.hotmail.com> wrote in message
> > news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> This works great! Would this be a good solution if I wanted say 5
> > optional
> >> parameters in a report, or is the LIKE clause too slow? I have about
> > 20,000
> >> records to search on, but the report isn't run very often.
> >>
> >> Also, I have seen others discuss using dynamic SQL with iff statements
to
> >> obtain the same results. Would this possibly be a more efficient
> >> solution
> >> than the LIKE clause?
> >>
> >> Thanks,
> >>
> >> Brian
> >>
> >>
> >>
> >>
> >> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> >> > Two points, unless you have to use a filter, don't (hard for me to
tell
> > if
> >> > you are using a filter or not). A filter first retrieves all the data
> >> > defined in the dataset and then filters it. So for instance with what
> > you
> >> > have below, you should use that query as the definition for your
> > dataset.
> >> >
> >> > Also note that for a query parameter you use @.whateveryouwanttocallit
> >> > if
> >> > you
> >> > are going against SQL Server and ? if going against oledb or odbc
> >> > datasourc.
> >> >
> >> > OK, the trick here is to use like. Then for your parameters have an
All
> >> > that
> >> > returns a %. To test this out first try have your labels and values
be
> >> > a
> >> > list that you input on the report parameters screen (you can also use
a
> >> > Union query to do this as well). What I like about this solution is
you
> >> > don't have to use the generic query screen plus you don't lose having
a
> >> > field list. Anyway, give it a try.
> >> > SELECT OrderDetail.* FROM OrderDetail WHERE
> >> > (fkCategoryID
> > => >> > 4) AND (ConvYear like ?)
> >> >
> >> > Bruce L-C
> >> >
> >> > "Brian" <bcovington@.hotmail.com> wrote in message
> >> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> >> Is it possible to create a parameter where a NULL value returns ALL
> >> > records
> >> >> and entering a value returns the filtered data? I am filtering a
date
> >> > field
> >> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
> > user
> >> > a
> >> >> choice of filtering the dataset with a paramater or leaving it blank
> > and
> >> >> returning records with all years.
> >> >>
> >> >> There must be an easy way to do this. I am aware of placing an iif
> >> >> statement in the dataset paramater
> >> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear ="
> >> >> +
> >> >> Parameters!Parameter1.Value)),
> >> >> but if the parameter is empty, how will this return ALL records? I
am
> >> >> confused and would appreciate any help.
> >> >>
> >> >> My query looks like this...
> >> >>
> >> >> SELECT OrderDetail.*
> >> >> FROM OrderDetail
> >> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> >> >>
> >> >> Thanks!
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Excellent tip! The visual designer messes up statements like:
(@.ItemNo Is Null OR ItemNumber = @.ItemNo)sql