Working with column filter transformations

You use column filter transformations to add calculated columns and filters to the source query without modifying the source query itself. You can also choose to exclude columns from the result query.

About this task

For example, you have a query that displays staff data for your company and that this query uses a calculated column to enumerate the rows. When you apply a filter to this query, some rows are excluded from the result set. You can use a column filter query to add another calculated column that displays the row numbers of the result query and not the source.

The following table shows a query with calculated column which returns row numbers:

Deptname Deptnumb Division Location Manager Row
HEAD OFFICE 10 CORPORATE NEW YORK 160 0
NEW ENGLAND 15 EASTER BOSTON 50 1
MID ATLANTIC 20 EASTER WASHINGTON 10 2
SOUTH ATLANTIC 38 EASTER ATLANTA 30 3
GREAT LAKE 43 MIDWEST CHICAGO 100 4
PLAINS 51 MIDWEST DALLAS 140 5

If you apply a filter to this query, some rows will be dropped from the result set. But the calculated column will return the same row numbers as in the source query, as shown in the following table:

Deptname Deptnumb Division Location Manager Row
HEAD OFFICE 10 CORPORATE NEW YORK 160 0
GREAT LAKE 43 MIDWEST CHICAGO 100 4
PLAINS 51 MIDWEST DALLAS 140 5

You can use column filter query to add another calculated column that will show row numbers of the result query, as shown in the following table:

Deptname Deptnumb Division Location Manager Row Row2
HEAD OFFICE 10 CORPORATE NEW YORK 160 0 0
GREAT LAKE 43 MIDWEST CHICAGO 100 4 1
PLAINS 51 MIDWEST DALLAS 140 5 2

To configure a column filter transformation:

Procedure

  1. In the Analytical query structure, select a column filter transformation node.
    In the Item editor pane, you can view the items of the column filter transformation. The columns from the source data set are displayed in the Result Columns list.
  2. Optional: If the selected column filter transformation is not populated yet, add a query or table into the <none> node. For more information, see Populating analytical query transformations.
  3. In the Result Columns list, use the Include All Columns check box to select or deselect all available columns.
  4. To apply sorting conditions to the columns that you included to the Result Columns list, specify values in the Sort Order and Key Sequence columns.
    • In the Sort Order column, double-click the value and specify whether the column is used for sorting data in the query result set. Select the Ascending, Descending, or None sort order from the list for the appropriate column.
    • In the Key Sequence column, double-click the value and specify the sequence in which the sort order conditions are applied to the columns. Select the sequence number from the list for the columns with sort order conditions (the column that has the key sequence number ‘1’ is sorted first and so on).
  5. Click the Design tab. On the Design page you can manage prompts, calculated columns, and filters.
  6. In the Prompts pane, you can add and edit prompts.
  7. Use the Calculated Column window to add calculated columns. Click in the Dynamart pane, and then select Add Calculated Column.
  8. Use the arrow buttons in the Dynamart pane to change the order of query columns. This column order is applied only when the query is used as a data source in the other application parts like dashboards or analytical queries.
  9. Use the Dynamart Filter wizard to create conditional expressions that control the contents of query results. In the Transformations pane right-click the Filter node and select Edit Filter.
    Note: When you use the Transformations pane controls the filtering is applied in the application, it does not change SQL text executed in the database.
    For more information about adding calculated columns and building filters, see:
  10. Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.