Sales History Report in Crystal Report for Macola.

[cparralesl]

Hello There,

I hope someone can help me with this report in Macola.

I just built a sales history report Crystal Report for Macola. I'm using OBDC to get the tables and I got the tables OEHDRHST & OELINHST they are linked by Ord_Type, Ord_No & Inv_No. The select expert is by Inv_Dt Which is translated using the function NumberToDate(Inv_Dt) in order to get along with the parameter DateIni and DateFin that are date type.

The problem is that it takes too long to show the data in the report, my oelinhst table has almost 800,000 records and I have no idea how to increase the speed, is about 1 hour and a half the whole process to show data.

Any help would be appeciated,

Cesar Humberto Parrales
Application Support
Home : 00505-289-2484
Mobil: 00505-8856-016

 

[dgillz]

Cesar,
I assume you are on Pervasive and not MS SQL. Going to SQL cures all of your speed worries.

However I have a good workaround, in your record selection formula, add an indexed field such as Order_no to the selection. Lets say the first order of this year was 300000. Put a statement into your report to take advantage of the fact that Order_no is an indexed field as follows:

{OEORDHDR_SQL.Order_no} > 300000 and
NumberToDate({OEORDHDR_SQL.Invoice_dt) in {?Date range} and
...the rest of your seelction formula here.....

You should see a great improvement in performance .

Let me know if you have any questions.
Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[cparralesl]

Hi Don,

First of all,Thank you for help.

I tried to use the Ord_No field, which is string, and it seems like it's not working. Below is the SQL Query that Crystal build automatically.

SELECT
OEHDRHST_SQL_NN.`Ord_No`, OEHDRHST_SQL_NN.`Inv_Dt`, OEHDRHST_SQL_NN.`Curr_Cd`, OEHDRHST_SQL_NN.`Orig_Trx_Rt`,
OELINHST_SQL_NN.`Item_No`, OELINHST_SQL_NN.`Item_Desc_1`, OELINHST_SQL_NN.`Qty_To_Ship`, OELINHST_SQL_NN.`Unit_Price`, OELINHST_SQL_NN.`Discount_Pct`, OELINHST_SQL_NN.`Qty_Return_To_Stk`, OELINHST_SQL_NN.`Unit_Cost`, OELINHST_SQL_NN.`Unit_Weight`, OELINHST_SQL_NN.`Orig_Price`
FROM
`OEHDRHST_SQL` OEHDRHST_SQL_NN,
`OELINHST_SQL` OELINHST_SQL_NN
WHERE
OEHDRHST_SQL_NN.`Ord_Type` = OELINHST_SQL_NN.`Ord_Type` AND
OEHDRHST_SQL_NN.`Ord_No` = OELINHST_SQL_NN.`Ord_No` AND
OEHDRHST_SQL_NN.`Inv_No` = OELINHST_SQL_NN.`Inv_No` AND
OEHDRHST_SQL_NN.`Ord_No` > '0'
ORDER BY
OEHDRHST_SQL_NN.`Curr_Cd` ASC,
OELINHST_SQL_NN.`Item_No` ASC

In red is the Key field inserted.

I will appreciate you help about.

Cesar Humberto Parrales
Application Support
Home : 00505-289-2484
Mobil: 00505-8856-016

 

[cparralesl]

Don,

I forgot to tell that Crystal Report process the report using the time as fallow:

Accessing the databases is about 1 hour
Showing the data in the reporte 3 o 4 hours

As I said, I already include a key field. I really do not know what is going on?

Thanks inadvance.

Cesar Humberto Parrales
Application Support
Home : 00505-289-2484
Mobil: 00505-8856-016

 

[dgillz]

Humberto,

Order_no>0 does you no good, because ALL ORDER_NOs are greater than 0.

Pick a number that helps you take advantage of the indexed field. What order_no started out on Jan 1, 2002? Make your select statement greater than that number. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com