Retrieving and Publishing a Budget Through Excel

Posted by Author, Curator, Visually Impared Squirrel Literacy Advocate & Dynamics AX Technical Solution Professional (TSP) at Microsoft on Dec 31, 2012

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.

A while ago I came across a blog post by Becky Newell on Importing a Budget via the Excel Add-in and thought that it would be a good exercise to recreate her example, and get it working for myself.

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:

Ledger.Ledger Name
(This will pull two fields onto the spreadsheet)
Budget Code
Budget Model
Default Date

Then from the Budget account entries group, add the following additional fields:

Parent.EntryNumber
Date
Currency
LedgerDimension.MainAccount
Comment
Transaction Currency Amount

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