cancel
Showing results for 
Search instead for 
Did you mean: 

Parameter option for Is Null

Former Member
0 Kudos

I have a column with type.  Users who enter data in the database do not always put something in that column as they enter records.  I have set up Crystal Reports with parameters so a user can select a certain type and display the records with the criteria chosen.  But how do they have the option to select all records where the type field is blank?  What we want to know is how many records are being inputted with the field left blank.

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Chantel,

You have two choices:

1) Assuming the prompt is dynamic, set the prompt as 'optional' and educate the users that Not selecting a value in the prompt will lead the report to show records where 'type' = Null.

The record selection will look something like this:

if Not(hasValue({?My Parameter}))

then

isnull({Type})

else

{Type} = {?My Parameter}

2) Use a Command Object to populate the List of Values and add a wildcard entry, for e.g: *Nulls*.

The query would look like this:

Select table.type from table

UNION

Select *Nulls* from table

Add this query using the 'Add Command' option and Don't Join it to the other tables.

Create a Dynamic prompt against the field in the Command Object and modify the record selection formula to:

If {?Prompt_Name} = "*Nulls*" then

     isnull({Type})

else

     {Type} = {?Prompt_Name}

-Abhilash

Former Member
0 Kudos

My list is static because there are a lot of records with very outdated types so the list is way too long to select from.

I am not sure I understand what you mean.  I am very new to crystal reports so I don't know a lot yet.

abhilash_kumar
Active Contributor
0 Kudos

It's easier when the list of Static.

Just add a new Value called "Nulls" and modify the Record Selection Formula to:

If {?Prompt_Name} = "Nulls" then

     isnull({Type})

else

     {Type} IN {?Prompt_Name}


This will return records where Type is null when a users selects 'Nulls' otherwise it returns whatever 'types' the user chooses.


-Abhilash

Former Member
0 Kudos

When I do this it ignores the date range I previously set.  If I choose a type for lets say type A.  My date range is included.  If I choose nulls, it ignores my date range.

abhilash_kumar
Active Contributor
0 Kudos

If you wish to return Null values 'within the date range', then use this:

(

If {?Prompt_Name} = "Nulls" then

     isnull({Type})

else

     {Type} IN {?Prompt_Name}

)

AND

{Date_field} IN {?DateRange}

If you wish to return all Null values in the table, in other words ignore the Date Range filter and return all nulls, use this:

(

If {?Prompt_Name} = "Nulls" then

     isnull({Type})

else

(

     {Type} IN {?Prompt_Name}

     AND

     {Date_field} IN {?DateRange}

)


-Abhilash


DellSC
Active Contributor
0 Kudos

Using IF statements in the selection criteria can slow down a report that runs against a large data set because Crystal will pull all of the data into memory and filter it there instead of sending the filter to the database.  If this is the case in your situation, try doing the following instead:

(

({?Prompt_Name} = "Nulls" and isNull({Type})) or

{Type} in {?Prompt_Name}

)

Note where I've put the parentheses - these are very important for this to work correctly.

-Dell

Former Member
0 Kudos

My date range is anything greater than 1/1/12.  This is what I have so far.  It keeps giving me errors to fix with no luck when I try.

{TASKS.REQDATE} >= DateTime (2012, 01, 01, 00, 00, 00) and

(

If {?type} = "Nulls" then

     isnull({TASKS.TYPE})

else

     {TASKS.TYPE} IN {?Type}

)

AND

{TASKS.REQDATE} IN {TASKS.REQDATE} >= DateTime (2012, 01, 01, 00, 00, 00)

abhilash_kumar
Active Contributor
0 Kudos

Thanks Dell. Simple If-then statements like the above work for me against Oracle though.

But it's always a good practice not to have them.

-Abhilash

abhilash_kumar
Active Contributor
0 Kudos

What's the last statement for?

Shouldn't it be:

{TASKS.REQDATE} >= DateTime (2012, 01, 01, 00, 00, 00) and

(

If {?type} = "Nulls" then

     isnull({TASKS.TYPE})

else

     {TASKS.TYPE} IN {?Type}

)

-Abhilash

Former Member
0 Kudos

When I use what you just corrected the formula doesn't have errors, but when I try to run the report the parameter, I get an error. This is when I try to do get the null results.

It says:Parameter has no value.

Then it gives me the formula editor.  The bold item is what it has highlighted.

{TASKS.REQDATE} >= DateTime (2012, 01, 01, 00, 00, 00) and

(

If {?type} = "Nulls" then

     isnull({TASKS.TYPE})

else

     {TASKS.TYPE} IN {?Type}

Former Member
0 Kudos

I may need to explain better. 

I run a parameter with a type that does have something in that column such as type test.  I get the results I want.  When I want to get a type that doesn't have something for that column, I don't.  What we are finding is there are too many records with the type being left blank. 

I want only records after 1/1/12 no matter what parameter I use.

My parameter is static with the ability for the user to enter their own (in case I forgot a type or a new type is added).  If it were not static the user's list would be long with outdated types we no longer use. We stopped using many types at the end of 2012.

abhilash_kumar
Active Contributor
0 Kudos

Go to the Field Explorer > Edit the prompt > scroll down and set the 'Optional Prompt' option to 'False'.

-Abhilash

Former Member
0 Kudos

I went back up and saw Dell's post in the middle (I missed it earlier somehow).  I think it is working, but I want to mess with it some more to see.

abhilash_kumar
Active Contributor
0 Kudos

That'll work too, as long as the prompt is Not optional.

-Abhilash