Using multiple optional parameters

[nscheaffer]

I just created a report which had two required parameters and five optional parameters.  I found quite a bit of information about how to create and use an optional parameter, but I could not find anything how to make use of more than one optional parameter.  I couldn't figure out how to translate what I was finding into working for more then one parameter.

I got the raw T-SQL query working as I wanted it to in Microsoft SQL Server using COALESCE function as follows where @OptionalParameter1 to @OptionalParameterN are variables representing my optional parameters...

SELECT *

FROM myTable

WHERE myTable.Column1 = COALESCE(@OptionalParameter1, myTable.Column1)

     AND myTable.Column2 = COALESCE(@OptionalParameter2, myTable.Column2)

...

     AND myTable.ColumnN = COALESCE(@OptionalParameterN, myTable.ColumnN)

I attempted to translate that SQL criteria to the following in the Record Selection Formula of my report as follows...

If HasValue({?OptionalParameter1}) Then {Command.Column1} = {?OptionalParameter1} Else True And

If HasValue({?OptionalParameter2}) Then {Command.Column2} = {?OptionalParameter2} Else True And

...

If HasValue({?OptionalParameterN}) Then {Command.ColumnN} = {?OptionalParameterN} Else True

That worked, but only for the first optional parameter for which I had specified a value.  I knew somehow I needed to eliminate the If-statements, but I was stuck. 

At last, in yet another desperate Google search I came across this, Crystal Reports: Parameters Optional.  Although that didn't answer my question directly it helped re-frame my logic in such a way that I was able to get rid of the If-statements as follows.

(Not HasValue ({?OptionalParameter1}) Or {Command.Column1} = {?OptionalParameter1}) And

(Not HasValue ({?OptionalParameter2}) Or {Command.Column2} = {?OptionalParameter2}) And

...

(Not HasValue ({?OptionalParameterN}) Or {Command.ColumnN} = {?OptionalParameterN})

My report is working beautifully now.  Hopefully this will help someone down the line.

Noel