AX1692

Use Column Value report parameters

When you use a Column Value report parameter, specify a column from which the user can select one or more values. The user's selected values for the column are then applied to the report as a report filter. This allows the user to dynamically change the data shown in the report.

For example, you may design a report that is intended to display financial results for a selected department or entity. Create a Column Value report parameter that uses the department or entity column. The user can select the departments or entities that they want to view and then apply the parameter values to refresh the report with the selected data.

When users view the report with a Column Value report parameter, the column values appear in a dropdown:

Column values appear with descriptions, if applicable.
Users can enter text into the dropdown to filter the list and find a specific value.
Users can select one value from the list or multiple values, depending on the parameter configuration.

Define a Column Value report parameter

Define Column Value report parameters on the Parameters tab of the Report Builder. You can define as many Column Value parameters as needed for the report.

In the Report Builder, select the Parameters tab.
Click Add > Column Value.

The new parameter is added to the All Parameters pane along the left side. The properties for the parameter appear in the main area.

Complete the following general properties for the parameter:

Item	Description
Name	The name of the parameter. By default, this is set to Report Parameter (Type). Edit this name, as needed. The parameter name does not appear to report users; it is solely used in the Report Builder. Give the parameter a name that indicates its purpose.
Display prompt	The prompt text to show to report users in the Report Parameters pane. By default, this is set to Report Parameter (Type). Edit this name, as needed. Define display prompt text that helps users understand the purpose of the parameter. For example, "Select a department."
Required	Specifies whether the parameter is required. If enabled, the report does not refresh with data until this parameter has a value. The message "Waiting for input" appears in the report grid until all required parameters have a value. In the Report Parameters pane, Apply does not become available until all required parameters have a value. Additionally, you cannot clear required parameters. If disabled (default), you can optionally leave this parameter blank (unset) when applying parameter values. The report can refresh data without this parameter.

Item

Description

Name

The name of the parameter. By default, this is set to Report Parameter (Type). Edit this name, as needed.

The parameter name does not appear to report users; it is solely used in the Report Builder. Give the parameter a name that indicates its purpose.

Display prompt

The prompt text to show to report users in the Report Parameters pane. By default, this is set to Report Parameter (Type). Edit this name, as needed.

Define display prompt text that helps users understand the purpose of the parameter. For example, "Select a department."

Required

Specifies whether the parameter is required.

If enabled, the report does not refresh with data until this parameter has a value. The message "Waiting for input" appears in the report grid until all required parameters have a value.

In the Report Parameters pane, Apply does not become available until all required parameters have a value. Additionally, you cannot clear required parameters.
If disabled (default), you can optionally leave this parameter blank (unset) when applying parameter values. The report can refresh data without this parameter.

In the Column List Configuration section, complete the following parameter-specific properties:

Item	Description
Source column type	Select one of the following to determine the source of the column for the Column Value parameter: Static column: Select this option if you want to select a specific column to use as the source column. Dynamic column: Select this option if you want to dynamically use the selected column for a Column List parameter as the source column.
Selected column	If the specified Source column type is Static column, click the Edit link to select a column from the Select Column dialog. Use the table treeview in the left pane to locate the column that you want to use. You can select any column from the primary table, a dimension table, or a related table. You can use the search boxes at the top of the pane to search by table names or by column names. After you locate a column that you want to use, select it and then click OK. The selected column name appears under the Selected column header. NOTE: If the primary table is a data table and the selected column looks up to a dimension table for the primary table, the column reference is automatically elevated so that it uses the lookup dimension table instead of the source table. For example, if the primary table is GL2022, and you select either GL2022.Acct or BGT2022.Acct, the column reference is elevated to Acct.Acct. This elevation is done so that the column is applied as a general filter affecting all tables that look up to the dimension table.
Selected parameter (dynamic column source)	If the specified Source column type is Dynamic column, select the preferred Column List parameter to use as the source. This means that the user first selects a column from the designated Column List parameter, then selects one or more values from that column using the Column Value parameter. The Column Value parameter is not active until the Column List parameter has a selected value. If the Column List parameter has an assigned default value, this value is used as the initial source column for the Column Value parameter when the report is opened.
Allow multi-select	Specifies whether the parameter allows selecting multiple values from the column. By default, this is disabled, which means users can only select a single value at a time.
Description display format	The display format to use for the column values in the dropdown. By default, this is set to Description only. This setting applies when the column used by the Column Value parameter has an associated description column. If the column used by the parameter does not have an associated description column, this setting is ignored and the column values appears as is.

IMPORTANT: The column used by the Column Value parameter must be valid as a filter column for the current report configuration. If a column in the list is not valid for use as a filter column, an error message appears when the user attempts to apply the parameter selection to the report. As the report designer, test your parameters to verify that they work as intended within the report.

Use DateParts with ColumnValue parameters

Use DateParts when setting parameters for a ColumnValue. Click Parameters from your report and click to add a Column Value.

In the Column Value Configuration section, select Static column. In the Selected Column section, click Edit and the select the column to use. Then, select which date part to use, such as Full Date.

When you view the report, select a date to apply to the results of the report.

Use a Column List parameter with a Column Value parameter

To use a Column List parameter as the source for a Column Value parameter, you must:

Create and configure a Column List parameter.
Configure the Column Value parameter as dynamic and select the Column List parameter as the source.

In the following example, the Column Value parameter is configured to use the FilterCols Column List parameter. The user first selects a column from the Column List parameter and then selects a value or values in that column from the Column Value parameter.

For more information about how to configure Column List parameters, see Use Column List report parameters.

Design considerations for the report user experience

When a Column Value parameter is linked to a Column List parameter, the Column Value parameter is automatically dependent on the other parameter. This means that the Column Value parameter does not become active in the Report Parameters pane until a value has been selected for the Column List parameter. The Column Value parameter then becomes active and enables the user to select a value or values from the column selected for the Column List parameter.

NOTE: Because of this automatic dependency, ensure that the Column List parameter is ordered before (above) the Column Value parameter.

Additionally, you must decide whether you want the Column Value parameter to start with a default column:

If the Column List parameter does not have a specified default column, the Column Value parameter is disabled until you select a value for the Column List parameter.
If the Column List parameter has a specified default column, the Column Value parameter is enabled and uses the default column. The user can optionally select a different value for the Column List parameter to change the column used by the Column Value parameter.

How Column Value parameter selections are applied as filters

When a Column Value parameter selection is applied to the report, the filter is applied as follows:

If the source column is from a related table or if the source column is from the primary table when the primary table is a data table, the filter is applied as a table-specific filter.
Otherwise, the filter is applied as a general report filter.

NOTE: Because columns that look up to dimension tables are automatically elevated to the dimension table when the primary table is a data table, it is not possible to apply table-specific filters with these columns when using a Column Value parameter. For example, it is not possible to use BGT2022.Acct in a Column Value parameter because the column reference is always elevated to Acct.Acct and therefore applied as a general filter. In the majority of use cases involving columns that look up to dimension tables, the general filter is the intended filter.

The filters resulting from Column Value parameter selections are applied in the same way as report-level filters defined in the report configuration pane. For more information about the difference between table-specific filters and general report filters, see Filter data in web reports.

The syntax used for filters depends on whether the parameter allows single selection or multiple selection. For example, if the source column is Dept.Dept, filters are created as follows:

Single selection: A filter is created, such as Dept.Dept=20000.
Multiple selection: A filter is created, such as Dept.Dept IN (20000,21000,45000).

To make your variable narrow the list to only the departments used in a single table (for example, GL 2020), enter GL2020.Dept.Dept.

This filter statement is not visible to report users—the users only see their selected value(s) for the column.

Note the following:

It is possible that the source column used by the Column Value parameter is not valid as a filter column for the report depending on the report configuration. In this case, an error occurs when the user attempts to apply the parameter.
It is possible that the Column Value parameter uses a source column that is valid as a filter column for the report, however, the filter has no effect. For example, if the column is applied as a table-specific filter but the report does not use any columns from that table, the table-specific filter has no effect and no error occurs.

Test all report parameters using the report designer to ensure that they are working as intended before rolling out the report to users.