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:
Saef
Past 24 Hours:
0
Prev. 24 Hours:
0
Overall:
5226
People Online:
Visitors:
314
Members:
0
Total:
314
Online Now:
New Topics
User Group Announcements
Carolina User Group Meeting
12/20/2024 3:15 PM
Date & Time: February 6, 2025, 8:30am - 4:00pm
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
Lawson S3 Procurement
ED501 Error: Map 850 not supported by /law/c15vda/lawson/test10/edi/bin/laws_out_91
11/12/2024 3:47 PM
Tried runnning ED501 and getting the atathced erro
Lawson S3 HR/Payroll/Benefits
Error
11/6/2024 9:54 PM
When I try to enroll a retiree in 72.1 health plan
Infor ERP (Syteline)
Syteline: New Data Maintenance Wizard (Error) Need help
11/1/2024 4:24 PM
Hi, I need help with an error on syteline while us
Dealing with Lawson / Infor
Implementing Lawson v10 with Cerner Surginet, Case Cart Picking, and Quick Adds for the OR
10/29/2024 4:20 PM
Hi Everyone, I am wondering if there is any org
Lawson S3 HR/Payroll/Benefits
Canada Tax Calculation (Federal and Provincial) Issue
10/23/2024 5:00 AM
Initially, we had problem with CPP2 calculation is
Lawson S3 HR/Payroll/Benefits
CA Section 125 401k Plan
10/22/2024 10:13 PM
Does anyone have any recommendations on how to fac
S3 Systems Administration
Running AC120 deleted records from ACMASTER table
10/22/2024 3:40 PM
We recently ran the AC120 as normal and somehow it
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
1372
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
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
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: 3353
12/6/2011 10:53 PM
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
(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: 3353
12/7/2011 12:39 AM
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
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: 3353
12/7/2011 3:58 PM
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
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: 3353
12/7/2011 6:27 PM
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
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
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
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
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.