Crystal Reports: When a parameter is null

[former_member548403]

I have a report that has a null value in the list of its parameter selections. Can the Crystal Report handle a null value in the parameter? My guess is that something will have to be set up, in order for me to pass it to the null value in the parameter. Is there a way to handle a null value in the parameter?

[former_member819228]

basically the parameter is missing in store procedures or in report.

this error might be occur because report is not getting that parameter, first try to get latest from DataBase> Get Datasource Location in Crystal reports.

[DellSC · Accepted solution]

Which version of Crystal are you using? Is the that you want to pass a null value through a parameter for a stored procedure or command, or is it just a parameter you're using in the Select Expert? What do you want to do if the value is null?

-Dell

[former_member548403 · Accepted solution]

It is the version 14.2. Basically, one of the parameter selections is null, or at least that is what the database has. What I would like to have is when a user selects something like "Undetermined" from the parameter list, that would get passed as the null value in the dataset and populate the report with the value. I'm not the best at explaining things, but hopefully you can understand.

[DellSC · Accepted solution]

Are you using a command or are you linking tables together in the report? If you're linking tables, please send the formula from the Select Expert, if you're using a command, please send the "where" clause from your SQL.

Thanks!

-Dell

[former_member548403 · Accepted solution]

Actually, neither; the whole view table is being selected for the report, so no linking tables nor a SQL query.

This, however, is what I have in my formula workshop:

{rpt_Returns.ReturnDate} in {?BeginDate} to {?EndDate} and
{rpt_Returns.barcode} in ["S04014", "S04015", "S04016", "S04104"] and
{rpt_Returns.DepartmentId} = "S" and
(isnull({?ReturnReason}) or  {rpt_Returns.ReturnReason} = {?ReturnReason} )

[DellSC · Accepted solution]

I see what the problem is, try this:

(not HasValue({?ReturnReason}) or  {rpt_Returns.ReturnReason} = {?ReturnReason} )

In the parameter definition, be sure to set "Optional Prompt" to True so that Crystal won't require a value and will show a "Null" checkbox on the parameter.

If you want to use "Undefined" as an option in the lookup, you'll do this instead:

({?ReturnReason} = "Undefined" or  {rpt_Returns.ReturnReason} = {?ReturnReason} )

-Dell

[former_member548403 · Accepted solution]

Thanks! We're getting there. I tried this below at the last line in the formula workshop and it is now pulling the data with null values now.

({?ReturnReason} = "Undefined" or  {rpt_Returns.ReturnReason} = {?ReturnReason} )

BUT for some strange reason, it is also pulling other data with all parameters selected. What do I need to change here?

[DellSC · Accepted solution]

Ahh! Now I see what you're trying to do. Try this instead:

(
  ({?ReturnReason} = "Undefined" and isNull({rpt_Returns.ReturnReason}) 
  or  
  {rpt_Returns.ReturnReason} = {?ReturnReason} 
) 

Note the extra set of parentheses that I added - it won't work correctly without them.

-Dell

[former_member548403 · Accepted solution]

YESSSSSS!!! Thank you!!