Fields restricted? Excel pull of Lawson data through OLEDB

Does your user have security to see the pay-related fields from the employee table? OLEDB will enforce Lawson security.

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!

Assuming you see using the same use for each, OLE DB and excel adding use the same engine and will return same results.

(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

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.

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

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.

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

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.

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

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.

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.

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