Problems using SQL ODBC from Navision to Excel

knight

Hi there,

I'm trying to export the chart of accounts (from table 15 G/L Account) to Excel, and I am encountering a few problems.

Firstly, I can't use the query wizard because of the fact that some field names contain an "." (Eg. G/L Account No.).. is there anyway around this without having to rename the field name?

Secondly, I cannot seem to access fields such as Net Change (flowfield) and Date Filter (flowfilter) from the table as SQL does not seem to even recognise those fields as being there. I understand from other Posts that SQL places them elsewhere... but where can I access them? It seems that ODBC from a Navision SQL Server is much more complicated than from a normal Navision Server.

Any good sources of info would be appreciated!

Thanks

Simon Kuldin
Information Outlook

StefanZimmer

Hy

to avoid problems with fieldnames and ODBC just define a SQL-view with compatible fieldnames.
But remember to grant only "read-rights" to this view.

AFAIK there is no way to get datas from flow-fields over odbc. <img border="0" title="" alt="[Frown]" src="images/smiles/icon_sad.gif" />

When i need flow-fields in excel, i use the automationserver objects in CAL to create this excelsheet.

Bye Stefan

<small>[ 01-08-2002, 12:24: Message edited by: StefanZimmer ]</small>

knight

Thanks for your response,

If I could ask one more question however.. do you have some sample code to show me how I could go about populating an Excel spreadsheet via an Automation object?

Your help is greatly appreciated,

Simon
Information Outlook

knight

Hi again Stefan,

I've started to use examples of Automation from other places within this forum. It is much more useful than structuring SQL queries in VB for trying to use ODBC.

Thank you very much for putting me on the right track.

I appreciate it!

Simon
Information Outlook

StefanZimmer

<img border="0" title="" alt="[Big Grin]" src="images/smiles/icon_biggrin.gif" />
Stefan

MattRowe

Hi Simon

Another approach you might like to try is to use SQL Server's Analysis services tools to create a cube of your chart of accounts. This negates the need to pick up any flow fields at all, as the cube engine does all the work for you.

I have had some good success with this approach and the resulting cube(s) are extreamly flexible when it comes to financial analysis. You can also create your own 'Calculated Measures' i.e. ROCE etc. within the cube for further analysis.

After you have created the cube you can attach Excel (2000/XP) to it and view the data in a pivot table/pivot chart.

If you want to know more drop me a line.

Regards

Matt

P.S. I am NOT part of a company selling solutions, so don't worry about a sales pitch.

knight

Sounds good... how'd I got about using it? (SQL Server) that is...