NULL Values in Parameter 1

[DHS44]

Hello,
I am using Crystal Reports XI and am trying to get null string values included in my parameter selection. Using a county field for example, when I run the parameter it only lists counties with values, therefore if I select all counties listed I am still missing records where the county name is missing. Does anybody know how to get null values listed in my parameter selection? I have also tried creating a formula field to fill in missing county names with NA for example, but there does not seem to be a way to use a formula field in a parameter selection. Any ideas out there? Thanks.

 

[hilfy]

Are you using a command or tables/views to provide the data for your report?

If you're using tables and/or views, you can create a SQL Expression that converts the null values to NA and then (I believe) use that to provide the data for your dynamic param. A SQL Expression is basically anything that you could use in the Select clause of a query. So, if I were writing this on an oracle database, I would use something like the following in the expression:

nvl(TABLE.COUNTY, 'NA')

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[DHS44]

Thanks for the tip. I am linking to a table/view for the report.
I was hoping to find some code that could be entered into the record selection formula. I tried entering your code there, but Crystal didn't like it.

 

[lbass]

Add "All" as an option in your parameter pick list, and then use a record selection formula like this:

(
(
{?County} <> "All" and
{table.county}={?County}
) or
{?County}="All"
)

-LB

 

[DHS44]

It worked! Thanks lbass.
Your technique is most excellent!

 

[DHS44]

I guess I've got one more question on this one afterall.
I see how to add "all" as an option in a static parameter, but not for a dynamic parameter. How can the dynamic parameter be updated? Thanks.

 

[lbass]

What dynamic parameter? Use a command to create the parameter picklist, like this:

select table.county
from table
union
select 'All'
from table

Do not link the command to your table--ignore the warning message--and do not reference the command field in your main report.

When you set up the dynamic parameter, choose {command.county} to populate the picklist. Use the record selection formula from my previous post.

-LB