×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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

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

(OP)
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

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

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

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

(OP)
>>> 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

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

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

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

(OP)


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close