Question Re: Crystal Reports: When a parameter is null in Technology Q&A

Crystal Reports: When a parameter is null

former_member548403 — Tue, 14 Aug 2018 17:08:33 GMT

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?

Re: Crystal Reports: When a parameter is null

DellSC — Tue, 14 Aug 2018 17:45:52 GMT

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

Re: Crystal Reports: When a parameter is null

former_member548403 — Tue, 14 Aug 2018 17:50:10 GMT

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.

Re: Crystal Reports: When a parameter is null

DellSC — Tue, 14 Aug 2018 18:20:47 GMT

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

Re: Crystal Reports: When a parameter is null

former_member548403 — Tue, 14 Aug 2018 18:29:10 GMT

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} )

Re: Crystal Reports: When a parameter is null

DellSC — Tue, 14 Aug 2018 18:39:41 GMT

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

Re: Crystal Reports: When a parameter is null

former_member548403 — Tue, 14 Aug 2018 18:57:53 GMT

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?

Re: Crystal Reports: When a parameter is null

DellSC — Tue, 14 Aug 2018 19:21:23 GMT

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

Re: Crystal Reports: When a parameter is null

former_member548403 — Tue, 14 Aug 2018 19:24:55 GMT

YESSSSSS!!! Thank you!!

Re: Crystal Reports: When a parameter is null

former_member819228 — Fri, 22 Apr 2022 07:57:14 GMT

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.