Retrieving and Publishing a Budget through Excel Using Matrix Fields

Posted by Author, Curator, Visually Impared Squirrel Literacy Advocate & Dynamics AX Technical Solution Professional (TSP) at Microsoft on Jan 08, 2013

The Dynamics AX Excel Add-In is great for updating data through a familiar tool, and in a previous post I showed how you could use it to update budgets within Excel. Although a lot of the time when you are adjusting budgets, you may not want to see every period’s postings one by one.

In the following worked example, we will show how you can use a feature in the Excel Add-In to create summary fields that allow you to update multiple records within the database at once, and then show how you can use this same feature to create a tabular style budget maintenance form within Excel to make the budget updates even easier.

Create A Matrix Field

The key tool that we will be using in these examples are the feature that allows you to create a summary Matrix Field within the field browser. To do this right mouse click on the headings in the field browser and select the Create Matrix Field from the context menu.

Give the new field a name, and then select a measure that you want to update within the spreadsheet. In this case we use the Transaction currency amount field.

For the budgeting example, we will restrict the data that is summarized through a condition. Select the Budget code field from the register entries table as the conditional field.

And then select the Budget budget code from the value list.

Our Matrix Field should look something like this.

Using The Matrix Field

Now that we have the Total Budget field defined, we can copy it over into the data update spreadsheet. This will summarize all fields that match the selection criteria.

Notice that in this example, we removed the Line Number field from the list of fields. This way the Total Budget field will be the sum of all the budget lines for that account.

In this example we will filter down the data just to one of the budget registers that we will be updating.

Now, we can change the value of the Total Budget, and publish it.

We can make sure that all of our changes were processed.

An in our Budget Register in AX, all of the lines that applied to that account have been adjusted proportionally.

Creating Period Summary Matrix Fields

We can do something that is even more useful by creating Matrix Fields that have more elaborate conditional statements. In this example, we will create a field that only selects the budget entries that are within a date range, creating a period field.

After creating the first field, it’s a mechanical process to create the other 11 period summary fields.

Now we will extend out our spreadsheet and replace the Total Budget field with the twelve period fields.

Now when we refresh the data in the spreadsheet, the period budgets are summarized by account.

If we can update any one of the periods and then publish the data back to Dynamics AX.

Only the period that we selected is updated.

The example that we have been using is just one account which is a little to simple for the real world. So lets add a few more GS&A accounts to our budget.

Now all 80+ budget entries are summaries into a manageable budgeting template that we can quickly update from Excel.

Share this post