Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Report Performance/CR 9

Status
Not open for further replies.

mwhager

IS-IT--Management
Jun 8, 2000
27
US
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")
INNER JOIN "OPCSAMS" OPCSAMS ON OPCSAHF."SALESREP_CODE" = OPCSAMS."SALESREP_CODE")
INNER JOIN "OPCCSMS" OPCCSMS ON OPCSAHF."CUST_CODE" = OPCCSMS."CUST_CODE")
INNER JOIN "KKCINAD" KKCINAD ON INCINV1."ITEM_CODE" = KKCINAD."ITEM_CODE")
INNER JOIN "KKLCOMM1" KKLCOMM1 ON OPCSAMS."SALESREP_CODE" = KKLCOMM1."SALESREP_CODE")
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.
 
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.
 
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?
 
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"). 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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top