It is always nice to have options.  Optional parameters can greatly enhance the functionality of your Crystal Reports and can be designated as such when you are creating a new parameter outside of a Command object by setting the Optional Prompt setting to “True”.

However, there is no way to specify a parameter as optional when created and used within the Command object.

I had always thought you could not have optional parameters when using a Command object.  I guess that technically isn’t true.  You can have optional parameters in your report when using a Command object, but not in your Command object.  Any optional parameter values you specify will not be used when running the query against your data source.  Instead those optional parameter values will be used to filter your data within Crystal Reports once the data is brought back from the database. Depending on the amount of data returned by your Command object query this could be a negligible cost or it could be prohibitive.

The report I am working on that prompted me to write this up has 17 optional parameters including 3 pairs of date ranges and no required parameters.  Since I couldn’t pass any of those optional parameter values along to be processed as part of my query I had avoided doing this report in Crystal Reports. However, this report was a replacement of an existing report embedded in a C# windows forms application based on a stored procedure.  The query was mostly written for me so I could just drop it in a Command object, right?

Well, I did some testing and my base query without out any of the optional parameter values returned about 1.1 million rows and took just over a minute to run in SQL Server. Certainly not a preferable situation, but it seemed better than trying to build a universe and a Crystal Report for Enterprise or Web Intelligence report given the short time frame I have to complete this.

So I decided to go down the road of building this report in Crystal Reports despite not being able to pass any of those optional parameter values to the Command object.  I got the report to the point of running right around 3 minutes.  I am not concerned about the impact on the database because I am hitting a copy, not the live Production database.  Here is the Performance Information from within Crystal Reports of my initial attempt; functional, but very cumbersome.

After wrestling with this report for about a week I had an idea.  Would it work to create a shell main report with all of the parameters set to optional and a subreport with all of the parameters created with the Command object and therefore required?  In order for this to work I figured all of the parameters in the subreport would need default values.  I would link the optional parameters in my main report to the required parameters in my subreport.  Then in my Command object query I could check for the default value of each parameter and negate the corresponding section the where clause of my query with some creative SQL; something like this…

SELECT * FROM MyTable

WHERE (‘N/A’ = ‘{?MyParameter}’ OR MyColumn = ‘{?MyParameter}’)

If no value is provided for MyParameter then its value would be the default value of “N/A” making that portion of the where clause true and thus return all rows.  If a value is provided for MyParameter then only those rows where MyColumn equals MyParameter would be returned.  The database I am using is Microsoft SQL Server 2008 R2.  Keep in mind that the syntax in the Command object is subject to whatever database to which you are connecting and therefore may be different that the examples I am providing.


As I worked through this my thought of specifying and default value and then checking for that didn’t work out as planned.  It seemed as though the main report was passing along NULL or something.  I am not exactly sure, but I do know that the parameter values in my subreport were not being set to their default values when the corresponding optional parameter in the main report was not given a value.


So I removed all of the default values for my Command object parameters.  Then for each optional parameter in my main report I created a formula to detect if a value had been provided and if it hadn’t to set it to something that I would check for in the Command object query of my subreport.  You could use whatever values you want to represent not specifying an optional parameter value, but here is what I used from my values for strings, numbers and dates to signify that no value had been selected…

String = N/A

Number = 0

Date = 01/01/1900

So my formulas (using Crystal Syntax) for each type look something like this…

If HasValue({?MyStringParameter}) Then

     {?MyStringParameter}

Else

     “N/A”

If HasValue({?MyNumberParameter}) Then

     {?MyNumberParameter}

Else

    0

If HasValue({?MyDateParameter}) Then

     {?MyDateParameter}

Else

     CDate (“01/01/1900”)

There are some settings available when creating parameters that I wanted to use such as allowing multiple values or range of values, but not together in the same parameter.  Perhaps there are other scenarios which need special attention, but dealing each of those separately was enough for my report.  When allowing multiple values you are in essence turning your parameter result into an array.  I couldn’t follow the pattern of the three formula examples above to pass along that no value had been selected for a multi-value optional parameter since as I learned “[T]he result of a formula cannot be an array.”


I came up with this for situations where I wanted multiple values…

If HasValue({?MyMultiValueStringParameter}) Then              

     “~” & Join({?MyMultiValueStringParameter},”~,~”) & “~”

Else

     “N/A”

That results in a string like this “~Value1~,~Value2~”.  I used the tilde (“~”) as my special character, but you can use whatever character you want to as long as it is not going to be legitimately found in your data.

SELECT * FROM MyTable

WHERE (‘N/A’ = ‘{?MyMultiValueStringParameter}’ OR PATINDEX(‘%~’ + MyColumn + ‘~%’, ‘{?MyMultiValueStringParameter}’) > 0)

Again, if no value is provided for MyMultiValueStringParameter then its value would be the default value of “N/A” making that portion of the where clause true and thus return all rows.  If a value or values are provided for MyMultiValueStringParameter then only those rows where MyColumn surrounded by tildes is found in the “~Value1~,~Value2~” would be returned.  Note that “PATINDEX” is a Microsoft SQL Server function.  If you are using a different database you will have to use the corresponding function for your database.

In order to deal with optional parameters that allow range of values I created formulas for the beginning and end range values; dates in my case.  Here is formula for MyBeginngingDate…

If HasValue({?MyDateRangeParameter}) Then

                Minimum({?MyDateRangeParameter})

Else

                CDate (“01/01/1900”)

And MyEndingDate…

If HasValue({?MyDateRangeParameter}) Then

                Maximum({?MyDateRangeParameter})

Else

                CDate (“01/01/1900”)

Finally, here is how you would put all of these optional parameter values together in the Command object SQL…

SELECT * FROM MyTable

WHERE (‘N/A’ = ‘{?MyStringParameter}’ OR MyColumn = ‘{?MyStringParameter}’)

     AND (0 = {?MyNumberParameter} OR MyNumberColumn = {?MyNumberParameter})

     AND (’01/01/1900′ = {?MyDateParameter} OR CONVERT (DATE,  MyDateColumn1, 101) = {? MyDateParameter})

     AND (’01/01/1900′ = {?MyBeginningDate} OR CONVERT (DATE,  MyDateColumn2, 101) >= {?MyBeginningDate})

     AND (’01/01/1900′ = {?MyEndingDate} OR CONVERT (DATE,  MyDateColumn2,, 101) <= {?MyEndingDate})

     AND (‘N/A’ = ‘{?MyMultiValueStringParameter}’ OR PATINDEX(‘%~’ + MyMultiValueColumn + ‘~%’, ‘{?MyMultiValueStringParameter}’) > 0)

I did get stuck at a few points which I want to highlight.

  • A Command object parameter of type Date or Number does not require quotes around it, but those of type String do.  I know that seems so simple, but I think that tripped me up for nearly a full day.
  • It took me a while to realize I couldn’t deal with a multi-value optional parameter with a type of Number since I couldn’t pass along the array of numbers in my formula if in fact some values had been chosen.  I had to force those parameters to have type of String.


You may be wondering what kind of performance gains were realized.  Was it worth the effort?  Absolutely! This report went from running in approximately 3 minutes to less than 2 secondsHere is the Performance Information again…


I am pretty sure there are other sources available that detail bits and pieces of how to do this, but I had not found a comprehensive resource with everything put together from start to finish.  Here are a couple links that used as resources and sparked some ideas along the way…

How to create subreports in Crystal Reports – http://scn.sap.com/docs/DOC-21638

Multi-value parameter in command object – http://www.forumtopics.com/busobj/viewtopic.php?p=995432

Extracting date range parameter values – http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=19282

Enjoy,

Noel

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply