×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Parameter or Database Connection?

Parameter or Database Connection?

Parameter or Database Connection?

(OP)
I have written a Crystal 10 report that duplicates the customer invoice feature in our accounting software. Our existing software allows reprinting invoices, but only one at a time.
Now, I am at the same wall with Crystal.
I have successfully joined two tables, one containing the invoice header data, with the table containing the line data. I have created a parameter field that allows for discrete and ranges and it will pull the invoice numbers that I ask for in the parameter prompt.

I need to print 1200 invoices. I currently have the invoice numbers to be printed in an Excel file, no particular range or order to them. I can find no way to "point" Crystal to the spreadsheet or to a text file and use it for my parameter "InvoiceNo". I have tried setting-up the spreadsheet as a data connection, I have tried creating a new table and importing the spreadsheet into it. Even with as little as 5 records, several minutes go by before I have to terminate the connection to get it to stop. I have tried 'linking' the tables in several different ways and have made sure my indexes are being used properly.

Is there a way to tell Crystal to use my list to select only the invoice numbers that I need printed?

RE: Parameter or Database Connection?

Assuming that your linking fields are correct and that your 'new' table of invoice numbers created from the spreadsheet is the leftmost table with a left outer join to the header table which then is linked to the line table,it should return the records you want- In your report you should group on the Invoice Number and place the line data in the details of that group.

Do not place any fields from the 'new' table in the report.

profile

To Paraphrase:"The Help you get is proportional to the Help you give.."

RE: Parameter or Database Connection?

(OP)
OK, I've downloaded 2011 30-day trial and re-created my report.
Chose New, Standard Report, Tables: SA_REPPRINT (5 test invoice numbers), SA_HDR (header info, 181K rows), SA_LIN_ITEM (line info, 524K rows) (named the column in my 'reprint' table the same as the indexed column of my invoice number in both other tables)
Link, took the default link suggestion of REPRINT ---> HDR, REPRINT ---> LIN on indexed invoice number only.
Chose minimum amount of fields, invoice number (from HDR table), customer number, item number, qty. Group by invoice number, no summary, no filter, finish.

Waited 5 minutes at max cpu on server for preview to display, disconnected session to stop.

Went back and changed link to suggested, REPRINT ---> HDR ---> LIN

Waited 5 minutes at max cpu on server for preview to display, disconnected session to stop.

Checked my SQL, SA_REPRINT isn't even used! (zkey0comp1 is the indexed column of my invoice number)

SELECT "SA_HDR"."Z_KEY_0_COMP_1", "SA_LIN_ITEM"."QTY", "SA_LIN_ITEM"."ITEM_NO", "SA_HDR"."CUST_NO", "SA_HDR"."CUST_NAM", "SA_LIN_ITEM"."DESC_LIN_1", "SA_LIN_ITEM"."DESC_LIN_2"
FROM "SA_HDR" "SA_HDR" INNER JOIN "SA_LIN_ITEM" "SA_LIN_ITEM" ON "SA_HDR"."Z_KEY_0_COMP_1"="SA_LIN_ITEM"."Z_KEY_0_COMP_1"
ORDER BY "SA_HDR"."Z_KEY_0_COMP_1"

Started over, linked as suggested, used invoice number out of the REPRINT table, grouped by reprint invoice, same fields as before.

Waited 1 minute 45 seconds for preview to display the 5 records. (projects out to 7 hours for 1200 invoices)

SELECT "SA_REPRINT"."Z_KEY_0_COMP_1", "SA_LIN_ITEM"."ITEM_NO", "SA_LIN_ITEM"."DESC_LIN_1", "SA_HDR"."CUST_NO", "SA_HDR"."CUST_NAM", "SA_LIN_ITEM"."DESC_LIN_2", "SA_LIN_ITEM"."QTY"
FROM ("SA_HDR" "SA_HDR" INNER JOIN "SA_REPRINT" "SA_REPRINT" ON "SA_HDR"."Z_KEY_0_COMP_1"="SA_REPRINT"."Z_KEY_0_COMP_1") INNER JOIN "SA_LIN_ITEM" "SA_LIN_ITEM" ON "SA_HDR"."Z_KEY_0_COMP_1"="SA_LIN_ITEM"."Z_KEY_0_COMP_1"
ORDER BY "SA_REPRINT"."Z_KEY_0_COMP_1"

SQL is still not as suggested, and I don't see a way to change it manually. How can I force it to be more efficient?

update: I went back to the links and joined 2 more indexes between the HDR and LIN tables, forced link order to REPRINT first, then invoice number, and got it down to 8 seconds. (projects out to 32 minutes) I can live with that.

Still don't understand why...

SELECT "SA_REPRINT"."Z_KEY_0_COMP_1", "SA_LIN_ITEM"."ITEM_NO", "SA_LIN_ITEM"."DESC_LIN_1", "SA_HDR"."CUST_NO", "SA_HDR"."CUST_NAM", "SA_LIN_ITEM"."DESC_LIN_2", "SA_LIN_ITEM"."QTY"
FROM ("SA_HDR" "SA_HDR" INNER JOIN "SA_REPRINT" "SA_REPRINT" ON "SA_HDR"."Z_KEY_0_COMP_1"="SA_REPRINT"."Z_KEY_0_COMP_1") INNER JOIN "SA_LIN_ITEM" "SA_LIN_ITEM" ON (("SA_HDR"."Z_KEY_0_COMP_1"="SA_LIN_ITEM"."Z_KEY_0_COMP_1") AND ("SA_HDR"."Z_KEY_0_COMP_2"="SA_LIN_ITEM"."Z_KEY_0_COMP_2")) AND ("SA_HDR"."Z_KEY_0_COMP_0"="SA_LIN_ITEM"."Z_KEY_0_COMP_0")
ORDER BY "SA_REPRINT"."Z_KEY_0_COMP_1"

Thank you for your help. Is there anything else that I can do?

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