Create Crystal Report with Excel Data and Upload to LBI

My first response would be why would you want to do this if you can just connect to the LBP database, but assuming youve already gone through this discussion...

A file DSN connection to your Excel file should be sufficient. You will need to create that connection on the server as well as the machine of the report developer. You may have to make sure WebSphere and/or the Crystal Report Server is running under a Network Account that has access to the file, otherwise you will get a looping login prompt.

Beyond that, you just need to make sure the Excel file is formatted appropriately to be consumed by Crystal.

hth
Matt

Hi Matthew,

Thanks for the response. I am not quite sure how Crystal would recognize the fields from an Excel file. What format would the file have to be formatted in order for Crystal to recognize the fields from the Excel Spreadsheet? We use Access 2010 and Excel 2010 here and I am thinkiing if I create a link in Access to the Excel Spreadsheet and pull the data from the Access table. I think I may be more comfortable doing it this way rather than trying to do it from Excel.

Do you know if I can create a DSN connection from Access 2010 to Crystal 2008 R2?

Thanks,
MT.

its been a while since Ive done this but as I remember, Crystal will recognize each sheet as a table, columns as fields and rows as transactions.

Seems like you would be creating a bit of a monster by creating a Crystal Report thats connected to an Access Database thats connected to an Excel spreadsheet with data thats extracted from LBP. That gives you no less than 4 points of failure or data corruption.

Again, if direct access to LBP really isnt an option I would suggest you stay with a File DSN to an Excel or CSV file.

Thanks Matthew,

We do not have live payroll data in LBP at this time. The payroll data is being extracted from an ADP report to a spreadsheet. I was exploring excel's setting-up a new data source option and noticed that once a DSN connection is established excel recognizes the header row as the Field Column. I had never used excel as a data source before and was kind of fuzzy how excel would know what the header row is? This is why I thought of linking the excel spreadsheet to an Access table and retrieving the data from Access.

I have not yet figured out the best way to approach it but I now atleast know that excel can be used if necessary as a data source.
thanks for the suggestions.

MT.

Hi - I used to teach Excel and typically when you want Excel to recognize something as a table for sorting or filtering, you need to change the formatting on the header row by making it bold, italics or underline to make it recognize that row as the header row. Give that a whirl. I hope that helps. Louise

Thanks Louise for the tip.