AccountingWeb
News
Tutorial: Import Data from Navision into Excel. By David Carter
This page is for readers of AccountingWEB who need to get reports out of Navision (aka Microsoft Dynamics NAV).It’s quite hard to get reports out of Navision. Experienced users agree, and it is certainly my own experience, that the ODBC route is the best - in fact the only - way to go.
Once you’ve installed ODBC, you can download each data table individually into Excel. Then start digging through them all (there’s a lot!) to find where your data is located.
This article contains instructions on how to install your ODBC driver and link it to Excel.
[Note: the instructions below apply if you are using Navision's proprietary database. If you are running on SQL Server, click here to see an article on setting up Access Dimensions as a SQL data source. It's the same process in SQL Server, whichever accounts package you are using]
1. Installing ODBC
First, you must find your ODBC driver and install it on your machine. On my Navision demo disk the driver is in a folder called CODBC. To install it, choose setup.exe.
After the driver has been installed, go into (Windows XP) Settings – Control Panel – Administrative Tools – Data Sources (ODBC). In the ODBC Administrator box, go to the Drivers tab and check that C/ODBC 32bit has been added to the list of installed drivers.
Second step is to set up your Navision data files as a data source. This involves moving to the System DSN tab, and telling Windows the data path where your Navision files are located.
An extremely useful and comprehensive set of instructions for doing this has been provided by Mr J Carlton Collins on his mbsadvisor.com website.
Rather than repeat what he says, I suggest you download and use them. (Note that Mr Collins sometimes refers to Navision Attain, which was the marketing name for Navision a few years ago.)
Important point: when setting up as a data source, make sure you go into Options and tick the Read Only box. Otherwise you could start changing the data files in Navision.
2. Download your data files into Excel.
Once you’ve installed ODBC and set up Navision as a data source (DSN), you can download the various Navision data tables and start searching through them to find your data.
Mr Collins shows how to do this in Exercise 1 of his instructions (download the Cust_Ledger table into Excel).
Work through that exercise, then download some other tables.
MORE TIPS ON DOWNLOADING DATA FILES INTO EXCEL
Start up Excel; open a new workbook. Click onto cell A1.
In the Choose Data Source box, highlight Navision. OK.
Hopefully, you now see the Query Wizard – Choose columns box, with a list of tables.
You are in. These are the data tables for Navision, and there’s an awful lot of them!
Download them one at a time
As with most accounts packages, the data in Navision is “normalised”, i.e. scattered around in numerous data tables.
Probably only 1-2% of this data will actually be useful for reporting. You now have to embark on a journey of discovery to find out where that 1-2% is located in Navision.
Download the tables that look interesting. Download them into Excel one at a time, then see what data each one contains and identify the important fields. Some important tables are:
for Sales and Margin Analysis
for Nominal (GL) Reporting)
Don't download every single record
When you download, e.g., GL Entry for the first time, select all the fields within the table. To do this, highlight the table name, then click on the right facing chevron. All the fields appear in the right hand box. Next.
You are now in the Filter screen. You don't want to bring over records from years back, only the more recent ones. Therefore :
Select Transaction Date = greater than 6 months ago. Next
In the Sort screen, choose: Transaction Date – Descending.
This means that only the last 6 months transactions will come over, and the most recent ones will appear at the top of the screen.
Next. Then just press Enter to Return the Data to Excel.
Use Paste Special – Transpose
Once you’ve downloaded the data table into Excel, highlight the first 4 or 5 rows, then Edit – Copy. Go into another worksheet, cell A1, then Edit – Paste Special – Transpose.
The field names are copied into column A and are much easier to read.
END OF SESSION
Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
Reporting tutorials index: Use MS Query to extract and analyse accounts data
Reporting Tools #4: Introduction to summary reporting
Towards an analysis-ready file for reporting #1
Towards an analysis-ready file for reporting #2
Management Reporting in Sage Line 50 and Instant
Want to learn more about pivot tables? Start here
Improve your reporting skills with self-teach tutorials
An introduction to Excel-driven reporting tools
Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.
AccountingWEB.co.uk 9-Oct-2007
Categories: IT Features, Management Reporting Features, ExcelZone Features
Times read: 6189
