Creating Cascading List Metrics

Using the List data type, Process Designers can create a cascading metric, or constrained metric, that displays different groups of list items depending on a data value. Creating the cascade requires a combination metrics: a "reference" metric, whose list options are all available, and a "restricted" metric, whose list options are limited to the values related to the reference metric's value.

For example, you could create the cascading metrics Business Unit, Brand, and Product Line, such that the Business Unit selection limits the Brand list options to the brands that are appropriate to for the currently selected Business Unit, and so on. In this way you can create a cascade, or series, of metrics that will present the appropriate set of choices at each level of the cascade.

Metric Cascade Example

Creating a cascading list metric is the same as creating a normal List metric; however, instead of creating a predefined list, either select columns from a reference table or write a query that constrains the returned values using a query field code in a WHERE clause.

Using a reference table to constrain the list allows you to create the constrained list without a custom reporting view and without needing knowledge of SQL.

To create a cascading list metric using a reference table:

Note: This process assumes that you have already created the "reference" metric, and its values are also defined in a column within a reference table.

From the System menu, select Content Sources > Metrics.

To narrow the metric list, search by the metric name, system name, or category.

Do one of the following:

To add a new metric - Click Add New in the upper right corner of the page.
To edit an existing metric - Click the name of the metric to open it for editing.
To create a metric based on an existing metric - Click in the Copy column to create a copy that can be used as a base to build a new metric.

Create the list metric using the Reference Table option in the List Source field.

Only reference tables set as Available to Metrics are available for selection.

Create or select a reference table and indicate the column within the table that contains this metric's list selections.

Only reference table columns with the data type set as String are available for selection.

In the Filter fields, select the reference table column that is used by the "reference" metric and will be used to restrict the list, and enter a query code that defines the value used for the restriction.

The value entered for the field can be one of the following:

A query code that references another metric or a matrix metric within the project. Query codes that reference a metric or matrix metric use the {*QME:<metricsystemName>*} format.
A query code that references project metadata, such as {*QMD:ProjectAccessGroup*}. Query codes that reference metadata use the {*QMD:<metadataName>*}. See Query Field Codes Reference for a list of valid metadata query codes.

Additional filters can be applied as necessary.

Example

For example, a user wants to categorize all of their Accolade projects by region and country that they have business assets in. The user has created a reference table called Region List, which contains a column list of regions and a corresponding column list of countries, and has created the list metrics Region and Country to use in Accolade projects. Both metrics use the Region List reference table as a list source, and use the corresponding table columns to define the list options that can be selected when creating projects. Additionally, to minimize setup errors, the user wants the list of countries available for selection to be restricted by the value selected for the region.

To set up the reference table, the user has included the following rows for each metric combination available for selection.

Cascading List Metrics - Reference Table Setup Example

For this example, the user wants Region to be the "reference" metric. To set up the Region metric, the user selects Region List as the List Source and Region as the Column to use as the metric's list value options.

Cascading List Metrics - Metric Setup Example

For this example, the user wants the Country metric to be the "restricted" metric. To set up the Country metric, the user selects Region List as the List Source and Country as the Column to use as the metric's list value options, but must add an additional filter in order to restrict the available list selections based on the Region value. In the Filter fields, the user selects Region as the column to be used as the filter to restrict the list, and enter {*QME:EX_Region*} as the value of this filter.

As entered, the filter and query expression combination states that Country values that have a matching Region value should be included in the list of options available for selection.

Cascading List Metrics - Restricted Metric Setup Example

To further illustrate this: in the example below, the user has selected North America as the Region value. Based on the applied filtering, the available country selections are restricted to Country values that have corresponding North America Region values (which are Canada, USA, and Mexico, based on the table setup).

Cascading List Metrics - Project Setup Example

Note that in the example above, the user could have created and applied additional list metrics, for example creating a City metric in the reference table whose list options can be restricted both by applying filters both to the Region value {*QME:EX_Region*} and to the Country value {*QME:EX_Country*}.

Click Create to create the new metric or Apply to save changes to an existing metric.

To create a cascading list metric using a query:

From the System menu, select Content Sources > Metrics.

To narrow the metric list, search by the metric name, system name, or category.

Do one of the following:

To add a new metric - Click Add New in the upper right corner of the page.
To edit an existing metric - Click the name of the metric to open it for editing.
To create a metric based on an existing metric - Click in the Copy column to create a copy that can be used as a base to build a new metric.

Create a list metric using the Query option in the List Source field.
Create or select a query that uses a query field code included in a WHERE clause to restrict the list.

At the highest level, a SQL query for a cascading list looks like:

SELECT Child Column
FROM View
WHERE Parent Column = {*QME:<metric system name>*}

Example

Consider the following SQL query that uses the GlobalRegions reference table, and refines the city selections based on the selections made for the region, country, and state. The city list only displays cities that are within the region/country/state area:

SELECT Distinct City
FROM CRV_Ref_GlobalRegions
Where Region = '{*QME:qblRegion*}' AND Country
= '{*QME:qblCountry*}' AND State
= '{*QME:qblState*}'

The top level of the cascade can be a list metric that is either populated by a query or defined manually.

Click Create to create the new metric or Apply to save changes to an existing metric.

Notes:

Administrators can view existing metric definitions, but cannot add or modify metrics.
It is not necessary for the parent metric in a cascade to appear on the same page as the child metric. The constraint is based on the selected value of the parent metric as found in the database.
You can create a constrained metric that is not part of a cascade. The query is constrained by metadata rather than by another metric.
If a cascading metric is selected as a filter metric, it does not cascade on the page where it is used as a filter. All of its list items display as if it were an ordinary list.
If a query contains a reference to a RVP_ or CRVP_ reporting view, the result displays [Unavailable] when used on an external Idea Submission form. External users do not have the security rights to see the data contained within those views.
In Innovation Planning and Roadmapping, a cascading list metric included in a matrix does not enforce the cascade. For example, if there is a state selection that restricts the cities available for selection, the city list shows all cities and does not filter to the selected state.
Idea Submission (as a part of Accolade Ideation), Innovation Planning and Roadmapping are optional Accolade components that you may not have access to. To implement these solutions, contact Sopheon Customer Support.

Learn More: