• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Get JDE report to Excel

iqkh1976

Member
I am a VBA developer and trying to get the Output of a report from JDE to Excel through ODBC.

I am able to connect to JDE using New Query option in Excel and I can see the tables but unable to find the data I am looking for.

We run a Gain and Losses report and then extract the report through Iseries and use the extracted data to create final reports.

However the task assigned to me is to run the reports in JDE and extract the data to get the final report but through Iseries I am unable to automate.

Hence was trying to get the report through ODBC directly to Excel.

I am unable to find the table or the view for the report in the list of tables under QGPL. The JDE team says it should be under F0411 table but I cannot find the table in the tree.

Thank you.
 

TerryWin

Well Known Member
I doubt very much that the F0411 file would be stored in the QGPL library. Ask the iSeries support team for the name of the Production data library and change your selection statement accordingly.
 

jean_driscoll

VIP Member
Do you have a fat client on your PC? If you do, under the 32-bit ODBC administrator (C:\Windows\SysWOW64\odbcad32.exe), you should see an ODBC named Oneworld ODA. This ODBC can be used with Excel to extract data.

As the other person said, the data is not in the QGPL library.
 

iqkh1976

Member
The report is combination of fields from 3 tables however I did not find any tables as informed by the JDE team.

Jean, I looked for Oneworld ODA in ODBC but did not find it.
 

Dunc R6

Member
Are you set on using OBDC? If your only goal is to get the iSeries report into Excel then I have a VBA solution for you. I can either post the code here or post an example .xlsm file. I was told when I first started here that we couldn't connect using OBDC so my only option was to create the VBA. Since you're a VBA developer you'd understand my code just fine. Though I'm sure you might have better ways of doing the code as I'm not a programmer...I'm a finance guy who doesn't like to do things manually lol.

The code replicates all the options in iSeries Excel add-in with these differences:
  1. It removes leading zeros from the fields unless you format the column in Excel to be text. If formatted as text it will retain the leading zeros.
  2. It keeps a user logged in until all the various reports marked for import are finished downloading and than it closes the connection and clears the password from the variable. This lets you do just one login for multiple reports.
  3. If a report isn't yet available, it keeps you logged in for 5 minutes and retries the connection every 10 seconds until it times out.
  4. It allows you to store JDE usernames in a list and then it matches them using the calculated Windows username to know who is trying to login. It's not required though, it just allows for one less thing you have to manually type. Passwords are not stored anywhere, but I guess you could add another variable if you really wanted to (yikes.)
  5. Allows for more than 65,536 rows to be downloaded in a single report.
  6. It is setup to replace a null GLSUB field with a single space so that it still looks like it's blank but so that Microsoft Access queries are happy. This only occurs if you specific that the report has this field.
  7. It is setup to remove duplicates in a particular column if you specific that the VBA is to check a given column.
  8. It is setup to remove the "total" line in a report if you specific that the VBA is to check for this.
  9. I'm currently adding another step to replace null GLASID fields with a 0 because apparently you can't do this in the World Writer expression screen from what I'm told. I have the expression SUBSTR(GLASID,2,8) in my report but I really need the blank fields to return a 0 when downloaded into Excel. So if anyone knows how to do this in the World Writer screen to replace null fields with 0 (GLASID is a string field) please feel free to tell me :)
 
Top