Introduction
Use the Dynamics AX top 10 function when you want to return an item that is ranked. Normally, these items are customers, vendors, accounts or inventory items, although any item on any table or view can be ranked using this function. Once the item is returned, use other functions to return values and balances. You can copy, edit and move the function hem like any other Excel function or formula.
Dynamics AX top 10 can be used to return, not only the top, but the bottom ranked items too; it is not limited to a specific number of ranks so you can use it to deliver the top 5 or the bottom 2 if you wish.
You can build the Dynamics AX top 10 function in one of two ways:
· Using the wizard
· Typing the formula directly into a cell
Note:
SP3 multi-company reporting feature does not apply to this function
Building the function using the wizard
Introduction
To create an 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
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 Top 10 item in the list
The wizard opens adjacent to the cell you selected and will appear as follows:
Figure 20 Top 10 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 top 10 function using the Supported tables list inside Dynamics AX.
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.
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 ranked item and the basis of that ranking. Normally, the ranked item will be a reference item such as a customer account.
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 Show drop-down to pick the direction of the ranking. E.g. Top or bottom
3. Choose the rank number. You can point to a cell that contains the rank on your spreadsheet if you wish
4. Choose
the field
you want returned from the function. Use the drop-down to show a list of all
fields for this view or table.
5. Select
the measure
that is used as the basis of the ranking. This will normally be a numeric field
from the view or table.
In the following
example, the top most (rank 1) ledger account will populate the cell based upon
the measure of Amount:
Figure 21 Dynamics AX top 10 that populates a cell with the top ledger account based on Amount
Using the wizard’s buttons
Lookup
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 Lookup 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.[4]
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 top 10 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 to build the associated formula.
For Dynamics AX top 10, type the following:
=AxaptaTop10( argument list )
Where the argument list is:[5]
|
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 |
|
“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 field, rank direction, measure” |
The Output field is the item that will populate the target cell with. The rank direction is either top or bottom. (0) is Top and (1) is
bottom. The measure field is the basis of the ranking. E.g. AmountMST |
Bothe the output field and the measure must be expressed as an Dynamics
AX AOT reference. But the rank direction is either a zero (0) for top or one
(1) for bottom. |
|
Rank |
Integer or cell reference indicating the rank
number. E.g. 1 |
|
|
“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 top 10 function and the correct formula description:
And in the context of the worksheet:
When resolved gives:
Figure 22 The top ten function returns the reference item to your worksheet. You can copy this to other cells as you see fit.
Editing formulas
Editing Dynamics AX top 10 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 top 10 wizard is opened and you can adjust the settings using it.
A worked example using Top 10
Example
The following worksheet shows a range of rankings 1 through 5. Dynamics AX top 10 will be used to show the top 5 ranked revenue accounts for the current year. 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 the first ranked item
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.
Type the value 40*
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.
Click the Output
options tab
8.
Set the Show entry to be Ascending
9.
Click in the Rank entry box and then use the mouse and click cell $A$4. This gives the ranking
10.
Choose Ledger
account from the drop-down list of field
names
11.
Choose Amount from the drop-down list of measures (Using)
The worksheet
now appears as:
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:
You can extend this report to include an Dynamics AX balance for each row, starting in column C.