|The Excel add-in for Dynamics AX is a great tool for getting data from AX into Excel for reporting and analysis. But if you set up your document services the right way then it becomes more useful by allowing you to publish data back into AX directly from the Excel spreadsheet.|
|Configuring the Document Service|
|We first need to configure the document services to enable the importing and exporting of data. To do this, open up the services in AOT, and select the BudgetTransactionsService. In the context menu, open up the Add-Ins, and choose the Register service function.|
|This will register all of the required services as a web service.|
|Now from the System administration area within AX, open up the Inbound Port configuration tool, and a Create a new port named BudgetImport.|
|Then click on the Service operations button and from the service operations on the right, select all of the BudgetTransactionService* functions, and select them for the operation.
After you have done that, click Activate to enable your port. Note: this may take a while to run.
|Creating an Excel Template|
|Now that the service is configured, we can open up Excel and go straight to the Dynamics AX tab to grab our data. If you don’t currently have the Dynamics AX tab configured, here is a link to the instructions for installing it: http://technet.microsoft.com/en-us/library/gg731844.aspx
The first step is to set up the default Connection for the spreadsheet.
|Then click on the Add Data button to select the Budget Register Entries document service.|
|This will connect to Dynamics AX and show you all of the available document fields that you can use in your spreadsheet.|
|From the budget register entries group, drag over the following fields:
|Then from the Budget account entries group, add the following additional fields:
|Click on the Field Chooser button to hide the fields on the left, and will enable the Refresh All button on the ribbon. Click on it and you will see all of the budgets populate in the spreadsheet.|
|Retrieving Data from AX|
|In this example we will use the filter operation in Excel to just select one of the budgets that we have set up in the system.|
|Now we have our budget data in Excel.|
|Publishing Data back into AX|
|But there’s more! If we modify any of the budget entries…|
|And then click on the Publish Data link in the ribbon bar…|
|The data that we changed will be pushed back into AX.|
|Here is the data that we changed in Excel, updated in the ERP system.|
|How cool is that.|