AX1690

Using 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 lets the report user 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. In order 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 lets the report user select a value or values from the column to be dynamically applied as a report filter and change the data shown in the report. In order to do this, you must configure the report with a Column Value parameter, then link the Column Value parameter to the Column List parameter.

When you view the report with a Column List report parameter, the list of columns in a drop-down list, using the header text as defined for the column.

Example Column List parameter in the Report Parameters pane

Defining 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.

To create a Column List parameter:

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

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	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 appear 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	Prompt text to display to report users in the Report Parameters pane. 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" appears in the report grid until all required parameters have a value. In the Report Parameters pane, the Apply button does not become available until all required parameters have a value. Additionally, required parameters cannot be cleared. 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

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 appear 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

Prompt text to display to report users in the Report Parameters pane. 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" appears in the report grid until all required parameters have a value.

In the Report Parameters pane, the Apply button does not become available until all required parameters have a value. Additionally, required parameters cannot be cleared.
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 in order 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. Select Edit to select columns in the Select Columns dialog. Use the table treeview in the left-hand pane of the dialog to locate the columns 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. 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-hand Column Properties pane. This header text appears in the Column List drop-down when users select 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 are presented to report users in a drop-down list, they appear in the order listed here (top to bottom). To reorder columns, select the drag handle in the left-hand 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, to return to the Report Builder, select OK. 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 GL2020, and you select either GL2020.Acct or BGT2020.Acct, the column reference is elevated to Acct.Acct. This elevation is done so that the column is valid for 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 will use 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. Select Edit to select columns in the Select Columns dialog.

Use the table treeview in the left-hand pane of the dialog to locate the columns 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.
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-hand Column Properties pane. This header text appears in the Column List drop-down when users select 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 are presented to report users in a drop-down list, they appear in the order listed here (top to bottom). To reorder columns, select the drag handle in the left-hand 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, to return to the Report Builder, select OK. 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 GL2020, and you select either GL2020.Acct or BGT2020.Acct, the column reference is elevated to Acct.Acct. This elevation is done so that the column is valid for 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 will use 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 will be used with a dynamic column, then all of the selected columns must be valid as row dimensions within the report configuration. If the parameter will be used with a Column Value parameter, then 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, you should test your parameters to verify that they work as intended within the report.

Example Column List parameter with a list of columns and a specified default value

Using a Column List parameter with a dynamic column

In order 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, then 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.

To define a dynamic column:

In the Report Builder, select the Build tab.
Select 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-hand 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 Enterprise Decision Support 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 in order to refresh data.

If no Column List parameters are defined, or if the Column List parameter does not have a defined list of columns, then the report cannot be refreshed 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 will 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.

Example Report Builder with dynamic row dimension

NOTE: In the Report Canvas, the dynamic column displays 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 the user select from three different grouping levels—Dept.Dept, Dept.WorldRegion, or Dept.VP. The report will refresh with data and use the selected column as the row dimension.

Example Column List parameter providing column options for the dynamic row dimension

Example end user experience to select and change 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, then when you open the report, it will not refresh with data and instead displays the message "Waiting for input". You 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, then when you open the report, it refreshes with data using the default column. You can then 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, then 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 want to use a dynamic column and also enable directed drilling for the report, you must be careful to 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 you attempt to drill the report.

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

Using 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. First, you will select a column from the Column List parameter, then you 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 about how to configure Column Value parameters, and how the selected values are applied as report filters, see Using 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 will not become active in the Report Parameters pane until a value has been selected for the Column List parameter. The Column Value parameter will then become active and let you 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:

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. You can optionally select a different value for the Column List parameter to change the column used by the Column Value parameter.