Dynamic Null Parameter

[PNC]

Hi,

I've created a parameter in CR XI with a dynamic list of value based on a field which contains some null values, my problem is the null value option won't show up in the group prompt... And I don't want to modify the formula to say field = null or field = {?My Parameter}, because I want to be able to select a combination of the null value and the non null values.

Any ideas on how to do this?

Thanks.

 

[lbass]

Not sure what you mean by "group prompt". If you set up the parameter to allow multiple values and your record selection formula to look like:

isnull({table.field}) or
{table.field} = {?parameter}

...then you can select one or more values that will appear along with all nulls.

If you then want the nulls to appear in a group with a certain value, you can create a formula like the following and insert a group on it:

if isnull({table.field}) or
{table.field} in ["A","B"] then "Group 1" else
if {table.field} in ["C","D"] then Group 2" else
"Group 3"

-LB

 

[PNC]

Thanks for your reply, but what I would like to have is this, the user cliks on the report, and there's a prompt:

SELECT VALUES FOR FIELD1

Available values:
[NULL]
Value1
Value2
...

I don't know if it's possible in crystal, I don't want to use a text file with a list of values, I would like to point to the field in the sql command query.

Thanks.

 

[PNC]

I found a solution, in the query I change the select statement from SELECT Field to:

 SELECT IsNull(Field, '[NULL]')

When the parameter prompt appears, you can select the null values as well as any other fields.

 

[lbass]

I don't think there is a way to use a dynamic parameter and include nulls in the options list, but you could add a second string parameter {?Nulls or not} with options "Include nulls" or "Exclude nulls". Then you could use a selection formula like:

if {?null or not} = "Include nulls" then
(
isnull({table.field}) or
{table.field} = {?parameter}
) else
if {?null or not} = "Exclude nulls" then
{table.field} = {?parameter}

-LB