how to pass null value when the parameters are left in empty

Question

Hai friends

I am developing a crystal reports from the database just I am invoking a parameters values in the crystal reports itself. I just used command field for that. My issue is when I run the crystal reports, if I leave any parameter to null it should bring all the records from the database. How is it possible?

Thanks in advance

with regrds karthik

Answer 1

Crystal Reports requires each Parameter Field to have a value; nulls aren't allowed.

If you are using a Command object, you will need to use syntax like this in SQL:

--t-sql syntax; assumes numeric value and -1 is the value chosen to represent 'all values'
WHERE  table.field = CASE 
                       WHEN {?command_object_prompt} = -1 THEN table.field
                       ELSE {?command_object_prompt}
                     END

If you are using 'standard' database linking (as opposed to a Command object), look at my posting Crystal Reports: Optional-Multi-Select Parameters.

Answer 2

If you're using parameters in a Database Command, you can't use optional parameters. However, if you're using CR2008 (I think that's when they were added), you can set your parameters as optional, and then use the HasValue() function to see if the user selected a value.

RecordSelection:

If HasValue({?MyParm}) Then
   {Command.MYFIELD} = {?MyParm}
Else
    True;