Skip to Content

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Mike McCracken

    The problem with your If then esle is Crystal treats it as

    The

    If HasValue({?OptionalParameter1}) Then

           {Command.Column1} = {?OptionalParameter1}

    Else 

          (True And  If HasValue({?OptionalParameter2}) Then

                             {Command.Column2} = {?OptionalParameter2}

                         Else

                            (True And …

    You can solve this by putting ( ) around the if then else as

    (

        If HasValue({?OptionalParameter1}) Then

             {Command.Column1} = {?OptionalParameter1}

        Else 

            True

    )

    And 

    (

        If HasValue({?OptionalParameter2}) Then

             {Command.Column2} = {?OptionalParameter2}

        Else

          True

    )

    And

    (

    )

    Depending on your personal style and preference that may be easier to read or understand

    Mike

    (0) 

Leave a Reply