Introduction
Main table report is used primarily to return a list of reference items and associated descriptions and/or other attributes to a range of cells in Excel. Normally, you use this function to get a list of ledger accounts, vendors, customers and dimensions.
Main table report is not implemented as an Microsoft Excel function; instead, the results are pasted back to Microsoft Excel as a snapshot of the current Dynamics AX data.
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”
Building the report using the wizard
Introduction
You can only create a transaction report by 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 fields to populate the worksheet with
· Apply up to three levels of grouping[8]
· Apply sub-totals to match the groups
· Apply formatting, these can be manual or auto-formats
· Apply, where necessary, managed columns such columns to the right or to the left the report are synchronized with the output of the report
· Save parameters if you wish to re-use your report
Opening the wizard
This is done from the Atlas menu or from the shortcut keys as follows:
1. Select the cell into where you want to report to be positioned
2. Click the Dynamics AX menu option. (Alt+X)
3. Click the Main table report item in the list
The same effect is achieved by typing Alt+XM
The wizard opens and will appear as follows:
Figure 25 Main table report wizard
Choosing a data source for the report
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 the Transaction reporting wizard 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 what fields to populate the spreadsheet with
The Output options tab is used to define what you want to show on the spreadsheet. By default pre-set columns from Dynamics AX’s Auto-report group are shown.
You can add, remove or change the order and number of columns to suit your needs. Available columns are shown in the left pane and selected columns are shown in the right-hand pane.
1. Click the Output options tab page
2. Choose
a field from the Available columns list
3. Click
the >
button to include it in the Selected columns
list or use the < and << to move a single or multiple
columns back to the Available columns list
4. Use
the Up
and Down
buttons to change the order of the Selected columns
5. When
you are satisfied with the list of columns to include in the report, choose a
destination for the report. Use the Destination
drop-down to make your choice. Current cell is the top left corner of the
report.
6. Choose
the direction of the output, Click Down to paste the
list down the worksheet or Across to paste it
sideways
In the following
example, the selected columns represent the Auto-report group from Ledger chart
of accounts.
Figure 26 Autoreport fields automatically appear in the Selected fields list.
Note:
SP3 allows you to include an additional column for company accounts. This can be used if you wish to get a list that includes a reference to the company in which the main table record is. E.g. a customer list by company.
Group and sort options
Outline
Without grouping, the report will list the items returned from the query. However, this tab allows you decide whether that list will be grouped in anyway. Transactions can be grouped by up to three levels using any of the fields from the Selected columns list from the Output options tab page. Indentation of the levels indicates the level of the grouping, level 1 being the highest level and level 3 being the lowest.
Within the grouping the transactions can be sorted. The sorting again relies upon a field from the Selected column list and can be ordered to show transactions in ascending order or descending order.
Grouping
Follow the steps below to group your report:
1. Select the Group / Sort tab
2. Click the drop-down for Group level 1.
3. Choose a report field by which the report will be grouped or leave as None to produce a list of transactions only.
4. Where applicable, tick the Include description box to show the code and the description for the grouping field.
5. Repeat for each level of grouping that you need
In the following example, the list will be grouped by Ledger account type.
Figure 27 Grouping the Main table report allows you to structure your list. Sorting is used to arrange the elements in it.
Sorting
Sort applies to the records of the request. Leaving the sorting option as None will mean that list be ordered according to how the records are returned from the query. Select sorting parameters as follows:
1. Click the Sort by drop-down list
2. Choose a report field to base the sorting on.
3. Click the direction drop-down to choose the order of the sort; ascending or descending.
Applying sub-totals
Use this tab to define which columns are totaled at each group level or for grand total purposes. The form shows two list; one containing the available columns that can be sub-totaled and the other shows the selected columns. By default, the Selected columns are derived from the numeric fields on the data source.[9]
1. Click on the Subtotal tab
2. Choose
a field from the Available fields list
3. Click
the >
button to include it in the Selected fields
list or use the < and << to move a single or multiple
columns back to the Available fields list
4. Use
the Up
and Down
buttons to change the order of the Selected columns
Applying formatting to your report
Outline
Formatting options extend to the use of Microsoft Excel’s Auto-formats and the use manual formatting. Number styles can be defined which can then be used on the numeric columns of the report. Atlas uses the default numbers styles but you can choose to add to these if these are insufficient.
By default, Auto-format style Simple and Number Style Comma are selected.
Auto-format
1. Click the Format tab page
2. Click to select the Auto formatting option
3. Click the drop-down of Auto-format styles and choose a style that suits your report. For no formatting, choose None.
Manual formatting
Manual formatting applies to the formatting of the text only, it does not include borders or shading of cells containing that text.
1. Click the Format tab page
2. Click to select the Manual formatting option
3. Click the drop-down of Manual format options and choose an option to which you wish to apply a format. For example, Headings. Other alternatives include: Sub-totals, grand total, sub-headings and normal text. The preview window is activated at this point
4.
Click the Modify… button and
choose the text formatting options that suit the report you want to produce.
Here you can see that the
5. Repeat for each format option you want to adjust.
Choosing a number style
Number Styles are used by Microsoft Excel whenever you click the ($), (,) or (%) buttons on the formatting toolbar. Excel allows you to create your own styles and these are saved with the workbook. Atlas uses these styles when producing reports.
1. Click the Number style drop down.
2.
Choose the style that you want to apply. Choices
include Comma, Comma [0], Percent, Currency, Currency [0] and
3. Click Modify… to add your own style. Here you can see that the style AtlasComma has been defined:
Saving your settings and column management
The final tab page governs whether you want to save your settings so that they can be used at a later date and allows you to include columns to be synchronized with the report even though they are not part of the Atlas report.
Saving your settings
The final tab page governs whether you want to save your settings so that they can be used at a later date.
1. Click the Save tab page
2. Click the Save as drop-down list. Type a name for this report. E.g. My Main table report
3. Click in the Named range box and give the report a named range. E.g. MyReport. This acts like a place holder, use this for those reports that will be refreshed, otherwise Atlas will put the report wherever the current cell happens to be.
4. Un-tick the Refresh option if this report is a one off and is not to be refresh.
Managed columns
The Managed columns feature can be used to synchronize columns on your worksheet with the report from Dynamics AX. You can include columns from either the left of the report or from the right. It is available for non-grouped reports and for reports that display down the page rather than across it.
Enter a positive number to include columns to the right of the last column of the report and a negative number to choose columns to the left of the first column of the report. For example, 6 will include six columns to the right of the last column.
You will use this feature with columns that relate to each row of the report but are not part of its definition. E.g. A main table report of customers and an adjacent column that has a balance formula that returns the customer balance.
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 execute the query, display the results and save any parameters from the Save tab page. The wizard is then closed.
Cancel
Closes the wizard and resets any settings the wizard may have changed whilst open.
Apply
This is similar in nature to the OK button but will not close the wizard.
Editing your saved parameters
Introduction
Saved parameters are a way to keep the settings you applied to build a report. You can re-use these settings by calling up the saved parameters whenever you want to run the report. If you want to adjust these settings, you can recall the saved parameters and then change the settings accordingly.
Recalling your saved parameters
Open the wizard as normal and then:
1. Click the Saved drop-down and pick a saved parameter
2. When the settings have been loaded, make the necessary changes and
3. Re-run the report
Managing your saved parameters
You can remove, export and import saved parameters. To do this, click the Modify… button to reveal the management form
Delete
This removes the saved parameter from the available list of parameters. You might do this if you do not use the settings anymore.
Rename
Use this to change the name of a saved parameter. Do this if you want a name to be more meaningful.
Export
This option allows you to save the parameters in a text file that can be imported into another workbook. This option will prompt for a file name and file location. Use this in conjunction with Import
Import
This function allows you to import a set of saved parameters from a text file. This is used in conjunction with Export.
A worked example using Main table report
Example
In the following worksheet Main table report will be used to produce a list of ledger accounts that exclude headers and total accounts. The wizard will be used to create a Main table report.
Start by positioning the cell pointer in cell C6.
Choosing what to include
Open the wizard and complete the range tab as follows:
1. Ensure the Saved drop down says Auto-report
2. Choose Ledger chart of accounts as the data source
3. Select the Ledger account row and then type 40* in the Range entry box
4. Select Account type in the grid and click in the Range entry box
5. Type !Header, !Total
Output options
In this example, auto-report columns are used and there isn’t any need to change this. Ensure that the destination is the current cell, C12 and that the direction is Down.
Grouping and sorting
The report is not to be grouped; consequently the grouping tab appears as follows:
Applying sub-totals to the report
You can add sub-totals for each group level. Atlas will also include a grand total. There as no sub-totals in this report, thus:
Choosing a format for the output
In this case the worksheet defines the formatting and as such the formatting of the report will be left as None. The number style will remain as Comma.
1. Click the format tab
2. Click the Auto-format drop-down and select None
3. Ensure the Number style is Comma
Saving the settings of the report
Use the saved parameters to record the settings of the wizard. The named range acts as a place-holder and is used to position the report in the same spot the next time the saved settings are used.
1. Click the Save tab
2.
Type
3. Type COA in the named range box
4. Click OK or Apply to run the report.
The worksheet now appears as follows:
Refreshing a Main table report
Using the wizard and saved parameters
This method uses the wizard and saved parameters to refresh the report. You must have created the report in the first place and you must have also defined saved parameters too.
1. Open the Main table report wizard
2. Click the Saved drop-down to reveal the list of saved parameters
3. Select the saved parameter of the report you want to run
4. Click OK
Using the refresh worksheet and refresh all options
These options allow you refresh all reports that have saved parameters for the worksheet and the workbook.
1. Open the Dynamics AX menu from the Microsoft Excel menu bar
2. Choose Refresh sheet or Refresh all