How to add formula to excel column while data export from Dynamics AX.

This Post is not about how the Dynamics AX data imported or exported, as you will find lots of blogs from Google. This post is about how to place the formula on a excel column which is not much available through search engins.

for this post you should have knowledge of excel export through x++.  if you would like to know see below links.

http://axaptadeveloper.blogspot.com/2008/06/export-data-to-microsoft-excel-from.html

https://community.dynamics.com/product/ax/axtechnical/b/mafsarkhan/archive/2011/03/07/dynamics-ax-code-to-read-write-data-to-excel.aspx

Few days back i came accross a scenario where i have to place the formula in a column of excel, the scenario was that i have to create the Bidding price sheet where i have to export some item data along with quantity and two empty columns, ‘Unit bidding price‘ and ‘total bidding price‘, both of these columns were empty but i also have to place formula of product on total bidding price column so that we can get the product of Quantity and Unit Bidding price. below piece of line helps me in putting formula on a column.

sysExcelWorksheet.cells().item(row,11).value(strfmt(“=PRODUCT(H%1:J%1)”,row));

Thanks to Kent Carstens who helps me out in this.

Office Addins Dynamics AX 2012

In the newer version AX2012, Their is a great Addin available called Office Addin, with this Addin the data Import/Export is really easy. The office addin also have the out of box consumer for comsuming the AX document services. This is real cool feature. You can use this addin to export data from Table, or if you need to export data on some criteria, then you can build the AOT queries to do the query based export. The Addin can be used to consume the web service operation, means Now you can fill the record in excel and just published, the record will be created/ updated in AX, read operation is also supported with this Addin. see pictures below for look of this tool.

Inorder to install it you need to run the setup, in the Addins you will find the Addin for Office. The installation is really simple, just next next and finished.

Once setup is installed, you have to do some setup in dynamics ax to use the addins.
Under Organization Administration> Setup> Document Management > Document datasources
On this form you need to do the setup, if you want to consume any document service from office addin, or you want to use the Query based export. Simply create a new record, specify the type of datasource (service/ Query), select the service or Query and make it active.
Note: this setup is not required for Table export.

Once the Addin installed, you will see the Dynamics AX tab in you Excel. You will see options like, Add data (for selecting service/ Query) , Add Table (here you can select Tables from AX). you will also find option for publishing the data to and from Dynamics AX. See screen below.

After selecting the Table, Query, or Service click Ok. This will open the Fields Choser in Excel where you can select the fields or drag it to Excel sheet.

Close the fields choser, click the Refresh data to see the data in excel,

In case or Web service, you can create records in Excel or modify the record in Excel, click the publish button to insert/ update records in AX.