Skip to main content
Post a question

Notifications

Announcements

No record found.

Microsoft Dynamics NAV (Archived)

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

Posted on 30 Mar 2017 12:05:43 by Microsoft Employee

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

  • Suggested answer
    4BzSoftware Profile Picture
    4BzSoftware 6,071 on 31 Mar 2017 at 07:14:35
    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

  • Suggested answer
    Alexander Ermakov Profile Picture
    Alexander Ermakov 28,094 on 30 Mar 2017 at 19:52:07
    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

  • Community Member Profile Picture
    Community Member Microsoft Employee on 30 Mar 2017 at 18:36:19
    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?

  • Suggested answer
    dkatson Profile Picture
    dkatson 2,263 on 30 Mar 2017 at 17:28:02
    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

  • Suggested answer
    dkatson Profile Picture
    dkatson 2,263 on 30 Mar 2017 at 17:27:49
    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

  • Verified answer
    AJAnsari Profile Picture
    AJAnsari 5,754 on 30 Mar 2017 at 14:43:55
    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?"

  • Suggested answer
    Alexander Ermakov Profile Picture
    Alexander Ermakov 28,094 on 30 Mar 2017 at 13:09:47
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans