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.

31 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) 
  10. Rayna Curtis

    Hi Dell,

    I am having a struggle with a Crystal where the command is in a subreport.  At the end of a report I have built showing products the client bought, I am then trying to list all items that did not appear on the top part of the report…ie all items that the client could have purchased but did not.

    So…I am handing in a parameter for the Purchase ID from the main report, and selecting all items in the “purchased” table in the subreport.  Then I am saying (in a Command) give me all the items in the Product table that aren’t in the Purchased table.  I created the parameter for the Purchase ID, and put it in the Command.  I then hand in the Purchase ID from the top level…and I match them up in the SQL Select.  But the Crystal still insists on prompting for both the original Purchase ID as well as the second one I built for the command.  Do you have any ideas what I might be doing incorrectly?  I would appreciate any guidance!

    -Rayna

     

    (0) 
    1. Dell Stinnett-Christy Post author

      Have you linked the two prompts?  To do this, right-click on the subreport in the main report and choose  “Change Subreport Links…”.  Under “Available Fields” on the top left, select the Purchase ID from the main report and move it to “Field(s) to link to” at the top right.  Uncheck “Select data in subreport based on field” on the bottom right.  On the bottom left, select the subreport parameter under “Subreport parameter field to use:”

      This will link things together so that the parameter in the subreport will get its value from the main report.

      -Dell

      (0) 
      1. Rayna Curtis

        You’re a genius!!  I have been working with Crystal Reports for more than a decade, and I don’t believe I have ever touched that lower left hand field…I only ever selected the lower right hand one, to join the left one and the right one!

        Thank you ever so much!

         

        (0) 
  11. Alison Goricanec

    Hi Dell,

     

    I’ve been using Crystal since the mid ’90s but have only started working seriously with the command in Crystal in the past few years.

    My issue is, I need to add two dynamic cascading parameters to my where clause in the command, based on fields in the database (Region – Postcode).  So depending on which Region(s) you pick, you get a subset of all the postcodes in that selection – plus the ‘All’ option.

    The report also has to be able to select ‘All’ Regions.

    My command is made up of 2 main Select statements – One creates a one-line summary per Region. and one is a Total level one line summary.

    The reason for this is that I need to do a distinct count of a ‘PersonNumber’ field that can be replicated across a few Regions.  I need it to count once per Region that its in, and once for the Total.

    I have already put my date range param into the Where clause, but I am stuck when it comes to the rest.

    I have a static table that holds the values for the Region/Postcode, and these need to be able to select ‘All’ as a value.

    I also need to add a couple of other list based parameters from the database code fields.

    Would love your assistance with this.

    Thanks 🙂

    (0) 
    1. Alison Goricanec

      It wouldnt let me edit so….

      Just a thought – Can I create the parameter in Crystal and send the result back to the Command?  Along the lines of {?Command Param} = {?CrystalParam Multivalue Selection}

      Where the crystal Parameter is populated with an unlinked table (EG RegionMapping) and the result is sent back?

       

      (0) 
      1. Dell Stinnett-Christy Post author

        As I note above, you’ll have to create the parameters in the Command Editor in order to be able to use them in your command.  I would add something like the following command to your report to provided data to your dynamic prompt (assuming SQL Server back-end):

        Select ‘*All’ as RegionCode, ‘*All’ as PostalCode”

        UNION ALL

        Select RegionCode, PostalCode from Regions

        Order by RegionCode, PostalCode

        Do not link this to your other command, use it just for your dynamic prompt.  I use “*All” because it will always sort to the top.

        In your other command, create the prompt for Postal Code and add something like the following to your where clause:

        and ({?PostalCode} = ‘*All’ or MyTable.PostalCode in {?PostalCode})

        Save your command.  In the Field Editor, edit the {?PostalCode} parameter, make it dynamic, and use the new command to make it a cascading prompt.

        -Dell

        (0) 
  12. David Beayon

    Hi Dell,

    I have a report that I created in Crystal Reports 2016 that pulls data based on a begin and end date parameter. The report runs as expected when initialed from Crystal.  I get prompted for the dates.

    Here is the parameter section:

    AND m.OrdDat BETWEEN {?BeginDate} AND {?EndDate}

    However, when I import this report into our  McKesson HomeCare application, I get prompted for the parameters, but the report returns blank.  If I upload the same report without any parameters, the data displays correctly.   If I upload a report with the dates hardcoded

    and m.OrdDat BETWEEN ‘20180201’ and ‘20180228’

    I get the expected results .from the application.  Could this be a compatibility issue with the application?  There are some custom reports that were generated in a previous version of CR that do work within the application fine.  I dug a little deeper and found that if I look at the SQL Query of the older report, the parameters portion of the code looks like this:

    WHERE (“DocVstCtn”.”DatIn”>={ts ‘2018-02-01 00:00:00’} AND “DocVstCtn”.”DatIn”<{ts ‘2018-03-01 00:00:00’})

    And in CR 2016 Show SQL Query, I still get the parameter name, but not the parameter value.

     

    m.OrdDat BETWEEN {?BeginDate} AND {?EndDate}

    Any thoughts or insight would be immensely appreciated.

    -David

    (0) 
    1. Dell Stinnett-Christy Post author

      Hi David,

      When you show the SQL Query in the new report, have you entered the date parameters yet?  If not, run the report so that you have to enter them and then see what the query looks like.  Those parameters won’t have any values unless you enter them before looking at the query.

      I vaguely recall reading a question on the forums about dates being handled slightly differently, but I’m not sure where and which version that was talking about.  I suggest asking this question and setting the primary tag to “SAP Crystal Reports”  so we can see what Don Williams from SAP might have to say on the subject.

      -Dell

      (0) 
  13. Kevin Price

    Hi Dell,

    I’m working on a Crystal report using SQL Server and I’m using a Command to query a view since the dataset is huge. I was able to add a parameter in the command for a date, but the data set was still over 12 million records. I need to filter by some of the other required parameters, which are currently field parameters, and they are all a list of static strings that allow for multiple selects. When I added these parameters to the SQL command it seems like they are only seeing the first value and not the other selected values for each parameter using the syntax :

    AND ([PlantWhse.Plant] in {?Plant}) AND ([PlantWhse.WarehouseCode] in {?Warehouse})

    If I wrap either one of these in () such as ({?Plant}) I get a SQL error about not being able to identify ‘,’ and no records are returned. I need to get something like AND ([PlantWhse.Plant]  in (‘Plant01’, ‘Plant02’) etc.  I can get the SQL query to return records with this criteria, but I don’t think the Command parameter is functioning that same way. Any advice?

     

    thanks,

    Kevin

    (0) 
    1. Dell Stinnett-Christy Post author

      Hi Kevin,

      What you have looks like it should work.  Can you post a screen shot of how the parameters are set up – not the definition in the Command Editor, but the one in the Field Explorer.

      Thanks!

      -Dell

      (0) 
      1. Dell Stinnett-Christy Post author

        Also, if you look at “Show SQL Query” on the database menu after you’ve set the parameters and run the report, it should show you how it is passing the parameters to the report.

         

        (0) 
        1. Kevin Price

          I did the show SQL query and it just shows this

          Where ( [PartTran.TranDate] <= {?AsofDate}) AND ([PlantWhse.Plant] in {?Plant}) AND ([Part.ClassID] in {?Part Class})

          It turns out the issue I had was with the parameter itself. The warehouse codes were used in the report as a left(4) of the actual code, so the SQL query couldn’t do that in a where clause for a list of strings.

          The {?Plant} and {?Part Class} are both multi-select strings that do work in the WHERE clause of the command. This blog was a huge help.

          Thank you

          (0) 

Leave a Reply