Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Performance Management
Lawson Business Intelligence/Reporting/Crystal
Create Crystal Report with Excel Data and Upload to LBI
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
Troy J
Past 24 Hours:
1
Prev. 24 Hours:
1
Overall:
5189
People Online:
Visitors:
311
Members:
0
Total:
311
Online Now:
New Topics
S3 Customization/Development
Cobol - Extract Current Time
4/24/2024 7:21 PM
How do you extract just the Current System Time in
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
IPA/ProcessFlow
IPA executing Job
3/13/2024 7:08 PM
New to the IPA world and was wondering, can an IPA
Lawson S3 HR/Payroll/Benefits
Life Age Reduction on benefits plans
3/12/2024 7:15 PM
For our optional life we have an age based coverag
Lawson S3 HR/Payroll/Benefits
BN53.1 Add-In
3/7/2024 3:31 PM
We are migrating to Solstice. They require a
Lawson Business Intelligence/Reporting/Crystal
Domain Name Change
3/5/2024 7:45 PM
Our domain name needs to change and was hoping I c
S3 Customization/Development
Cobol calling Shell Script
2/29/2024 1:27 PM
Has anyone created or modified a Lawson Cobol prog
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1351
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
Lawson Business Intelligence/Reporting/Crystal
Create Crystal Report with Excel Data and Upload to LBI
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
imty
Advanced Member
Posts: 35
9/4/2012 10:15 PM
Hello Group,
I'd like to know if it is possible to Create a Crystal Report that retrives data from an excel spreadsheet via an ODBC connection. Publish the Crytal report into LBI and allow users' access based on their authorization.
This data is actually LBP (Lawson Budget Planning) data that is exported to an excel spreadsheet.
Any suggestions is much appreicated.
Please see attachment of sample data.
thanks,
Imtiaz.
Attachments
Sample_Payroll_Data.txt
Matthew Nye
Veteran Member
Posts: 514
9/5/2012 7:09 AM
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
imty
Advanced Member
Posts: 35
9/5/2012 3:50 PM
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.
Matthew Nye
Veteran Member
Posts: 514
9/6/2012 8:56 AM
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.
imty
Advanced Member
Posts: 35
9/6/2012 4:24 PM
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.
Louise I
Basic Member
Posts: 4
9/6/2012 5:07 PM
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
imty
Advanced Member
Posts: 35
9/6/2012 5:13 PM
Thanks Louise for the tip.
You are not authorized to post a reply.