Parameter / Record Selection option to include or exclude NULL values

cpjeffm · Oct 16, 2018

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!

kray4660 · Oct 17, 2018

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.

cpjeffm · Oct 17, 2018

Crystal Reports XI...it was in my original post.

fisheromacse · Oct 17, 2018

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}

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!

Parameter / Record Selection option to include or exclude NULL values

cpjeffm

IS-IT--Management

kray4660

MIS

cpjeffm

IS-IT--Management

fisheromacse

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor