Report Performance/CR 9

[mwhager]

I am using CR 9, connecting to DB using ODBC data source. I am using an SQL command as my datasource. The problem is the length of time it is taking to run the report. I guess the SQL statement or something is not done very efficiently. The information returned is correct, but it takes a while for the report to generate. This is a sales commission report. The SQL statement used is below: SELECT
OPCSAHF."INV_NUMB",
OPCSAHF."INV_LINE_NUMB",
OPCSAHF."CUST_CODE",
OPCSAHF."ITEM_CODE",
OPCSAHF."SALESREP_CODE",
OPCSAHF."DATE_CUST_INVOICE",
OPCSAHF."QTY_SALES",
OPCSAHF."SALES_VAL",
OPCSAHF."PKGS_SALES",
INCINV1."ITEM_DESC",
KKCINAD."RANDOM_WGHT_ITEM",
KKCINAD."CASE_WEIGHT",
KKCINAD."SLS_COST_PRD_MGR",
OPCSAMS."SALESREP_NAME",
KKLCOMM1."SALESREP_CODE",
KKLCOMM1."SLS_COST_PRD_MGR",
KKLCOMM1."COMM_IND",
KKLCOMM1."COMM_AMT",
KKCBOMU."MULTIPLIER",
OPCCSMS."CUST_NAME",
OPCCSMS."CUST_ADDR_L1",
OPCCSMS."CUST_ADDR_L2",
OPCCSMS."CUST_ADDR_L3",
OPCCSMS."CUST_STATE"
FROM
{oj ((((("OPCSAHF" OPCSAHF INNER JOIN "INCINV1" INCINV1 ON OPCSAHF."ITEM_CODE" = INCINV1."ITEM_CODE&quot
INNER JOIN "OPCSAMS" OPCSAMS ON OPCSAHF."SALESREP_CODE" = OPCSAMS."SALESREP_CODE&quot
INNER JOIN "OPCCSMS" OPCCSMS ON OPCSAHF."CUST_CODE" = OPCCSMS."CUST_CODE&quot
INNER JOIN "KKCINAD" KKCINAD ON INCINV1."ITEM_CODE" = KKCINAD."ITEM_CODE&quot
INNER JOIN "KKLCOMM1" KKLCOMM1 ON OPCSAMS."SALESREP_CODE" = KKLCOMM1."SALESREP_CODE&quot
INNER JOIN "KKCBOMU" KKCBOMU ON KKCINAD."ITEM_CODE" = KKCBOMU."ITEM_CODE"}

Here is the select statement I am using:
{Command.SALESREP_CODE}={?Sales Rep} and
{Command.DATE_CUST_INVOICE} in {?Date Range}

I suspect the one to many relationship between OPCSAHF and KKLCOMM1 maybe slowing things down. Any suggestion on improving the performance would be appreciated.

Many thanks.

 

[FVTrainer]

It looks like you're not including the WHERE clause in your command object, but instead are using the record selection formula in Crystal. Is that correct? If so, after running the report in Crystal, go to Database | Show SQL. Do you see a WHERE clause in the sql shown there. If not, you may be returning all records to the client machine and then doing the filtering there. If you are not including the "select" statement you show (i.e., the WHERE clause) in the command object, edit your command object to include it and see if that helps.

 

[mwhager]

you're right, I have no WHERE clause in my command object. The Select statement in from the Crystal Select Records formula. So how can you put those parameters in the SQL command object using a WHERE clause?

 

[FVTrainer]

First, edit the command object. On the right-hand side of the Modify Command window you'll see a Create button. You use this button to create the parameters you need ("Sales Rep" and "Date Range&quot. The, at the bottom of you SQL Command, enter the following text:

WHERE
KKLCOMM1."SALESREP_CODE" =

with the cursor to the right of the "=" sign, double-click on the Sales Rep parameter.

Then add:

AND OPCSAHF."DATE_CUST_INVOICE" in

and double-click on the Date Range parameter to add it to the WHERE clause.