AX1690

Use Column List report parameters

When you use a Column List report parameter, you define a list of table columns to allow the report user to select a column. The user's selected column can then be applied to the report in one of the following ways:

The selected column can be applied to the report as a row dimension. This allows the report user to dynamically change the row dimension for the report so that the same report can be used to view data by different dimensional groupings such as department, region, or entity. To do this, you must configure the report with a dynamic column for the row dimension and then link the dynamic column to the Column List report parameter.
The selected column can be applied to a Column Value parameter. This allows the report user to select a value or values from the column to be dynamically applied as a report filter and change the data shown in the report. To do this, you must configure the report with a Column Value parameter and then link the Column Value parameter to the Column List parameter.

When viewing the report with a Column List report parameter, the list of columns appears in a dropdown using the header text as defined for the column.

Define a Column List report parameter

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

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

The new parameter is added to the All Parameters pane along the left side. The properties for the parameter nappear 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.

NOTE: If the Column List parameter is linked to a dynamic column, the parameter must have a value to refresh data regardless of whether the parameter is configured, as required.

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

Item	Description
Selected columns	The selected columns for the parameter. Click Edit to select columns in the Select Columns dialog. Use the table treeview in the left pane of the dialog to locate the columns 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, drag the column to the middle Selected Columns pane. For each selected column, define Header text in the right Column Properties pane. This header text appears in the Column List dropdown when selecting a column from the list. If the Column List parameter is linked to a dynamic column, this text is also used as the header text for the dynamic column within the report grid. When the columns appear to report users in a dropdown, they appear in the order listed here (top to bottom). To reorder columns, click the drag handle in the left side of the column box and then drag and drop the column to a new location within the list. After selecting and configuring the columns, click OK to return to the Report Builder. The selected columns appear in the Selected columns box. NOTE: If the primary table is a data table and a 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 valid to use as a row dimension and so that any filter resulting from the column is applied as a general filter affecting all tables that look up to the dimension table.
Default value	Optional. The default value for the parameter. When a report user opens the report, the parameter uses this value by default. You can select any of the columns in the Selected columns list to use as the default value or you can leave it blank to have no default value.

Item

Description

Selected columns

The selected columns for the parameter. Click Edit to select columns in the Select Columns dialog.

Use the table treeview in the left pane of the dialog to locate the columns 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, drag the column to the middle Selected Columns pane.
For each selected column, define Header text in the right Column Properties pane. This header text appears in the Column List dropdown when selecting a column from the list. If the Column List parameter is linked to a dynamic column, this text is also used as the header text for the dynamic column within the report grid.
When the columns appear to report users in a dropdown, they appear in the order listed here (top to bottom). To reorder columns, click the drag handle in the left side of the column box and then drag and drop the column to a new location within the list.
After selecting and configuring the columns, click OK to return to the Report Builder. The selected columns appear in the Selected columns box.

NOTE: If the primary table is a data table and a 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 valid to use as a row dimension and so that any filter resulting from the column is applied as a general filter affecting all tables that look up to the dimension table.

Default value

Optional. The default value for the parameter. When a report user opens the report, the parameter uses this value by default.

You can select any of the columns in the Selected columns list to use as the default value or you can leave it blank to have no default value.

IMPORTANT: The columns selected for the Column List parameter must be valid within the context of how you intend to use the parameter. If the parameter is with a dynamic column, all of the selected columns must be valid as row dimensions within the report configuration. If the parameter is used with a Column Value parameter, all of the selected columns must be valid to be used as filters within the report configuration. If a column in the list is not valid for use as a row dimension or a filter, an error message appears when you attempt to apply that parameter selection to the report. As the report designer, test your parameters to verify that they work as intended within the report.

Use a Column List parameter with a dynamic column

To use a Column List parameter to dynamically change the row dimension of the report, you must:

Add a dynamic column as a row dimension for the report.
Link the Column List parameter to the dynamic column.

This configuration is only possible when using row dimensions, meaning the report rows are dynamically generated based on the row dimension columns. If you are using a fixed row structure in the report, the option to add a dynamic column is not available.

Web reports can use one or more row dimension columns. When using dynamic columns, all of the row dimensions can be dynamic, or you can have a mix of dynamic and fixed row dimensions.

In the Report Builder, select the Build tab.
Click the plus icon + over the Row Dimensions box, and then select Add Dynamic Column.

A column box labeled Dynamic is added to the Row Dimensions box.
In the right Column Configuration pane, from the Dynamic column parameter list, select the Column List parameter to use with the dynamic column. Parameters are listed by name as defined on the Parameters tab.

By default, Axiom Budget Planning and Performance Reporting automatically selects the first available Column List parameter and uses the default value defined for that parameter. If the parameter does not have a defined default value, the Report Builder uses the first column in the list of columns defined for the parameter. This is because the Report Builder must be able to associate the dynamic column with an actual table column to refresh data.

If you do not define any Column List parameters or if the Column List parameter does not have a defined list of columns, you cannot refresh the report with data until this issue is resolved.
Complete the remaining Column Configuration properties, as needed. Note the following:
- The Header property and the related header properties on the Advanced tab are not available for dynamic columns. The column header text for each column in the column list is defined when configuring the Column List parameter.
- You can optionally enable Show description and select a Description display format for the dynamic column. These options apply if the selected column for the Column List parameter has an associated description column.

The following example shows a web report configured with a dynamic row dimension column linked to a Column List report parameter. The grid is refreshed with data using the default column specified for the report parameter.

NOTE: In the report canvas, the dynamic column appears in the grid using the header text Dynamic. This only occurs in the Report Builder so that you know which column is the dynamic column. When previewing or viewing the report, the dynamic column uses the header text defined for the currently selected column as defined in the Column List parameter configuration.

In this example, the Column List report parameter lets you select from three different grouping levels—Dept.Dept, Dept.WorldRegion, or Dept.VP. The report refreshes with data and uses the selected column as the row dimension.

Design considerations for the report user experience

When using a dynamic column with a Column List parameter, you must decide whether you want the report to refresh using a default column selection or if you want to require the user to make a selection before the report refreshes with data.

If the Column List parameter does not have a specified default column, when a user opens the report, it does not refresh with data and instead shows the message "Waiting for input." The user must select a value for the Column List parameter and apply before the report is refreshed with data.
If the Column List parameter has a specified default column, when a user opens the report, it refreshes with data using the default column. The user can optionally change the selected column to refresh the report using a different row dimension.

NOTE: If the Column List parameter that drives the dynamic column does not have a default value, the report cannot be exported or shared because it does not have a column to use as the row dimension. Although report parameter selections made by users are not honored when exporting or sharing web reports, the default value is honored for the dynamic column when exporting or sharing the report.

Design considerations for drilling

If you use a dynamic column and also enable directed drilling for the report, you must ensure that all of the drill columns are compatible with the Column List columns. If the user can select a column for the Column List parameter that is not compatible with the drill columns, an error occurs when the user attempts to drill the report.

Alternatively, you can enable drilling and use key column drilling, which continues to work as long as all of the columns defined the Column List parameter are valid as row dimension columns.

Use DateParts in a Column List parameter

Use DateParts when setting parameters for a ColumnList. Click Parameters from your report and click Add to add a Column List.

In the Column List Configuration section, click Edit to select a column and then drag the column name to the Selected columns section. Use the Date part to retrieve menu to select a date part and then click OK.

Use a Column List parameter with a Column Value parameter

To use a Column List parameter to populate the source column for a Column Value parameter, you must:

Define a Column Value parameter.
Configure the 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 Value parameters and how the selected values are applied as report filters, see Use Column Value 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.