In AX 2012 Budget entries can be inserted using the Office Add-ins.
This functionality is helpful also to copy an existing budget into a new one, since the “copy-budget” functionality, avaialble in AX2009, is no more available in AX2012 due to the major re-design of the budget module and functionalities.
Below, all the steps to be followed (some parts of the guide below are taken from other blogs available on the web):
Setup with AX
- Open a Development Workspace (CTRL + SHIFT + W).
- In the AOT navigate to Services > BudgetTransactionService.
- Right-click on it and select Add-ins > Register Service
4. Close the Development Workspace.
5. In the AX Client navigate to System Administration > Setup > Services and Application Integration Framework > Inbound Ports.
6. Select the New button to create a new port and populate the following:
- Port Name: BudgetImport
- Description: Importing Financial Budgets
7. Select the Service Operations button.
8. Add all the services that start with BudgetTransactionService to the left column
9. The next step is to activate the port. Click Activate on the form. Once the process is completed, users should see a message stating something similar to figure below:
10. The new port should also have a green check next to it. At this point, the datasource should be ready to be used.
11. Navigate within the Microsoft Dynamics AX 2012 client to Organization Administration, click Setup, click Document Management, and then click Document datasources.
12. Click New.
13. Set the following values:
- Module: General Ledger
- Data source type: Service
- Data source name: BudgetTransactionTransaction
- Activated: Checked
14. Click Close.
At this point, the service should be ready to be used as a Document data source.
Setup with Excel
- Open Excel and create a new spreadsheet.
- Select the Dynamics AX tab.
- Select the Connection button.
- Select the Legal Entity that you want to import into and the Account Structure you will evaluate against.
5. Select the OK button.
6. On the Dynamics AX tab select the Add Data button > Add Data.
7. Select the Budget Register Entries selection from the list and select OK.
8. You should see a form open on the left side of the Excel Spreadsheet with fields that you can drag onto the spreadsheet.
9. Select the following fields in this order to drag onto the spreadsheet.
- Ledger.Ledger Name — This will pull two fields onto the spreadsheet
- Budget Code
- Budget Model
- Default Date
NOTE: These are your budget header fields
The spreadsheet should look like the following screenshot:
10. Expand the Budget Account Entries node and drag the following field onto the form – move them a couple spaces to the left of your last field (Default Date).
Transaction Currency Amount
NOTE: These are you budget Line fields
To be clearer, add the fields at point 10 like in the image below, leaving a blank column:
11.Close the pane with all the fields that you can drag on to the spreadsheet
Insertion of new budget lines
Case 1: I would like to export the existing budget entries, copy this transaction creating a new transaction in Excel and import the new transaction in AX.
a) I created in AX the following budget entry:
b) I select the Dynamics AX tab and I click on Refresh all.
At this point, there is a LOT of data in the worksheet. Apply a filter to see that data in just the CEU ledger name
i. Click Apply filter, and add the following condition:
ii. Click OK.
iii. Click Refresh All, found in the Dynamics AX tab of the Excel ribbon. Less data should show up in the worksheet.
As an alternative, it is possible to select the rows you want to delete in the Header table, right click and select delete rows.
c) Insert the new lines (copying the ones you want to copy as in the example below).
Insert different entry number as in the example below (row 3 in the spreadsheet).
From A to E is the Header ; from G to N you have the lines (lines for the same header have an increasing line number).
d) Select the field that you want to import, select the button Publish data and then “Publish selected”:
You will obtain an import log:
e) If you come back in AX and refresh the budget entry form you will see the updated transactions:
As you can see the Entry number is automatically converted using the correct number sequence.
Case 2: import brand new Budget entries
A) Starting from the grid created in Excel
B) Add the following information into line 2:
Ledger.Ledgername: ceu — (this is the company name).
EntryNumber: Test1 — (This is a place holder, your number sequence from within AX will substitute when you import).
BudgetCode: Budget (the Budget code must exist in the company you are importing into)
BudgetModel: Sub1 (the Budget Model must exist in the company you are importing into)
Default Date: 9/10/2012
EntryNumber: Test1 —- This must match your header entry number
Comment: Anything you want
TransactionCurrency: Amount: 10,000
C) Select any cell that is blank on the spreadsheet – this will activate the “Publish Data” button.
D) Select the Publish Data button > Publish All.
You will see all data from the BudgetTransactionHeader and BudgetTransactionLine tables populate on the spreadsheet which is fine – this is in case you want to update which you can also do from the spreadsheet.
C) Open an AX client.
D) Go to Budgeting > Common > Budget Register Entries.
E) Search for budget register entry with the date of the one you entered (9/10/2012 in my case).
You can see it imported correctly. I can also see that “Test1” was replaced with 000529_1032 which was the next number in my number sequence.