Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameter / Record Selection option to include or exclude NULL values

Status
Not open for further replies.

cpjeffm

IS-IT--Management
Mar 9, 2015
73
US
Using Crystal Reports XI. I've got a field in our Inventory database called PartCategory and that field has 4 possible values: Y, W, U or NULL.

I'm trying to add a parameter to allow users to select which category(s) they want to include on the report. I've tried adding a static parameter but I can't leave the value as NULL...it doesn't like that. So I decided to do two parameters...one category "Include Categories" for the user to select which categories (Y, W or U) to include and a second parameter "NULL Categories" to either include or exclude NULL categories. Just need some direction on how to setup my record selection to make this happen.

My current record selection is:

{Inventory.Yard} = {?Yard} and
{Inventory.Stock} = {?Stock Numbers} and
{Inventory.PartCategory} = {?Include Categories}

Any help would be greatly appreciated.

Thanks!
 
First off, what version of Crystal Reports are you running? Because a few versions ago, you can have optional parameters (thus allowing null values). Then there is a function called HasValue(). It checks a parameter to see if anything was entered.
 
Crystal Reports XI...it was in my original post.
 
I think this may help.

For your Categories in the parameter, use the word 'Null'.
Then in your select/formulas if the word 'Null' is selected perform the null check.
Something similar to below but with your actual criteria/database

Create a parameter
--{?Param} values = Yard, Stock, Upper Shelf, None

Edit the record selection formula
--Select Formula =
IF {?Param} = "Yard" then
{Inventory.PartCategory} = "Y"
and {whatever other items needed here}
ELSE
IF {?Param} = "Stock" then
{Inventory.PartCategory} = "S"
and {whatever other items needed here}
ELSE
IF {?Param} = "Upper Shelf" then
{Inventory.PartCategory} = "U"
and {whatever other items needed here}
Else
IF {?Param} = "Null" then
(TRIM({Inventory.PartCategory}) = ""
OR
IsNull({Inventory.PartCategory})=TRUE)
and {whatever other items needed here}
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top