automatic Data Import in Excel via ODBC??

Hallo Forum,

i am pretty new in Navision (vers. 2009). We want to take raw data like sales, material costs or inventories every month and automaticaly put it into an excel file. The question ist now about the possibilities to realise it with ODBC (Instead of implementing a cube).

What is needed to do it this way?

Do i need a Software to realise the query or is it enough to install the driver and write appropriaty query in excell?

thx for the help

Hi,

There a any number of solution to your problem and will depend on how you want to proceed:

  1. Dataport / XMLport to flat files which can be opened in Excel

  2. Report which export to excel

  3. ODBC and a query build within the Excel sheet to refresh the values

  4. Report writer e.g. reporting services to export the values.

Installing the ODBC and writing the query will work but be aware that there may be a preformance hit on the server.

Not to forget not being able to extract flow-fields.

thx for the answer,

We want to take about 40 Values from the DB monthly (extracting flow-fields not necessary here?).

Can it be made a predication about the needed performance (time?) if we use ODBC?

Hi,

It all depends on the values and where these are taken from - if you can give more detail then we can guide you.

The performance is hughly dependent on the data required, system setup, hardware so you wont know until you try. The point here is to be aware that it MAY have an impact and to take this into account by doing the update at an appropriate time.

kk, i gonna keep it in mind. Maby it is possible to make the update at night.

the needed data is: sales, capital, Increase in inventories, annual net profit, liquid assets, order balance, Marketing expenses and so on.

Its desirable, that it can be automatically writen in one excel file without extra license costs. So i suppose the solution with ODBC should be the best here

Hi,

It sound like you will be taking summarised values off the G/L Entry table which is typically a very large file. Have you considered build an account schedule, exporting to Excel and linking in the existing Excel sheet to the account schedule output.

This a real beginners/functional question?

What does ODBC stands for?

I already googled it!

Dont bother to answer I am sorry for annoying all of you.

gonna check this option

Is it possible to autmise this procedure, so it can export the data to Excel every month without going into Navision?

Hi,

Yes with some coding. Possibly using the NAS and a codeunit with amended code from report 29

ok, gonna keep this in mind for futer solution. I think for the first we gonna try it without programming in nav

Always a sensible approach - far too often people go headlong into development without proving the concept.

actually a good point, some later i want to expose the different (and better) possibilities for our projekt (XMLPort, Report, potentialities of the RolesTailored Client etc.). So maby i gonna come back to some of this points :slight_smile: