Link to home
Start Free TrialLog in
Avatar of mroberson
mroberson

asked on

Using Command Objects in CR

I have been experimenting with Command Objects in my CR but the simple SQL queries that I have been using are slowing down the report to the point to where it is unuseable. Any solutions or suggestions to this?  
Avatar of Mike McCracken
Mike McCracken

Can you build the queries in the database?

WHat is the command you are using?

mlmcc
Avatar of mroberson

ASKER

The query I was experimenting with was
SELECT coreid
FROM coretable

and

SELECT namefirst
FROM nametable
WHERE nametable.nametype = 'a'

In each attemp with either query the report was to slow to use
UNless those return a large number of records they shouldn't take long to run.

mlmcc
This question is not abandoned I am terribly busy with my job & just do not have the time to deal with the problems I currently have open in expert exchange Please do not delete the question.
If there are no comments for 21 days, the question is considered abandoned.  If you wish to keep the questiion alive then make a comment every 10-15 days.

mlmcc
I am wondering If one can use command objects in place of temp tables or views ....
WHen you say command objects, are you referring to the Crystal Command capability or something else?

mlmcc
an sql command  which is used as a virtual table by the crystal report

I don't think Crystal can do that.

mlmcc
Defining a SQL Command
If the database you are using supports a query language such as SQL, you can write your own command which will be represented in Crystal Reports as a Table object. This allows database users complete control of the data processing that gets pushed down to the database server. A user who has experience with databases and the SQL language can write a highly optimized command that can considerably reduce the size of the set of data returned from the server.

You can write your own command by using the Add Command node in the Database Expert to create a virtual table which will represent the results of processing the command. Once the command has been created, you can store it in the Crystal Repository so it can be shared between many users.

To create a command object
In the Database Expert, expand the Create New Connection folder and browse the subfolders to locate your data source.
Under your data source, double-click the Add Command node.
In the Add Command to Report dialog box, enter an appropriate query/command for the data source you have opened.
For example:

SELECT


    Customer.`Customer ID`,


    Customer.`Customer Name`,


    Customer.`Last Year's Sales`,


    Customer.`Region`,


    Customer.`Country`,


    Orders.`Order Amount`,


    Orders.`Customer ID`,


    Orders.`Order Date`


FROM


    Customer Customer INNER JOIN Orders Orders ON


        Customer.`Customer ID` = Orders.`Customer ID`


WHERE


    (Customer.`Country` = 'USA' OR


    Customer.`Country` = 'Canada') AND


    Customer.`Last Year's Sales` < 10000.


ORDER BY


    Customer.`Country` ASC,


    Customer.`Region` ASC


Note:    The use of double or single quotes (and other SQL syntax) is determined by the database driver used by your report.

Create a parameter for your command by clicking Create and entering information in the Command Parameter dialog box.
Click OK.
You return to the Report Designer. In the Field Explorer, under Database Fields, a Command table appears listing the database fields you specified.

Note:    By default, your command is called Command. You can change its alias by clicking F2.

To edit a command object
In the Selected Tables area of the Database Expert, select the command you want to edit.
Right-click the command and select Edit Command from the shortcut menu.
Make the changes you want in the Modify Command dialog box and click OK when you're done.
To create a parameter for a command object
You can create a parameter field while working in the Add Command to Report dialog box or the Modify Command dialog box.

In the Parameter List area of the appropriate command dialog box, click Create.
In the Command Parameter dialog box, enter the following information in the fields provided:
Parameter Name
Enter the name you want to identify your parameter by.

Prompting Text
Enter the text you want to appear when the program prompts you.

Value Type
Enter the data type of the parameter field.

Default Value
Enter the value you want the program to use if you do not supply a new value.

Click OK.
Your parameter is added to the Parameter List. You can modify or delete it by returning to the Modify Command dialog box.

the previous comment is right out of the help files I can get it to work with a simple query  but not even close to the sample .... I am wondering if the correct syntax is like the example where keywords are on one line & data fields are on another ....
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial