AX1718

Defining data sources for Data Grid components

Web reports that use Data Grid components must have at least one data source. The data source specifies a primary table (such as GL2019 or Dept) and an optional filter. The data source can then be assigned to various Data Grid components in the web report to determine the data available to that component.

Data sources are defined separately from the Data Grid component properties, so that they can be controlled centrally. For example, you can have two or three Data Grid components that all use the same data source. If you decide you need to change the data source properties, you can do it in one place instead of needing to modify all of the components individually.

NOTE: If the report does not contain a Data Grid component, then you do not need to define a data source. Currently, the Data Grid component is the only component that uses data sources.

Managing data sources for a web report

Data sources are defined on the Data Source panel of the Report Designer.

Example Data Source tab

When you first create a new web report, it has one data source named Datasource 1 by default. Any Data Grid components in the report are configured to use this initial data source by default. You can edit this data source to define a primary table and optional filter, and change its name.

To edit a data source:

Select the data source name from the drop-down list at the top of the panel.

The panel updates to show the properties for the selected data source. You can edit any of the data source properties as needed.

NOTE: If refresh variable dependencies have been created for the data source, the primary table cannot be changed. You must delete the dependencies first.

IMPORTANT: If you change the primary table for an existing data source, any components that use the data source may now have invalid configurations. For example, existing columns in a Data Grid component may now be invalid in the context of the new primary table. If that is the case, errors will occur when attempting to render the component. You must edit the component to remove or change any invalid settings.

To add a data source:

Click Create New Data Source.
In the Create New Data Source dialog, define the data source properties as needed. You must define a name and a primary table.
Click OK to save the new data source.

Remember, just creating a data source does not have any impact on the web report. You must assign the data source to one or more components in order to display data from the data source in the web report.

To delete a data source:

Select the data source name from the drop-down list at the top of the panel.
Click the Delete Data Source icon to the right of the name. Note that the icon does not display if there is only one data source, since that data source cannot be deleted.

If the data source is assigned to one or more components, the data source cannot be deleted. You must first edit the components to use different data sources, and then you can delete the data source.

Data source properties

Item	Description
Name	The name of the data source. By default, the first data source is named Datasource 1. All newly created data sources start with the name Datasource 2, Datasource 3, and so on. When assigning data sources to components, you choose from a list of these data source names. You can change the data source name as needed. It is recommended to give it a name that is indicative of the data that can be queried using the data source. For example, you might name the data source something like "Actuals Data" or "Department Data". You might also want to use the name of the primary table as the data source name.
Primary Table	The primary table for the data source. Click the table icon to select a table. Each data source must have a primary table. In the Select a Primary Table dialog, the tables available for selection are determined as follows: If the report does not have an associated file group, then you can select any table in the Table Library. If the report has an associated file group, then the table list is limited by default to showing tables associated with the file group (and related lookup tables). Table variable names display in parentheses after the table names. If you select a table with a variable name, the table reference is stored using that variable name, so that it can dynamically change as the variable value changes. If you want to use a table that is not associated with the file group, enable Show all tables at the bottom of the dialog. The dialog updates to show all tables. The primary table determines the valid list of tables and columns for use in components that use the data source, and thereby determines the data that can be displayed in the components. If the primary table is a data table, the valid tables include the primary table itself, reference tables that the primary table looks up to, and other data tables that share validated keys with the primary table. If the primary table is a reference table, the valid tables include the primary table itself, reference tables that the primary table looks up to, and data tables that look up to the primary table.
Filter	Optional. Specify a filter criteria statement to limit the data available to components that use this data source. Click the filter icon to create a filter using the Filter Wizard. The Filter Wizard is limited to only showing valid selections for the primary table. When data is queried into a component that uses the data source, this filter is automatically applied (in addition to the user's applicable table security filters). NOTE: If the report has an associated file group, the tables in the Filter Wizard show with table variable names (as applicable), but the filter is not stored using these variable names. The filter does not update for changes to the table variable values.

Item

Description

Name

The name of the data source. By default, the first data source is named Datasource 1. All newly created data sources start with the name Datasource 2, Datasource 3, and so on. When assigning data sources to components, you choose from a list of these data source names.

You can change the data source name as needed. It is recommended to give it a name that is indicative of the data that can be queried using the data source. For example, you might name the data source something like "Actuals Data" or "Department Data". You might also want to use the name of the primary table as the data source name.

Primary Table

The primary table for the data source. Click the table icon to select a table. Each data source must have a primary table.

In the Select a Primary Table dialog, the tables available for selection are determined as follows:

If the report does not have an associated file group, then you can select any table in the Table Library.
If the report has an associated file group, then the table list is limited by default to showing tables associated with the file group (and related lookup tables). Table variable names display in parentheses after the table names. If you select a table with a variable name, the table reference is stored using that variable name, so that it can dynamically change as the variable value changes.

If you want to use a table that is not associated with the file group, enable Show all tables at the bottom of the dialog. The dialog updates to show all tables.

The primary table determines the valid list of tables and columns for use in components that use the data source, and thereby determines the data that can be displayed in the components.

If the primary table is a data table, the valid tables include the primary table itself, reference tables that the primary table looks up to, and other data tables that share validated keys with the primary table.
If the primary table is a reference table, the valid tables include the primary table itself, reference tables that the primary table looks up to, and data tables that look up to the primary table.

Filter

Optional. Specify a filter criteria statement to limit the data available to components that use this data source. Click the filter icon to create a filter using the Filter Wizard. The Filter Wizard is limited to only showing valid selections for the primary table.

When data is queried into a component that uses the data source, this filter is automatically applied (in addition to the user's applicable table security filters).

NOTE: If the report has an associated file group, the tables in the Filter Wizard show with table variable names (as applicable), but the filter is not stored using these variable names. The filter does not update for changes to the table variable values.

Assigning a data source to a Data Grid component

When you first create a new web report from a template, any Data Grid components in the report are automatically assigned to the default data source Datasource 1. Once you have configured the default data source, the Data Grid components are now ready to be configured if you want them to use that data source. However, if you have created additional data sources and you want the Data Grid components to use different data sources, then you must change the data source assignment in the component properties.

Data Grid components are configured on the Components panel of the Report Designer.

To assign a data source to a Data Grid component:

In the Components panel, select the Data Grid component that you want to configure. You can use the drop-down list at the top of the panel to select the component, or you can select the data grid directly in the web report canvas.
In the Component Properties section, use the Data Source list to select the desired data source.

Once a data source has been specified for a Data Grid component, the grid is now limited to using table columns that are valid within the context of the primary table for the data source. Dialogs such as the Add Columns dialog are automatically filtered to show valid selections.

If you change the data source for a Data Grid component after columns have already been added to the grid, the existing columns may now be invalid in the context of the new data source. If the data grid has invalid columns, they must be removed in order to render the grid without error. However, if the existing columns are still valid in the new data source, then the data grid will continue to work after changing the data source.

Defining refresh variable dependencies for data sources

Data sources can be dependent on one or more refresh variables, so that users can filter the data in the associated Data Grid components on demand.

In order to create a dependency, the refresh variable must already be created on the Refresh Variables panel. For more information on creating refresh variables and how dependencies work, see Configuring refresh variables for web reports.

Refresh variable dependencies for data sources are defined in the Refresh Variable Dependencies section of the Data Sources panel. If the currently selected data source already has defined dependencies, they are listed in this section. Dependencies are listed by the name of the variable they are associated with.

Example refresh variable dependencies for a data source

To create a refresh variable dependency:

Select the desired data source from the drop-down list at the top of the panel. If your report only has one data source, then it is selected by default.
In the Refresh Variables Dependencies section, click Add Dependency.

In the Create a Dependency dialog, complete the following:

Item	Description
Variable	Select the name of the variable that you want to associate with this data source. You can select any variable defined on the Refresh Variables tab, except for Year variables. Year variables do not use dependencies.
Table Reference	This field only displays if the primary table of the data source has multiple column paths to the variable. In this case, you must select the column path that you want to use for this variable. If the primary table only has one column path to the variable, then this field does not display. You do not have to configure the column association between the data source and the variable because it is automatically set.

Item

Description

Variable

Select the name of the variable that you want to associate with this data source. You can select any variable defined on the Refresh Variables tab, except for Year variables. Year variables do not use dependencies.

Table Reference

This field only displays if the primary table of the data source has multiple column paths to the variable. In this case, you must select the column path that you want to use for this variable.

If the primary table only has one column path to the variable, then this field does not display. You do not have to configure the column association between the data source and the variable because it is automatically set.

NOTE: If the primary table of the data source does not contain a path to the value column for the variable, the dependency cannot be created because there is no way to filter the data source based on the variable. In this case, the dialog displays a message "No dependencies available" after you select the variable name.

Click OK to create the dependency.

Refresh variable dependencies cannot be edited once they are created. They can only be deleted. To delete a dependency, hover your mouse over the dependency in the list and then click the Delete icon .

NOTE: If you change the value column or hierarchy for the refresh variable after you create the dependency, the dependency will still work as long as there is only one path to both the original and the new value column or hierarchy (and assuming the new selection is still valid against the data source). However, if the original dependency had a table reference, or if the new value column needs a table reference, then you must delete and re-create the dependency in order for the variable to work.