Sorting in Crystal via Excel 1

[gbut44]

I am a newbie to Crystal. Using Crystal 10. Created a report (Open Items by Job)from MAS90 listing open items grouped by Job#. I have an Excel spreadsheet listing Job numbers with the dates they are scheduled to ship. I would like to be able to sort my "Open Items" report by the ship dates contained in my spreadsheet. The ship dates on the spreadsheet are constantly changing(almost daily). The Job# field in the report is a text string (7 chr)usually with 3 leading zeros. The Job# in the spreadsheet is a number with no leading or trailing zeros. Any ideas?? I am currently taking an online course for Crystal 10 in hopes of becoming better at creating more complex reports. Until then, I would appreciate any & all help. Thanks!

 

[Madawc]

Are you asking how to read an Excel spreadsheet into Crystal? Use SEARCH on Forum 3.

If not, what are you asking? What have you tried? Dates may need Cdate to get them accepted. Numbers convert with ToNumber

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[gbut44]

Madawc,
Is there any way I can attach the report so you can look at it? I don't know if that is possible or not. I'll try to give you as much info as I can. Open Item by Job Number Report - "Select Expert" {PO1_PurchaseOrderEntryHeader.OrderStatus} in ["B", "C", "N", "O"] and
({PO2_PurchaseOrderEntryLine.QtyReceived} < {PO2_PurchaseOrderEntryLine.QtyOrdered}) and
{PO2_PurchaseOrderEntryLine.JobNumber} = {?By Job Number}
"Group Expert"
Group by: PO2_PurchaseOrderEntryLine.JobNumber-A
"Record Sort Expert"
Sort Fields: Group#1O2_PurchaseOrderEntryLine.JobNumber-A
A-PO2_PurchaseOrderEntryLine.LinkToPreviousLine
The report as it stands now works just like I need it too. I enter a Job# or a range of Job#'s & it shows me all the items I have on order that have not been received yet for each of the jobs. It sorts by ascending job number. The shipping manager has an Excel spreadsheet with all the Job#'s listed by ship date.(Column A-Job#, Col B-State, Col C-Customer, Col D-City, Col E-Depart Date, Col F-Arrive Date, Col G-Arrive Time)
What I would like for Crystal to do is run like it currently does, then be able to sort by Job# in the order that is in the Excel spreadsheet (which is Job# by Ship Date, in which the order by ship dates changes constantly) if possible. I hope I have explained it better this time. I appreciate the time you've spent on this for me. I look forward to hearing from you. Thanks again!

 

[lbass]

Sounds like you need to add a group by ship date, and then make that your group #1, with job # your group #2. You can either drag the ship date group into the #1 position in design mode, by dragging the GH#2 area to the left of the report into the GH#1 position, or go into the group expert and use the arrow keys to change the group order.

-LB

 

[gbut44]

LB, Remember, I said I am a newbie. I tried what you said, but to no avail. These are the steps I took.
1) Database Expert - Create New Connection - Choose Access/Excel (DAO)
2) Access/Excel (DAO) - Connection - Open, File Name: (I chose Excel Spreadsheet titled "Shipping Report for Crystal") - Files of type: Data Files
Database Name: C:\Documents......Shipping Report For Crystal
Database Type: Excel 8.0
Selected Tables: Sheet1_
Received message: Visual Linking - "Your current link configuration contains multiple starting points. Please be advised that this is generally not supported" - Hit OK
Received message: Database Warning - "More than one database driver has been used in this report. If you want to change the database drivers use Database/Set Location. Please make sure that no SQL Expression is added and no server-side group-by is performed."
In Group Expert I selected "Sheet1_Ship Date - A" I then placed that file first in order and placeed "PO2_PurchaseOrderEntryLine.JobNumber - A" second.
When I refreshed the report, instead of showing 4 items grouped by Job# 0001627 as it did originally, it showed a list of dates in the left window, and in the main window it not only listed the original 4 items, it listed the same items only duplicated 4 times each by the same job#, for each date listed in the side window. HELP?!?!?!

 

[lbass]

You should be linking the two tables on job #. Go to database->database expert->links and create the link.

-LB

 

[gbut44]

LB, When I attampt this it gives me a message that says "Data types are not compatible"