AX1701

Defining the data context for a web report

The data context for a web report determines the overall pool of data that is eligible to be included in the report. To define the data context, you select a primary table as the "base" table for the report. This primary table then determines which other tables are eligible for inclusion in the report, based on lookup relationships. All table columns and filters used in the report must be compatible in the context of the primary table.

In the Report Builder, the primary table for the data context is specified in the left-hand Data Panel. You must select the primary table before you can drag and drop any table columns out to the grid.

To select a primary table for the data context:

There are two ways to select a primary table for the data context.

  • When you create a brand new report, you are automatically prompted to select a primary table for the data context.

    You can select a table from the drop-down list directly, or type into the box to search for a table name. The search uses "contains" matching to return any tables that contain the search text within the table name. In the following screenshot, the text 2020 has been used to search for tables with the year 2020 in the name.

  • If you are already in the Report Builder, then you can define or change the primary table using the Data Context box at the top of the Data Panel. Click the Edit icon to open the Select Table dialog (as shown in the previous screenshot).

Once you have selected a table, that table name is shown in the Data Context box, and the Data Panel populates to show a table tree in three expandable/collapsible sections:

  • TableName: The selected table and its columns. This table is the primary table.

  • Dimension Tables: Reference tables that the primary table looks up to. If the reference tables have lookups to other reference tables, these multi-level reference tables are accessible through the first-level reference tables.

  • Related Tables: The contents of this section depend on the type of table selected as the primary table.

    • If the primary table is a data table, then this section contains other tables that look up to one or more of the same reference tables as the primary table.

    • If the primary table is a reference table, then this section contains tables that look up to the reference table.

Example Data Panel with a defined data context

You can expand these tables to view the columns, and then drag and drop columns out to the Report Canvas area so that they can be used as row dimensions or data columns.

In this example, we have selected GL2020 as the primary table. GL2020 is a data table that looks up to reference tables Dept and Acct. The table tree is populated as follows:

  • GL2020: This node contains all columns in GL2020, as well as columns in the lookup tables Dept and Acct.
  • Dimension Tables: This node contains the lookup reference tables Dept and Acct. If the reference tables look up to other downstream reference tables (multi-level lookups), those downstream reference tables can be used through these tables.
  • Related Tables: This node contains other tables that also look up to Dept or Acct (or to a multi-level lookup through Dept or Acct). This may include tables such as GL2021, BGT2021, and BGT2020.

When you save the report, the data context is saved for that report and will be reloaded into the Data Panel whenever the report is opened in the Report Builder.

NOTES:  

  • When choosing the data context, the list of tables is automatically filtered to only show tables that you have access to. If you have the Administer Tables security permission, all tables will be shown. This means it can be possible to select a primary table where you do not have access to any of the data in the table. You can build the report but it will not populate with data.

  • Certain tables can be restricted from showing the in the Report Builder using the system configuration setting TablesRestrictedFromReportWriter. If a table that you have access to is not available, it has likely been restricted using this setting.

Changing the data context

You can change the data context freely until you have done either of the following:

  • Dragged and dropped columns out to the grid setup boxes in the Report Canvas
  • Selected a fixed row structure for use with the report (when using the Use fixed rows option in the Grid Configuration properties)

You can still change the data context if needed, but any newly selected primary table must be compatible with the table columns you have already added to the grid, and with the fixed row structure you have selected (if applicable). If the newly selected primary table is not compatible, an error will occur when the Report Builder tries to refresh the grid in the Report Canvas. At this point you have the choice of selecting a different primary table that is compatible (which may mean returning to the original primary table), or removing the incompatible columns from the grid, or choosing a different fixed row structure.

Other settings that must be compatible with the primary table include the Data Filter for the grid or for any of the columns, and any columns selected as drilling columns for a Directed drilling configuration. If you change the primary table and any of these settings are incompatible with the new primary table, an error will occur.

If you change the data context and save the report, the new primary table is now saved for the report and will be reloaded into the Data Panel whenever the report is opened in the Report Builder.