Best Practices When Using Commands with Crystal Reports


In model 9 and newer Crystal has a characteristic referred to as a “Command”.  A Command is a SQL Choose assertion that the consumer can write to extract information for a report.  Instructions can be found for many, however not all, databases.  To find out whether or not a Command might be created for a particular sort of connection, open the connection in Crystal; the primary line below the title of the open connection will likely be “Add Command”.

Instructions use the SQL syntax of the database that’s being related to. So, for instance, when connecting to MS SQL Server, the “getDate()” perform is used to get the present date however in Oracle it could be “SysDate”.  Crystal formulation can not be utilized in a command, so any calculations or filters should use SQL syntax.

What follows are some finest practices for working with Instructions in Crystal.

A Command ought to embody ALL of the information required in a report.

When a Command is linked within the Database Skilled with different tables or Instructions, Crystal is unable to push the joins to the database for processing. As an alternative, the entire information from the Command together with the entire information from the tables is pulled into reminiscence the place Crystal will course of the take part reminiscence.  With interior joins, this steadily means that rather more information is introduced into reminiscence than will really be used on the report.  As a consequence, becoming a member of Instructions to tables or different Instructions can considerably decelerate report processing

Any information filtering MUST be executed within the The place clause of the Command.

See also  Xcode Not Showing Microphone Access Alert For Iphonex Simulator

When the Choose Skilled is used towards a command, Crystal is as soon as once more unable to push the filter right down to the database and as a substitute pulls the entire information into reminiscence and processes the filter there.  Identical to when a Command is joined to tables or different Instructions, this will considerably decelerate report processing.

SQL Expressions.
It isn’t potential to create or use SQL Expressions within the Subject Explorer for the report when utilizing a Command. As an alternative, the syntax of the SQL Expression  ought to be used within the Command itself as both an extra “area” within the Choose clause or as a part of the The place clause.


1.  Parameters which are utilized in a Command MUST be created within the Command Editor.  The Command Editor can not “see” parameters which are created within the Subject Explorer for the report.  Nonetheless, within the Command Editor solely the Parameter Identify, Prompting Textual content, Worth Kind, Default Worth, and whether or not the immediate permits a number of values might be set.

Additionally, if a parameter is created however not used within the Command Editor, the parameter won’t really be created for the report.  Nonetheless, as soon as a parameter is created within the Command Editor and utilized in a Command, it may be modified within the Parameters part of the Subject Explorer for the report. On this method the parameter might be made dynamic and nearly some other properties might be set.

2.  When a parameter is a string worth, it have to be surrounded by single quotes within the Command to ensure that it to work.  When a parameter is multi-select, use “in” as a substitute of “=”.  So, a the place clause would possibly appear to be this:

See also  How To Glue 3D Printed Parts To Wood

The place MyTable.StringField = {?String Param}

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

and MyTable.DateField = {?Date Param}

3. Parameters which are utilized in Instructions CANNOT be elective.   As an alternative, I usually use a default of “*All” (the asterisk causes this to kind to the highest in dynamic parameters) to point that each one values ought to be chosen.  Within the The place clause, it could be used one thing like this:

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

4. DO NOT use the Command that gives information for the report to supply information for dynamic parameters.  The parameters won’t present any area values till after the Command has been run. As an alternative, do one of many following:

a.  If the report is being run by BusinessObjects (BOBJ) or Crystal Experiences Server (CRS), use the Enterprise View Supervisor that’s put in with the Shopper Instruments to create the Listing of Values that may present information to a dynamic parameter.  On the whole, ALL dynamic parameters for studies which are operating in BOBJ or CRS ought to be created and managed within the Enterprise View Supervisor – it’s extra environment friendly and permits for parameters to be reused as a substitute of recreating them for each report.  Sooner or later sooner or later I plan to submit a weblog that walks by how to do that.

b.  If the report is just not being run by BOBJ or CRS, then do the next:

      • Within the Database Skilled, add a Desk or Command
        that may present the information for the parameter.
      • DO NOT hyperlink this Desk or Command to something.  Crystal will throw a warning that that is usually not supported.  This warning might be ignored as a result of such a configuration is supported for this objective.
      • Use fields from the brand new Desk or Command within the
        parameter configuration to supply information for the parameter.
      • DO NOT use fields from the brand new Desk or Command
        anyplace else within the report.
See also  These are the Best Galaxy Z Fold 3 Chargers in 2021

When these finest practices are adopted, Instructions can grow to be a really highly effective device to supply information in Crystal Experiences.  Particularly since there are issues that may be executed in a Command that can not be executed when linking tables in Crystal.  For instance, it’s potential to filter dated information to indicate solely the latest date, use a “Having” clause to indicate solely information that meets a sure abstract threshold, work with “Union” queries, and even pivot information in order that it may be proven as if it had been a cross-tab with out really utilizing a cross-tab.  Absolutely anything that may be executed in a SQL question in a database might be executed in a Command, which makes it a strong device for reporting.

NOTE : Please do not copy -

Leave a Reply

Situs Judi Slot Online Terpercaya

Link Slot Gacor

Slot Anti Rungkat

Slot Online Terpercaya