Best Tool to Export multiple tables to one Excel spread sheet on a workstation and not in Development Enviornment

(0) Share

Report

Posted on by Community Member

Hi,

I am new within the NAV community and we are about to go live and the accountant here at would like to access data such as Posted Sales Invoice; Header and Line Detail on the same spread sheet. He has used Access in the past. Can he still use Access or is the another tool that can help him extract that data in a simple way?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Alexander Ermakov 28,094 on at

Like (0)

Report

RE: Best Tool to Export multiple tables to one Excel spread sheet on a workstation and not in Development Enviornment

You can let accountants to copy-paste data from tables to Excel. They can use standard Excel VALUE LOOKUP functions to attach needed data from Header table to Lines table. For this, you can create a separate page for showing Posted Sales Lines (in Sales Lines table you can also add lookup flowfields with missing data from Header). Alternatively, you can setup export of data to text files (msdn.microsoft.com/.../hh167691(v=nav.90).aspx) so they will open needed text file in Excel then.

Was this reply helpful? Yes No
Verified answer

AJAnsari 5,754 on at

Like (1)

Report

RE: Best Tool to Export multiple tables to one Excel spread sheet on a workstation and not in Development Enviornment

Alexander has some good suggestions. If you are looking to export data to Excel and wish to be able to refresh the Excel spreadsheet subsequently, I would recommend looking into using one of the following options:

Option 1: Jet Express from JetReports (you get it for free if you have NAV 2009 R2 or newer)

Once installed and configured, you can use the Table Wizard to get data from one or multiple NAV tables (as a view) into one spreadsheet. You can easily refresh this with the push of a button. It is very user friendly and intuitive. Check out this video for a quick example: https://www.youtube.com/watch?v=EtGtZzbjSdo (the Table wizard is shown around the 35 second mark).

Option 2: Use Web Services in NAV to expose data and access it in Excel using OData

This takes a couple steps but will not require anything besides NAV and Excel. Go to Web Services, and create one or more new web services for the Pages from which you wish to get data in Excel. Publish the web service(s) and copy their OData URLs to a notepad.

In Excel, go to the Data tab, Get External Data > From Other Sources > From OData Data feed, and here, enter the OData URL for your first web service, enter credentials, and follow the steps to completion. Repeat the process for each additional web service you have created in NAV. You can refresh the data in this file by clicking on Refresh All from the Data tab in Excel. Use Pivot Tables to combine data as needed. If you have access to PowerPivot in Excel, it's even more powerful and easier to get data from OData feeds and model it.

I hope this helps! If it has, please take a moment to verify the answer by clicking on Yes next to "Does this answer your question?"

Was this reply helpful? Yes No
Suggested answer

dkatson 2,263 on at

Like (0)

Report

RE: Best Tool to Export multiple tables to one Excel spread sheet on a workstation and not in Development Enviornment

Just connect to your NAV SQL database from Excel, via Connections tab in Excel.

Or you can publish web services and use them also in Excel

Was this reply helpful? Yes No
Suggested answer

dkatson 2,263 on at

Like (0)

Report

RE: Best Tool to Export multiple tables to one Excel spread sheet on a workstation and not in Development Enviornment

Or you can publish web services and use them also in Excel

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Best Tool to Export multiple tables to one Excel spread sheet on a workstation and not in Development Enviornment

How do I publish the web service?

Was this reply helpful? Yes No
Suggested answer

Alexander Ermakov 28,094 on at

Like (0)

Report

RE: Best Tool to Export multiple tables to one Excel spread sheet on a workstation and not in Development Enviornment

How to: Publish a Web Service msdn.microsoft.com/.../dd338978(v=nav.90).aspx

Was this reply helpful? Yes No
Suggested answer

4BzSoftware 6,073 on at

Like (0)

Report

RE: Best Tool to Export multiple tables to one Excel spread sheet on a workstation and not in Development Enviornment

Hi Jmedinis,

Hope you interested in our Query and Excel Report tool which is built-in NAV!

With 3 simple steps, you can quickly export data to Excel:

- Select Tables.

- Select Fields.

- Export Report to Excel.

Please take a look at our website and feel free to contact us!

www.4bzsoftware.com/.../query-and-excel-report.html

Was this reply helpful? Yes No