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
Fields restricted? Excel pull of Lawson data through OLEDB
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
Wojo
Past 24 Hours:
1
Prev. 24 Hours:
0
Overall:
5265
People Online:
Visitors:
390
Members:
0
Total:
390
Online Now:
New Topics
Lawson S3 Procurement
Tolerance Settings
3/31/2025 2:01 PM
I've been trying to set a tolerance for some t
Dealing with Lawson / Infor
Printing Solutions other than MHC
3/27/2025 1:00 PM
What are others using for printing solutions besid
Lawson S3 Procurement
Green check marks in Lawson 9.0.1
3/20/2025 4:55 PM
Hi, How to remove green check mark on items when o
Lawson S3 HR/Payroll/Benefits
Pay Rate History to Show All Positions
2/26/2025 3:34 PM
Does anyone know how to modify payratehistory.htm
Infor CloudSuite
How to build a Pre-Prod tenant
2/7/2025 1:28 AM
After we finished our implementation and ended our
Lawson S3 Procurement
Browser issue with RQC Shopping
1/28/2025 5:49 PM
Since the recent Chrome/Edge updates, our RQC shop
Lawson S3 Procurement
S3-Procurement New Company
1/22/2025 10:38 PM
My Accounting Department has created a new Company
S3 Customization/Development
JUSTIFIED RIGHT
1/15/2025 7:41 PM
Is there a way in Lawson COBOL to make a character
S3 Systems Administration
ADFS certificate - new cert
12/3/2024 9:38 PM
The certificates on the windows boxes expired and
Lawson S3 HR/Payroll/Benefits
Post Tax Benefit Plan Table
11/14/2024 9:16 PM
Hi, totally new to Laswon. I have a repor
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3291
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1375
Roger French
1315
mark.cook
1244
Forums
Filtered Topics
Unanswered
Unresolved
Announcements
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
Performance Management
Lawson Business Intelligence/Reporting/Crystal
Fields restricted? Excel pull of Lawson data through OLEDB
Please
login
to post a reply.
13 Replies
0
Subscribed to this topic
22 Subscribed to this forum
Sort:
Oldest First
Most Recent First
Author
Messages
BertNeu...
Basic Member
Posts: 8
12/6/2011 7:54 PM
So, built a DME using Lawson Query builder (LQB), (Employee table, fields = Employee, Pay-rate); execute gives expected fields. Pasted the LQB DME text into the Excel Data -other sources routine results: only the employee field was returned. Hmmm, went back to LQB, and specified the entire employee table, copied the DME, and in Excel all fields came back except anything to do with pay. Same thing when pulling from Timerecord table - only non-pay fileds returned to Excel, despite all fields showing in the "executed" LQB display.
Any ideas? Thanks in advance.
(First time poster - sure hope this is the right place...)
Greg Moeller
Veteran Member
Posts: 1498
12/6/2011 9:53 PM
Split
Does your user have security to see the pay-related fields from the employee table? OLEDB will enforce Lawson security.
BertNeu...
Basic Member
Posts: 8
12/6/2011 10:25 PM
Split
Most assuredly laua security is good as evidenced by the full expected results being seen through the LQB Execute window - all fields show. I'm wondering if there's possibly some database element security since, as I've observed thus far, this only applies to pay related data fields.
Thanks!
John Henley
Posts: 3351
12/6/2011 10:53 PM
Split
Assuming you see using the same use for each, OLE DB and excel adding use the same engine and will return same results.
BertNeu...
Basic Member
Posts: 8
12/6/2011 11:23 PM
Split
(uh-oh getting the BIG GUY involved in my first thread)
Hi John, that's the perplexing thing. I've attacheda screenshot showing the Excel OLEDB edit query window with exactly the sam text as the (superimposed) LQB window, however, LBQ is showing the 2 data fields and Excel is not
Attachments
OLEDB_MissingFieldScrShot.docx
John Henley
Posts: 3351
12/7/2011 12:39 AM
Split
I didn't realize you were using OLEDB within excel and not the assigns. Regardless the results should've been the same. Try switching the order of the fields. Also try replacing the semicolon between the fields with %3B.
BertNeu...
Basic Member
Posts: 8
12/7/2011 3:36 PM
Split
No such luck, John. Switching the order of the fields in the example from Timerecord tabel (Employee~Hours to Hours~Employee) returned exactly the same result: Employee field... and that's it. The replacement of semi to %#b returned errors in both excel and LQB.
As background, I had boiled this issue down to the simplest example (just the two fields requested) after even trying using the DME: ...&FILE=TIMERECORD&INDEX=TRDSET1&KEY=1%3D15 which dutifully returns the ALL fields for a record in the LQB, but in Excel ANY field that has anything remotely to do with pay is not returned: hours, pay-rate, wage-amount, etc., even fields that tie to LP (sick/vacation stuff) are not coming through to Excel.
(Yes, MOAddins returns results just fine also)
So, I've got a strange one, yes?
Thanks SOOOOO much even for thinking about this,
Bert
John Henley
Posts: 3351
12/7/2011 3:58 PM
Split
Try going against another table with amounts in it, like GLTRANS, and see if it's perhaps the translation of amount fields in the OLE DB provider when connected to Excel.
BertNeu...
Basic Member
Posts: 8
12/7/2011 5:31 PM
Split
You may very well be on to it! Confirmed that GLTrans fiels with numerics (possibly large digit fields) didn't come through Excel. Went back to TimeRecord Table and found these fields - all numerics - DIDN'T come through to Excel:
ANNUAL-SALARY 0
ATN-OBJ-ID
CHECK-ID 6092132
CONT-SEQ-NBR
GLT-OBJ-ID
HOURS 8
LNK-TIME-SEQ
ORIG-OBJ-ID
OT-PREM-AMT 0
OT-RATE 0
PAY-UNITS 0
RATE xx.xx (#'s obscured)
SHFT-DIFF-RATE 0
SHIFT-DIFF 0
SH-OBJ-ID
TES-OBJ-ID 1027373
TIME-SEQ 18702978
WAGE-AMOUNT xxx.xx( #'s obscured)
Okay (duh) they're not all "Pay" related as I so on-the-surface observed.
So John, do you think this is an Lawson OLEDB issue, data defintion on the database, or and Excel thing. (Surely others have tried this and gotten it to work - I'm just an "HR" guy trying different tools)
Bert
John Henley
Posts: 3351
12/7/2011 6:27 PM
Split
Glad to be making progress! I seriously doubt that very many beyond the very brave (I'll include myself in that "1%" group!) have tried using OLEDB provider outside of Crystal Reports. Most are satisfied with the Excel Addins, and have no need for using OLEDB *within* Excel...however, there are some uses, particularly for programmatic solutions, since you can't automate the Lawson Excel Addins via VBA/macros.
At this point, I'd suggest you look at what version of the OLE DB provider you have installed, and see if a newer version of the provider is on the Lawson web site, and if it fixes the problem. If not, there are some options/tracing flags you can set via the OLE DB administrator that might help, and you'll need to open a case with Lawson support.
BertNeu...
Basic Member
Posts: 8
12/7/2011 6:48 PM
Split
Thanks John - I've already got my Lawson Rep checking/validating for the latest OLEDB version and I'll relate the thread to my tech support folks to look at their options.
It's funny how you mention "most are satisified": I'm working on options that will allow us to query without having to buy more Business Objects Infoview licenses (formerly enterprise web deployment of Crystal reports we don't have LBI)and no more MOA licenses. As you can tell, I'm getting close. I was really bummed when LSF9 came around and did something quirky to MOA data calls. Prior to that I had a bunch of VBA routines doing some really cool stuff form my users just using VBA and never launching MOA - then LSF9 broke them with this wierd limiting of data packets sent back from a MOA data call. I found this option after a ton of trial and misses. And okay - I'm an HR guy that doesn't know what he can't do and nobody to tell me I can't give it a try... amzaing how ofte success follows.
I'll let you know what the results are - probably have to go into wait mode for a bit.
Bert
mikeP
Veteran Member
Posts: 151
12/7/2011 7:52 PM
Split
I am curious as to why you're using OLE DB. When we first started with Lawson (AS400) we found it much slower than ODBC. We use ODBC for pretty much all external data access, Crystal, MS Access, and MS Excel. I think there may be a few using Add-ins to query Excel, but for the most part they only use Add-ins for uploads.
BertNeu...
Basic Member
Posts: 8
12/7/2011 10:29 PM
Split
Our Lawson usage is HR/Payoll only. We didn't have ODBC for our first few years after moving to Lawson v8. As you can imagine, we had to get darn good at tweaking our indexes to get the fastest response possible out of MOA or Crystal. Then, after we started ODBC usage, we were very surprised that many of the properly indexed OLEDB /MOA calls ran considerably faster than the "replacing" ODBC call (where's that undo button?).
So for our environment only a few folks have ODBC and they have top level security access - they must control exactly what data gets out into the hands of customers (HR/PR and others) especially when publishing a report to Infoview. For all other users, they use MOA, Crystal reports or Infovew - all with OLEDB security applied. Saves a lot of time not having to build views or attempting to duplicate Lawson security against the ODBC access.
BertNeu...
Basic Member
Posts: 8
1/11/2012 8:17 PM
Split
Update 1/11: Lawson has replicated this issue and written up JT-308015 to address it.
In working with my tech group, we (he) used "Fiddler Web Debugger" to trace what was sent in the OLEDB call, and what came back to Excel in return.
Fiddler trace revealed that the (numerical) fields/data that weren't coming into Excel were actually contained in the call response - but OLEDB wasn't doing with it what it needed to so that it would come into Excel.
I'll post again as the thing develops...
Bert
Please
login
to post a reply.