How to add a "NULL" option to a dynamic parameter

[jeffm777]

I have a parameter where I need to include a NULL option into the dynamic parameter. The field that includes the NULL values is a string. For example, I want the Available Values option of the parameter to show...

NULL
Andy
Jeff
Bill
Carl
Eddie

 

[lbass]

Create a command that is referenced only to create the dynamic parameter picklist:

select table.name
from table
union
select null
from table

Do not link the command to any tables (ignore the warning) and do not reference the command in the body of the report.

-LB

 

[jeffm777]

Ok. Thanks for the reply but need some help as I'm not too savy with Commands. Here's what I've done thus far...

I created the command and it compiled and I ignored the link warnings. I have the command in my list of database fields and I have created a dynamic parameter pulling in the field from the command. Now what?

 

[lbass]

Actually, let me change that a bit:

The command should read:

select table.name
from table
union
select 'null'
from table

Click on the create parameter column once you have selected the command.field to populate the picklist. Then, in report->selection formula->record, add:

(
(
(
{?Parameter}="Null" and
isnull({table.name})
) or
{table.name} = {?Parameter}
) or
(
{?Parameter}<>"Null" and
{table.name}={?Parameter}
)
)

-LB