Parameter or Database Connection?

[SuperComputing]

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?

 

[Turkbear]

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.



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

 

[SuperComputing]

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?