What to do? Crystal Report tables don't have common indexes - slow

[bordway]

Hi,

Hoping someone can help me find better approach to a custom report.
I found detail data that would work but.... in three tables without common indexes the report is so slow - it's not useable.

Below is the source data I've been using - to get invoices and any payments.

Rpt Name.......From Table......From Field
Name...........ARHistHead......BillToName
CustID.........ARHistHead......CustomerNo
Address........ARHistHead......BillToAddress
City...........ARHistHead......BillToCity
State..........ARHistHead......BillToState
Zip............ARHistHead......BillToZipCode
InvoiceNo......ARHistHead......InvoiceNo
InvoiceType....ARHistHead......InvoiveType
TermsCode......ARHistHead......TermsCode
TermsCodeDesc..ARTerms.........TermsDescription
InvoiceDate....ARHistHead......InvoiceDate
TranDate.......ARHistHead......TransactionDate
InvcAmt........ARHistHead......TaxableSalesAmt + NonTaxableSalesAmt+ FreightAmt + SalesTaxAmt
OpenAmt........ARCashRcpt......InvoiceBalance
PayDate........ARCashRcpt......PostingDate
PayType........ARCashRcpt......TransactionType
PayAmt.........ARCashRcpt......CashAmountApplied
DiscAmt........ARCashRcpt......DiscountAmountApplied

I've tried the report Wizard and ODBC, but as soon as I start joining the tables it gets slow like a glacier.
Just to get by, I've been dumping the tables individually and building a temporary report inside MS Access.

Thanks in advance for any ideas...

bordway


Bruce O

 

[LouieGrandie]

Maybe a few more details. What version are you on? What are you trying to do with the report. Which tables are you currently using.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[bordway]

Hi,

>> some details....
Here is everything I can think of.
Thanks in advance for your help.

Version: Sage MAS 200
Tables: AR_InvoiceHistoryHeader (as ARH), AR_CashReceiptsHistory (as ARC) and AR_Terms (as ART)

This is an MS Access Query that should pull the data I need but... it is too slow:

SELECT
ARH.BillToName AS Name,
ARH.CustomerNo AS CustID,
ARH.BillToAddress1 AS Address,
ARH.BillToCity AS City,
ARH.BillToState AS State,
ARH.BillToZipCode AS Zip,
ARH.InvoiceNo,
ARH.InvoiceType,
ARH.TermsCode,
ART.TermsCodeDesc,
ARH.InvoiceDate,
ARH.TransactionDate AS TranDate,
ARH!TaxableSalesAmt+ARH!NonTaxableSalesAmt+ARH!FreightAmt+ARH!SalesTaxAmt AS InvcAmt,
ARC.InvoiceBalance AS OpenAmt,
ARC.PostingDate AS PayDate,
ARC.TransactionType AS PayType,
ARC.CashAmountApplied AS PayAmt,
ARC.DiscountAmountApplied AS DiscAmt

FROM (ARH LEFT JOIN ARC ON (ARH.InvoiceNo = ARC.InvoiceNo)
AND (ARH.CustomerNo = ARC.CustomerNo)
AND (ARH.ARDivisionNo = ARC.ARDivisionNo))
LEFT JOIN ART ON ARH.TermsCode = ART.TermsCode
ORDER BY ARH.BillToName, ARH.CustomerNo, ARH.InvoiceNo, ARC.PostingDate;

Users want summary reports formatted for Excel data only export.
I have previously made Crystal reports using ODBC or the built in report "Wizard".

But... I'm seeing extreme speed issues when joining tables AR_InvoiceHistoryHeader and AR_CashReceiptsHistory.
So to get by, I have been pulling raw data from individual tables and combining manually.
I suspect my problem is indexes.
Mu only ideas right now are to use a subreports.
Also I was wondering what "chaining" does in MAS & if that might be an option that could help?

Bruce O

 

[LouieGrandie]

If you are using MS Access then you should use SQL Specfic Pass Through Queries, one for each table, then base the Crystal report on those queries. I think you will see an increase in speed.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[bordway]

>>> If you are using MS Access...
MS Access is just the temporary band-ade.

I want to add a Crystal report on the MAS menu


Bruce O

 

[LouieGrandie]

You can. The key is that you before you do you have to go into the report, then File, Summary Info and in the Keywords field enter in "Converted to version 5.00" or whatever version you are on, without the quote marks. When attempting to run the report from a menu in MAS the Crystal run time will check that field to see if the report needs to be converted. When you paste in "converted to version ?.??" the run time engine will go ahead and run the report. Most people don't do this and as a result it tries to convert using the SOTAMAS90 driver it errors out as data source not found.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[bordway]

Nice to have something different to try.
I've been sitting on a couple of these & I'm ready to get them out of my queue.

Will report the results.

Thanks

Bruce O