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.
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.