AX1692

Using Column Value report parameters

When you use a Column Value report parameter, you 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. You can 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 are displayed in a drop-down list:

Column values are displayed with descriptions if applicable.
Users can type text into the drop-down list 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.

Example Column Value parameter in the Report Parameters panel

Defining a Column Value report parameter

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

To create a Column List parameter:

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

The new parameter is added to the All Parameters panel along the left-hand side. The properties for the parameter now display 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). You can edit this name as needed. The parameter name does not display to report users; it is solely for use in the Report Builder. You should give the parameter a name that indicates its purpose.
Display prompt	The prompt text to display to report users in the Report Parameters panel. By default, this is set to Report Parameter (Type). You can edit this name as needed. You should 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, then the report does not refresh with data until this parameter has a value. The message "Waiting for input" displays in the report grid until all required parameters have a value. In the Report Parameters panel, the Apply button does not become available until all required parameters have a value. Additionally, required parameters cannot be cleared. If disabled (default), then users 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). You can edit this name as needed.

The parameter name does not display to report users; it is solely for use in the Report Builder. You should give the parameter a name that indicates its purpose.

Display prompt

The prompt text to display to report users in the Report Parameters panel. By default, this is set to Report Parameter (Type). You can edit this name as needed.

You should 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, then the report does not refresh with data until this parameter has a value. The message "Waiting for input" displays in the report grid until all required parameters have a value.

In the Report Parameters panel, the Apply button does not become available until all required parameters have a value. Additionally, required parameters cannot be cleared.
If disabled (default), then users 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-hand panel 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 panel to search by table names or by column names. Once you locate a column that you want to use, select it and then click OK. The selected column name now displays 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, then select the desired Column List parameter to use as the source. This means that the user will first select a column from the designated Column List parameter, then the user can select one or more values from that column using the Column Value parameter. The Column Value parameter will not be active until the Column List parameter has a selected value. If the Column List parameter has an assigned default value, this value will be 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 drop-down list. 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, then this setting is ignored and the column values will display 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, then an error message will display when the user attempts to apply the parameter selection to the report. As the report designer, you should test your parameters to verify that they work as intended within the report.

Example Column Value parameter allowing report users to filter the report by the source column

Using DateParts with ColumnValue parameters

You can use DateParts when setting parameters for a ColumnValue. Click Parameters from your report and click Add to add a Column Value.

Parameters view

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

Specifying column and date part

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

Report with column value drop-down list

Using a Column List parameter with a Column Value parameter

In order 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. First the user will select a column from the Column List parameter, then the user will select a value or values in that column from the Column Value parameter.

Example Column Value parameter using a Column List parameter to provide the source column

Example end user experience to choose the filter column and values

For more information on how to configure Column List parameters, see Using 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 will not become active in the Report Parameters panel until a value has been selected for the Column List parameter. The Column Value parameter will then become active and allow the user to select a value or values from the column selected for the Column List parameter.

NOTE: Because of this automatic dependency, you should make sure 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 or not:

If the Column List parameter does not have a specified default column, then the Column Value parameter will be disabled until a value is selected 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, then 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's 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 panel. For more information on the difference between table-specific filters and general report filters, see Filtering 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 will be created such as Dept.Dept=20000.
Multiple selection: A filter will be created such as Dept.Dept IN (20000,21000,45000).

To make your variable narrow the list displayed 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.

Keep in mind 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 will occur 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, then the table-specific filter will have no effect and no error will occur.

All report parameters should be tested by the report designer to ensure that they are working as intended, before rolling out the report to end users.