Nulls in Parameters

[jrcol12357]

Crystal XI:
I have a view in which I return nulls with 'N'
NVL(L.RETAIL_WHLSALE_TYPE,'N') BUS_CHAN. Works great returns information I want. I can not get nulls 'N' to pull up in my report. Using this (If {?Bus Chan} = "*" then {VW_RPT_p142.BUS_CHAN} = {VW_RPT_p142.BUS_CHAN}
else {VW_RPT_p142.BUS_CHAN} in {?Bus Chan}). Returns all but the nulls. If I select {VW_RPT_p142.BUS_CHAN} = 'N' I get only nulls as would be expected. I have reviewed other information on NULLs in Parameters but I'm not getting it

 

[Turkbear]

Hi,
your code:

If {?Bus Chan} = "*" then {VW_RPT_p142.BUS_CHAN} = {VW_RPT_p142.BUS_CHAN} 
else {VW_RPT_p142.BUS_CHAN} in {?Bus Chan}).

Can be rewritten this way:

{VW_RPT_p142.BUS_CHAN} = 'N' OR
(
If {?Bus Chan} = "*" 
then 
True
else
{VW_RPT_p142.BUS_CHAN} in {?Bus Chan} 
)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I think the following might work:

(
If {?Bus Chan} = "*" then
true else
{VW_RPT_p142.BUS_CHAN} in {?Bus Chan}
)

I think you would get the "N"s if true, but not otherwise--although you could add "N" as one of the list of values and then the user could choose it along with other selected values, if they wanted to.

-LB

 

[jrcol12357]

This shouldn't be difficult but I'm still not returning any 'N' or nulls. I'm 48 records less than my view results. Thank you for your help thus far.

 

[lbass]

What is your entire record selection formula--maybe there is another factor at play here.

-LB

 

[jrcol12357]

This is all of it.
(If {?Regional Manager} = "*" then {VW_RPT_p142.MANAGER} = {VW_RPT_p142.MANAGER}
else {VW_RPT_p142.MANAGER} in {?Regional Manager}) and
(If {?Bus Chan} = "*" then
true
else
{VW_RPT_p142.BUS_CHAN} in {?Bus Chan}) and
(If {?Lender Status} = "*" then {VW_RPT_p142.LENDER_STATUS} = {VW_RPT_p142.LENDER_STATUS}
else {VW_RPT_p142.LENDER_STATUS} in {?Lender Status}).

I was thinking of trying isnull{VW_RPT_p142.BUS_CHAN} or (
If {?Bus Chan} = "*"
then
True
else
{VW_RPT_p142.BUS_CHAN} in {?Bus Chan}
)

 

[lbass]

Please clarify the following:

Do you have "N" and "*" as options in your list of values?
If you choose "*" do you get the "N" values returned?
If you choose "N" do you get the "N" values returned?
If you choose "N" plus other values do you get the "N" values returned?

-LB

 

[jrcol12357]

I have my Parameters set to * - All, C - Correspondence, W- Wholesale, N - Nulls. When I select * I only get W,C. When I select N I get no returns. I haven't gotten to the N + C or N + W yet.

 

[lbass]

If you are now saying you never get the "N's" (which is not what you said in your first post), I think that suggests a problem with your joins in the view.

I'm not sure how views interact with report settings--whether the null is converted to "N" before CR report options are checked, but please let us know whether you have "convert nulls to default values" checked in file->report options.

-LB

 

[jrcol12357]

I now have "convert nulls to default values" selected. I could get 'N' to return only by {VW_RPT_p142.BUS_CHAN}= 'N'. Only in the select and not including any other BUS_CHAN coding.

 

[lbass]

I think the convert nulls to default should NOT be checked.

So you're saying that if you use:

(
If {?Bus Chan} = "*"
then
True
else
{VW_RPT_p142.BUS_CHAN} in {?Bus Chan}
)

and select "N" only, you get no results, but if you use:

{VW_RPT_p142.BUS_CHAN} = {?Bus Chan}

...you do? Makes no sense to me.

If you remove the record selection and put the field in the detail section, do you see only populated fields including "N"?

-LB

 

[jrcol12357]

With the report wide open and no parameters I get all records. I noticed that my problem might be that all the null Business Channels also have null Regional Managers, which I did not account for. Lets suspend this topic after I do more testing. I will provide a conclusion.

 

[lbass]

Yes, I agree that could be the issue.

-LB

 

[jrcol12357]

Turns out that I had another parameter that was returning nulls and it was in the same record as my main question parameter Bus_Chan. It wasn't until I removed the parameters from the report and took a good look at the data to understand the real problem. Next time I will look in the database to see if Nulls are permitted for a parameter I plan on using. This could save some headaches. Thanks for your help.