Introduction
Use Dynamics AX balance when you want to return an amount into a cell on your spreadsheet. The amount is returned via a formula, you build the formula using the wizard provided. Once created, you can then copy, edit and move them like any other Excel function. Dynamics AX Balance functions return an amount back into Microsoft Excel.
It is these features that will allow you to easily build complex reports from within Microsoft Excel.
You can build the Dynamics AX balance function in one of two ways:
· Using the wizard
· Typing the function directly into a cell
Dynamics AX balance offers drill down features too. These are available after the function has been built and evaluated, from the right-click menu.
Building the function using the wizard
Introduction
To create a Dynamics AX balance function using the wizard, follow the steps outlined below:
· Open the wizard
· Choose from which data source you want to get the data from
· Enter filters against that data source
· Choose the measure to populate the cell with
· Optionally apply special output options
Note:
For SP3 users this function was enhanced to allow you to select one or more companies from which the query will return values. Use the Dynamics AX filter expressions to describe the list of companies you wish to include. E.g. “DMO, USA” gets results from DMO and USA. “DM?” for example, gets results from the company accounts that begin with “DM”
Opening the wizard
This is done from the Atlas menu or from the shortcut keys as follows:
1. Select the cell into which you want to put the formula
2. Click the Dynamics AX menu option. (Alt+X)
3. Click the Balance item in the list
The wizard opens adjacent to the cell you selected and will appear as follows:
Figure 8 Dynamics AX balance wizard
Choosing a data source for your formula
Here you select from which table or view the data will come from. The list will be limited to those data sources that have been nominated as available for use with Dynamics AX balance function.
1. Click the drop-down list to reveal a list of data sources
2. Use your keyboard navigation keys or your mouse to highlight and select a source
Entering a filter for your data source
A filter applies constraints to the data on your data source. You might apply filters to restrict the range of values that are of interest to you. You will apply filters to the list of fields available in your data source. These are shown in the Range grid on the Dynamics AX balance form.
Filters can be typed into the wizard directly or they can be sourced from a cell or range of cells in your workbook.
1. Click the row in the grid that contains the data source field you would like to filter on
2. Enter the filter expression in the Range entry box or navigate to the cell or range of cells that contain the filter expression
3. Repeat for each filter you wish to apply.
Note:
Filter expressions describe the filter being applied. They conform to the conventions used by Dynamics AX and are described in more detail at the beginning of this document. An example of a filter expression applied to an account range may be 40*, 50*, !5
Choosing a measure to populate the cell with
This tab is used to define what you want to show as the measure or amount in the cell. Normally, this will be a numeric value from your data source and may be quantity or an amount of some kind. This field is normally filled in with a default value. You can choose to use this value or you can change it suit.
1. Click the Output options tab page
2. Click the Populate cell with drop-down and pick the amount from the list
In the following
example, the measure is Amount:
Figure 9 Dynamics AX balance that populates a cell with the sum of Amount
Other output options
Introduction
You can choose to adjust the output from the wizard using:
· Invert sign
· Allow comment break-out
Invert sign
Tick this option to change the direction of the sign. A positive number becomes negative and vice-versa.
Using the wizard’s buttons
Field browser
Use this button to give a list of the possible reference items you are selecting when you enter a filter expression into the range box.
1. Click on the row that contains the data source field you want to filter
2. Click the Field browser button
3. A form appears showing a list of items, use your navigation keys to select the value you need.
OK
Execute the request and close the form. This will build the formula, evaluate the formula, set any registry keys and then close the form.
Cancel
Closes the formula wizard and resets the cell formula to that when the wizard opened.
Apply
This is similar in nature to the OK button but will not close the formula wizard.
Building the formula without the wizard
Introduction
This is an alternative to using the wizard. It is not the preferred method, as it requires you to understand the structure of the function very well. However, including it here will help you understand the function’s structure so that you can modify any that you have already created.[2]
The steps involved include:
1. Selecting the target cell for the formula
2. Building the function by specifying its arguments
Select the target cell for the function
The formula operates like any other Microsoft Excel formula. You can create it from within the cell you have chosen. Simply enter the ‘=’ command and then type the formula according to the correct format. (If you omit the equal sign, Microsoft Excel will treat the formula as plain text and will not compute the result)
Select the cell into which the function will be put and then press F2 to switch on edit mode.
Building the function and specifying its arguments
The Dynamics AX balance function has a pre-defined structure, like all other Microsoft Excel built-in functions. Like all formulas and functions you can use the formula bar or you can type directly into the target cell.
For Dynamics AX balance, type the following:
=AxaptaBalance( argument list )
Where the argument list is:[3]
|
Formula argument |
What to enter as the Argument… |
Comments |
|
“Version number”, |
V35 |
|
|
“Company”, |
Enter the Dynamics AX company account code. E.g. “DMO” |
Dynamics AX company where data is source. This can be an expression in
SP3. E.g. US* |
|
“View”, |
View or table against which the function will be performed. E.g. “LedgerTrans” |
This must be expressed as an Dynamics AX’s AOT reference. |
|
“Output measure, sign” |
The measure field is the value to display in the cell. The sign is
used to modify the value to show as negative or positive. E.g. AmountMicrosoftT, 0 |
The measure must be expressed as an Dynamics AX’s AOT reference. But
the sign is either a zero (0) or one (1). Use 1 if you wish to invert the
value by multiplying it be -1. |
|
“Break out basis, break out measure, Dates as periods”, |
Not used |
Optional: Enter “”, |
|
“Field list, ….“, |
Comma separated list of fields for which a criteria has been attached. |
Optional. E.g. “AccountNum, TransDate” If no criteria is used enter as “”,
|
|
Field list criteria, …. |
Comma separated list of criteria values. These values may be
references to cells in the spreadsheet |
Optional. E.g. “40*”, “CY” or $B$4, $B$5 |
Note:
The criteria can be typed directly or can de derived from a cell reference. Depending on how you specify the cell reference can have an effect when copying functions in your spreadsheet. Excel adjusts cell references relative to each new location of the formula.
To override this behaviour, you need to indicate that a cell location in a formula is absolute rather than relative. You do this by inserting a dollar ($) symbol before the column and/or row designation. So, for cell B6 to be an absolute reference you will need to describe it as $B$6. Here are some other combinations:
· Column relative, row absolute: B$6
· Column absolute, row relative: $B6
· Column and row absolute: $B$6
· Column and row relative: B6
You can cycle through these alternative by using the F4 command on your keyboard.
Here is an example of a cell with the complete Dynamics AX balance function and the correct formula description:
And in the context of the worksheet:
When resolved gives:
Using the right-click drill down features
Introduction
When a cell formula has been built, you can use the right-click menu to investigate the transactions that make up the balance. The right-click offers three options:
· Transaction drill down
· Transaction report
· Pivot table report view
Transaction drill down
Outline
Using this option, a list of the transactions that make up the cell balance is shown in tabular form. Using this form you can sort the transactions within it, group the columns according the fields selected, drill-down to see the associated voucher (only if voucher appears in the result set) and paste the results to a location in your existing worksheet or to a new workbook.
Note:
SP3 will include a company account column if the function returns results from more than one company. If the results are for one company account only, then this is extra column is not included.
Opening the transaction view form
To open the form you must have selected a cell that contains a valid Atlas Dynamics AX balance formula. In the following example, a drill down to transactions is to be done on cell C6:
Figure 10 Select an Dynamics AX balance formula before using the right click drill down options
1.
Choose the cell containing the
Dynamics AX balance formula
2.
Using the
mouse right-click to show the
right-click menu
3.
Select the Dynamics
AX menu and then choose Transaction
drill down
The following
form appears:
Figure 11 Sample transaction drill down from Dynamics AX balance
Note:
The columns shown are those found in the Autoreport field group in the AOT for that table or view.
Grouping transactions on the form
You can use the form to group the entries that appear in
the grid. Simple drag the column heading to the area above the grid
specifically for this:
Figure 12 Transaction drill down form showing the drag and drop grouping area. Simply drag a column onto the drop zone to group the list.
In the following example, Currency has been dropped onto the grouping area at the top of the form. Use the (+) and (-) signs to expand or collapse the sections of the form accordingly:
Figure 13 Transactions grouped by currency
You can added addition columns to the grouping section to suit your analysis needs.
Drilling through to ledger voucher
If the drill down includes the voucher as column, then you can select the Voucher tab to see all the transactions that belong to that voucher. Simply:
1. Select the transaction with the voucher you are interested in from the grid.
2.
Using your mouse, click the Voucher
tab
The transactions that make up the voucher are displayed for you. The following is an example of this in action:
Figure 14 Select the transaction with the voucher you want to review.
Figure 15 The voucher includes all transactions regardless of filters.
Saving the transaction listing to an Excel worksheet
You can save the results to a new location in your spreadsheet. To do this you will need to:
1. Select the destination from the drop-down list at the base of the form. E.g. New Worksheet
2. Click the paste button, which pastes the results and close the form
In the following example, the list is saved to a New Worksheet:
Figure 16 Saving the transaction drill down to a new Worksheet
Click paste causes the list to be pasted to a new Worksheet:
Note:
Grouping, if applied to the form will be applied to the pasted results too
Transaction report
Outline
This is similar in nature to the Transaction Drill down except that you can choose to send the results to either the drill down form or you can send the results to a new worksheet destination.
Opening the transaction report wizard and producing the drill down
To open the transaction report wizard you must have selected a cell that contains a valid Atlas Dynamics AX balance formula. In the following example, a transaction report will be prepared for the account 40110:
1.
Choose the cell Dynamics
AX balance formula (C6)
2.
Using the
mouse right-click to show the
right-click menu
3.
Select the Dynamics
AX menu and then choose Transaction
report
The following
form appears:
Basic information is complete, such as the criteria, you will now need to fill in what you want as the column basis, the destination, grouping parameters, amount selections and formatting (for more detail see the section on Transaction report). In this example the report will show transactions in a new worksheet:
When the report is executed the results are as follows:
Pivot table drill down
Outline
Use this feature to show the drill down as a pivot table on another worksheet. You can use this to see a summarized perspective of the main elements that make up the balance. For example, currency and ledger account.
Opening the pivot table wizard and producing the drill down
To open the pivot table wizard you must have selected a cell that contains a valid Atlas Dynamics AX balance formula. In the following example, a pivot table drill down for the account 40111 is to be done, so cell C6 is selected:
4.
Choose the cell C6
5.
Using the
mouse right-click to show the
right-click menu
6.
Select the Dynamics
AX menu and then choose Pivot
report or Pivot table cross-tab
The following
form appears:
Basic information is complete, such as the criteria, you will now need to fill in what you want as the row and column basis, the destination, grouping parameters, amount selections and formatting (for more detail see the sections on Pivot table cross-tab and Pivot report). In this example the report will show description as the row basis and currency the column basis in a new worksheet. The amount selection is ledger amount:
Figure 17 Use the row and column basis to determine the level of analysis you want to see.
When the report is executed the results are as follows:
Editing and extending formulas
Introduction
Once the function has been built and the formula created in the target cell, you can edit and extend that formula like any other Excel formulas.
You can edit the formula in the cell directly or you can use the formula bar above the worksheet or recall the wizard to do the editing.
Because the Dynamics AX balance function returns a value, you can manipulate this value by extending the formula or use it in other formulas
Editing Dynamics AX balance functions
Microsoft Excel allows you to edit a cell containing a formula in the same way as you would any other cell. Position the cell pointer over the formula and press F2 to enter edit mode. Move the insertion point to the argument or value you want to adjust and change accordingly. Press enter to make the changes permanent or use the ESC key to cancel the edit.
If you double-click on the cell the Dynamics AX balance wizard is opened and you can adjust the settings using it.
Extending the formula
Because the formula returns a value, you can manipulate the result in formula or by using other formulas that refer to it. In this example, the result of the formula is divided by 1000 to give a number more suitable for consolidated reporting:
Instead of:
The result is:
A worked example using Balance
Example
The following worksheet shows a range of revenue accounts. Dynamics AX balance will be used to show the year-to-date value for each of these accounts. To do this the wizard will be used to create the first formula, which will be copied downwards to resolve the value for each of the other accounts.
Create the formula for account 40110
Open the wizard and complete as follows:
1.
Choose view Ledger
transactions
2.
Click ledger
account row in the range grid. The row is
selected
3.
Click in the Range
entry box beneath the grid
4.
Using the mouse click in cell A5
5.
Click Date row in the range grid. The row is selected
6.
Click in the Range
entry box beneath the grid and type CY
7.
Note the sum
field of Amount and then click OK
The worksheet
now appears as:
Figure 18 Dynamics AX balance example for a sales account
Copy the formula down for all rows in the report
Copy the formula by selecting the small AutoFill square at the bottom right of the cell containing the formula and dragging that down for the extent of the account range, thus:
Figure 19 Use the AutoFill handle to copy the Dynamics AX balance formula to all rows of the report