Home >

AX2236

Using a data table as the source table for multipass processing

When using "basic mode" to define multipass settings, the source table is determined automatically based on your selected source column. Because basic mode only allows selecting columns from reference tables, the source table is always a reference table, such as ACCT or DEPT. However, in some cases you may want to use a data table as the source table.

For example, imagine that you want to perform multipass processing by department. In basic mode you select DEPT.DEPT as the source column, which means that the report will be processed once for each department in the DEPT table. Depending on the data in the data tables that the report queries, this may result in processing the report for departments that have no data.

Imagine that the report queries data in the Plan2018 table. The DEPT table contains department 3200, but this is a newly-added department that was not part of the 2018 planning process, and therefore has no records of data in the Plan2018 table. When department 3200 is processed as part of the multipass process, it will return no data for that report.

This may be the desired result—you may want to process the full list of departments, even if a few departments may not have data in the current context. Reference tables are used by default in basic mode so that you can be certain that every item was included, and because this is the most common use case. However, if you only want to process the items that are relevant to the data being queried, you can specify a data table as the source table instead.

Continuing the example, you could use advanced mode settings to specify Plan2018 as the source table instead of DEPT. Although the source column and grouping level is still DEPT.DEPT, because the multipass list of items is now being generated from the data table, the multipass process will only process departments that are found in the Plan2018 table. While the DEPT table might have 100 departments, the Plan2018 table might have data for only 90 of those departments. When Plan2018 is the source table, the multipass process will result in 90 passes instead of 100 passes.

TIP: You can see exactly how many items will be processed based on the current multipass settings by using Preview Multipass List.

Now imagine that the report queries two tables, GL2018 and GL2017, and you want to use multipass processing on this report by department. The easiest and most common way to ensure that the report is processed for all relevant items is to use DEPT as the multipass source table. However, if you do not want any no-data passes, but you want to ensure that the report is processed for every department that has data in either GL2018 or GL2017, then you can do the following:

• Set the source table to one of the data tables. For example: GL2017.

• Specify two columns for the source columns. For example: DEPT.DEPT and any data column from the GL2018 table.

DEPT.DEPT is evaluated against the source table by default, and includes all of the departments that have data in the GL2017 table. By including any data column from the GL2018 table, the multipass list also includes any departments that have data in the GL2018 table.