AX1701

Define 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, select a primary table as the base table for the report. This primary table then determines which other tables are eligible to include in the report based on lookup relationships. All table columns 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 Build tab, in the left data pane. You must select the primary table before dragging and dropping any table columns out to the grid.

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 new report, you are automatically prompted to select a primary table for the data context.

    Sselect a table from the dropdown directly or search for a table name. The search uses contains matching to return any tables that contain the search text within the table name. Tables that start with the search text are listed first, followed by tables that contain the search text anywhere in the table name. In the following screenshot, the text 2022 is used to search for tables with the year 2022 in the name.

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

After selecting a table, that table name appears in the Data Context box, and the data pane 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, 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, this section contains tables that look up to the reference table.

Expand these tables to view the columns and then drag and drop columns out to the report canvas so that you can use them as row dimensions or data columns.

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

  • GL2022: This node contains all columns in GL2022 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), use those downstream reference tables 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 BGT2022.

When you save the report, the data context is saved for that report and is reloaded into the data pane whenever you open the report in the Report Builder.

NOTE:
• When choosing the data context, the list of tables is automatically filtered to only show tables that you have access. If you have the Administer Tables security permission, all tables appear. 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 does not populate with data.
• Certain tables can be restricted from appearing 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.

Change 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 to 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 occurs when the Report Builder tries to refresh the grid in the report canvas. At this point, you can select a different primary table that is compatible (which may mean returning to the original primary table), remove the incompatible columns from the grid, or choose a different fixed row structure.

Other settings that must be compatible with the primary table include 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 occurs.

If you change the data context and save the report, the new primary table is saved for the report and is reloaded into the data pane whenever you open the report in the Report Builder.