Monday, September 29, 2014

How to use Dynamics NAV data in Excel to create a Dashboard

Many users are very familiar with Microsoft Excel and do most of their reporting and calculations using Microsoft Excel. Even some of the users create dashboards using Microsoft Excel. 
What if we can provide Microsoft Dynamics NAV data as a data source for those reporting ?? Can this be achieved without using any advance features ? Yes! 
Did you knew that Microsoft Excel add-on allow us to synchronize data without even logging in to Dynamics NAV. 
With this new functionality, users who does not have Dynamics NAV can access the Dynamics NAV data using Microsoft Excel. 
Most importantly uses does not required any additional knowledge on Dynamics NAV 2013.

Once the data table is created in Microsoft Excel, only thing user has to do is refresh the Microsoft Excel sheet with the use of Dynamics NAV add-on, and it will automatically fill the data sheet with the new data and time stamp will get registered in the Microsoft Excel sheet.

Lets see how to do this.. 

First contact a person with Microsoft Dynamics NAV access rights and knowledge in Microsoft Dynamics NAV. 

1. Go to any page that your data is available ( In this case I am using Customer Ledger Entry Page)
2. Click on the Send To Excel Button


3. This will open a excel file with all the selected data available in the database. ( Please refer bellow image)

4. In order to get the latest data go to "Dynamics NAV" toolbar in the ribbon and click "Refresh" button in the ribbon. This will check the data table with the database and all the new entries will be inserted into data table in the Microsoft Excel.


With the use of this option you can do very advance things. 
Following is a dashboard we created using this functionality. 
Thank you and Regards,
Tharanga Chandrasekara.

2 comments:

  1. How does one makes Excel to update automatically?

    ReplyDelete
    Replies
    1. You can use a web service or you can use a automation.

      Delete