Skip to Content

In version 9 and newer Crystal has a feature called a “Command”.  A Command is a SQL Select statement that the user can write to extract data for a report.  Commands are available for most, but not all, databases.  To determine whether a Command can be created for a specific type of connection, open the connection in Crystal; the first line under the name of the open connection will be “Add Command”.

Commands use the SQL syntax of the database that is being connected to. So, for example, when connecting to MS SQL Server, the “getDate()” function is used to get the current date but in Oracle it would be “SysDate”.  Crystal formulas cannot be used in a command, so any calculations or filters must use SQL syntax.

What follows are some best practices for working with Commands in Crystal.

 

A Command should include ALL of the data required in a report.

When a Command is linked in the Database Expert with other tables or Commands, Crystal is unable to push the joins to the database for processing. Instead, all of the data from the Command along with all of the data from the tables is pulled into memory and Crystal will process the join.  With inner joins, this will frequently mean that much more data is brought into memory than will actually be used on the report.  As a consequence, joining Commands to tables or other Commands can significantly slow down report processing

 

Any data filtering MUST be done in the Where clause of the Command.

When the Select Expert is used against a command, Crystal is once again unable to push the filter down to the database and instead pulls all of the data into memory and processes the filter there.  Just like when a Command is joined to tables or other Commands, this can significantly slow down report processing.

 

SQL Expressions.
It is not possible to create or use SQL Expressions in the Field Explorer for the report when using a Command. Instead, the syntax of the SQL Expression  should be used in the Command itself as either an additional “field” or in the Where clause.

 

Parameters.

1.  Parameters that are used in a Command MUST be created in the Command Editor.  The Command Editor cannot “see” parameters that are created in the Field Explorer for the report.  However, in the Command Editor only the Parameter Name, Prompting Text, Value Type, Default Value, and whether the prompt allows multiple values can be set.

Also, if a parameter is created but not used in the Command Editor, the parameter will not actually be created for the report.  However, once a parameter is created in the Command Editor and used in a Command, it can be modified in the Parameters section of the Field Explorer for the report. In this way the parameter can be made dynamic and almost any other properties can be set.

2.  When a parameter is a string value, it must be surrounded by single quotes in the Command in order for it to work.  When a parameter is multi-select, use “in” instead of “=”.  So, a where clause might look like this:

                Where MyTable.StringField = {?String Param}

and MyTable.NumberField in ({?Multi-select Number Param})

and MyTable.DateField = {?Date Param}

3. Parameters that are used in Commands CANNOT be optional.   Instead, I generally use a default of “*All” (the asterisk causes this to sort to the top in dynamic parameters) to indicate that all values should be selected.  In the Where clause, it would be used something like this:

Where (‘{?String Param}’ = ‘*All’ or MyTable.StringField = ‘{?String Param}’)

4. DO NOT use the Command that provides data for the report to provide data for dynamic parameters.  The parameters will not show any field values until after the Command has been run. Instead, do one of the following:

a.  If the report is being run through BusinessObjects (BO) or Crystal Reports Server (CRS), use the Business View Manager that is installed with the Client Tools to create the List of Values that will provide data to a dynamic parameter.  In general, ALL dynamic parameters for reports that are running in BO or CRS should be created and managed in the Business View Manager – it’s more efficient and allows for parameters to be reused instead of recreating them for every report.  At some point in the future I plan to post a blog that walks through how to do this.

b.  If the report is not being run through BO or CRS, then do the following:

      • In the Database Expert, add a Table or Command
        that will provide the data for the parameter.
      • DO NOT link this Table or Command to anything.  Crystal will throw a warning that this is
        generally not supported.  This warning
        can be ignored because this type of configuration is supported for this
        purpose.
      • Use fields from the new Table or Command in the
        parameter configuration to provide data for the parameter.
      • DO NOT use fields from the new Table or Command
        anywhere else in the report.

 

When these best practices are followed, Commands can become a very powerful tool to provide data in Crystal Reports.  Especially since there are things that can be done in a Command that cannot be done when linking tables in Crystal.  For example, it is possible to filter dated data to show only the most recent date, use a “Having” clause to show only data that meets a certain summary threshold, work with “Union” queries, or even pivot data so that it can be shown as if it were a cross-tab without actually using a cross-tab.  Just about anything that can be done in a SQL query in a database can be done in a Command, which makes it a powerful tool for reporting.

To report this post you need to login first.

19 Comments

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

  1. Arthur Shanklin

    Dell,

    your information in the section above is very informative. I really appreciate the explanation especially to some who are new to Crstal reports and how the add command works effortlessly.

    (0) 
  2. Mark Richardson

    As with most things this COMMAND Feature works better / easier in CR-2013 than in CRYSTAL REPORTS FOR ENTERPRISE.

    Hope that CR4E gets some “love and attention” in 2016 from a User Experience and Customer Feedback P.O.V.

    Lot’s of little things are either missing or too-cumbersome in the current release.

    (0) 
    1. Dell Stinnett-Christy Post author

      I absolutely agree with you Mark.  I’ve been using Crystal for over 19 years and I avoid CR4E when I can – I just can’t make it “sing” like I can “classic” Crystal because the features just aren’t there.

      -Dell

      (0) 
  3. Gary Chen

    Great post! I wish I could read it last week. I wasted several days struggling on providing data for dynamic parameter until I read it today. Thank you, Dell.

    (0) 
  4. mark kuula

    Dell, I just want to confirm…the Select Expert information….

    Any data filtering MUST be done in the Where clause of the Command.

    When the Select Expert is used against a command, Crystal is once again unable to push the filter down to the database and instead pulls all of the data into memory and processes the filter there.  Just like when a Command is joined to tables or other Commands, this can significantly slow down report processing.



    If I want to select invoices by date (considering we have a lot of invoices)…. the SQL Command would bring back all of the invoices and if I add a Select Expert filter…


    example…


    {Command.InvDate} IN (

    IF {?DateRange}= “YearToDate” THEN YearToDate

    ELSE IF {?DateRange}= “LastFullMonth” THEN LastFullMonth

    ELSE IF {?DateRange}= “MonthToDate” THEN MonthToDate

    ELSE IF {?DateRange}=”LastFullWeek” THEN LastFullWeek

    ELSE IF {?DateRange}= “Yesterday” THEN CurrentDate-1

    ELSE CurrentDate

    )

    The above Select Expert filter/formula would be run AFTER the SQL command is run ?

    after it pulls back all of the invoices…

    so, the only way to have an effect on the sql command before it is executed, is to have sql command parameters…

    (0) 
    1. Dell Stinnett-Christy Post author

      That is correct.  In the command, I might rewrite it like this for the Where clause (assuming you’re on SQL Server – syntax may be different for other databases):

      table.InvDate >=  –Get the start date of the time period

        (Case

           when ‘{?DateRange}’ = ‘YearToDate’ then (GetDate() – DatePart(dy, GetDate()) + 1)

           when ‘{?DateRange}’ = ‘LastFullMonth’ then (DateAdd(month, -1, GetDate()) – DatePart(d, GetDate()) + 1)

           when ‘{?DateRange}’ = “MonthToDate” then DateAdd(d, (-1 * DatePart(d, GetDate())), GetDate()) + 1

           when ‘{?DateRange}’ = ‘LastFullWeek’ then DateAdd(ww, (-1 * DatePart(w, GetDate())), GetDate())

           when ‘Yesterday’ then GetDate() -1

           else GetDate()

         end)

      table.InvDate <= –Get the end date of the time period

      (Case

           when ‘{?DateRange} = ‘LastFullMonth’ then DateAdd(d, (-1 * DatePart(d, GetDate())), GetDate())

           when ‘{?DateRange} = ‘LastFullWeek’ then DateAdd(d, (-1 * DatePart(dw, GetDate())), GetDate())

          else GetDate()

        end)

      I can translate this to Oracle if that’s what you need.

      -Dell

      (1) 
  5. mustafa aboharb

    I have to add a comment in the sql command, but it taking forever to get back to save, is there a way that you can save it without having crystal go back to check the original code ?

    (0) 
    1. Dell Stinnett-Christy Post author

      No.  Crystal has no way of knowing what type of changes were made so it will verify the query for ALL changes made in the Command Editor, even if it’s just an added comment.

      -Dell

      (0) 
  6. Scott Carper

    I’m using Crystal Reports 2013.
    I have searched for a solution to my question but have not found one.
    I have 2 static parameters (P1 & P2).
    I want to create a dynamic parameter (P3) that uses the 2 static parameters to limit records listed in the dynamic parameter.

    I initially thought a Dynamic Cascading Parameter would work, but it forces the user to re-enter the first two parameters (P1 & P2).

    Then I looked at a Command, but a Command forces the parameter definition to be within the Command Editor, and the user would be forced to re-enter the first two parameters, just as with a Dynamic Cascading Parameter.

    I have used Crystal Reports before, but I’m definitely not an expert.

    Any suggestions?

    Scott

    (0) 
  7. Dell Stinnett-Christy Post author

    Try this:

    Use the Command for the fields for the Dynamic Cascading Parameter ONLY – do NOT use its fields anywhere else!  Create the two static parameters for the command only – do not create them in the Field Explorer. If you need them elsewhere in the report, you can use them after they’ve been defined for the parameter.

    After you’ve created the first two parameters in the Command and saved it, edit them in the Field Explorer to add any static values that you might need.  Then create the dynamic parameter using values from the command.

    Your report should now prompt for the two static params, then for the dynamic one.

    -Dell

    (0) 
  8. Ross Coker

    Dell,

    I’ve done a lot of research over the past 2 days regarding “Parameters that are used in a Command allowing multiple values to be set”.  I’ve got a Crystal Report which uses a Command for the entirety of the report and has 6 parameters provided via the “Modify Command” window.   2 of those 6 parameters are multiple value parameters

    Whenever we run the report and pick 0-All or any single value in either of those two parameter fields it works fine, but as soon as we enter more than one parameter in either of those two, it generates an error.  The Command WHERE clause seems to be correct as it relates to those two parameters:

    and (0 IN ({?Parameter1}) OR tablealias.field1 IN ({?Parameter1}))
    and (0 IN ({?Parameter2}) OR tablealias.field2 IN ({?Parameter2}))

    but whenever more than one value is used in either parameter within the “Enter Values” parameter screen and the report is run, it spits out a Failed to retrieve data from the database message box, with another notation of Incorrect syntax near ‘,’

    I’ve done a lot of research recently that implies older versions of Crystal Reports couldn’t handle the this sort of multiple value parameter situation, but your post didn’t seem to have any caveats to this situation, so I’m wondering if this is truly still an issue.  I’m using Crystal Reports 2013 SP 5 and am having this issue.   Many tech/help websites claim this is an issue but all of the posts similar to below are fairly dated:

    “The limitation is that CR Commands do not have a mechanism to allow for the use of multivalue parameters.  You cannot simply put in the command sql “table.fieldname IN ({?Parameter})” because the parameter is technically an array in crystal that is not comma delimited in a parsable string by a database server”.

    I’m hoping you can help me understand if this is still an issue with my version of CR or if there is something else going on that is causing my report to not be able to interpret multiple values even though they are set up as such in both the SQL and in the Command Parameter setup (via the “Allow multiple values” checkbox.

    Thanks,

    Ross

    (0) 
    1. Dell Stinnett-Christy Post author

      You need to remove the parentheses around the parameters so that it looks like this:

      and (0 IN {?Parameter1} OR tablealias.field1 IN {?Parameter1})
      and (0 IN {?Parameter2} OR tablealias.field2 IN {?Parameter2})

      Crystal will automatically add the parentheses with multi-value parameters.

      -Dell

       

      (1) 
      1. Ross Coker

        Thanks!  I had since found out I was using an outdated copy of the report.  The newer version of the report had made the exact corrections to that portion of the code, that you suggested.

        (0) 
  9. Pravin jayaram

    Hi — I am passing the multi values parameter form parent report as sql recognizable format.  It will be like ‘ABC’,’CDE’

    I am using this parameter in the child report command editor. Should I use it as network in [{?NETDS}]. Or without any braces?

    (0) 
    1. Dell Stinnett-Christy Post author

      So you’re sending this list of values as a single string?  I haven’t ever done this, but I would try it with parentheses.  Remember that in the command you’re using the syntax of the database not Crystal’s syntax.  If your database requires square brackets for “in” clauses, then use the square brackets. But most databases require that the list be between parentheses like ({?NETDS}).

      -Dell

      (0) 

Leave a Reply