Menu
Cart 0

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

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
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.


Share this post



← Older Post Newer Post →


Leave a comment

Please note, comments must be approved before they are published.