Filter item reference property

The Filter Item Reference property identifies the query item used when Reporting or Query Studio generates a filter. This property can help create more efficient queries by ensuring that a filter uses an indexed numeric column rather than a non-indexed string column.

For example, a report author wants to create a filter for the Country Name query item. You set the Filter Item Reference property to use Country Code instead of Country Name for any filter that uses the Country Name query item.

In another example, a report author wants to create a filter for the Country Code query item that displays in the Orders table. You want that filter to use the Country Code in the Country table because there are fewer rows to read in the Country table so you set the Filter Item Reference in the model to Country.Country Code.

This property is used by:

  • Query Studio to create a filter expression
  • The Build Prompt Page tool in Reporting to create a filter expression

Default: If no value is set, the property defaults to the name of the query item.

Using the filter item reference and use item reference properties

The values of the Filter Item Reference and Use Item Reference properties must be compatible. The value of the Use Item Reference property must be a type that is expected by the Filter Item Reference property. Otherwise, the report user may receive unexpected results. This may occur when a report user creates a filter without creating a prompt page.

In an example model, the Use Item Reference property is set to Employee Number and the Filter Item Reference property is Employee Name. In Reporting, a report author creates the following filter without creating a prompt page:

Reference.EmployeeName in ?parm1?

Reporting automatically generates prompts when you create a filter without creating a prompt page. Because the prompt is generated, Reporting uses the Prompt Info properties from the Employee Name query item in the Framework Manager model.

The Use Item Reference indicates that the values being passed to the filter are employee numbers. The Filter Item Reference is filtering data based on Employee Name. The filter is as follows: Reference].[Employee Name] in ("1", "2"). Since there are no Employee Name values of "1" or "2", the report will be blank.

Using filter item reference for dimensionally modeled relational metadata

For dimensionally modeled relational metadata, Prompt Info is specified on the attribute with the role of _memberCaption, instead of the level. Although set on the attribute, the Prompt Info properties are processed as if they were on the level. By default, when the level is included in a report, users are prompted to enter MUNs in the level's prompt. To enter caption values instead, set the attribute's Filter Item Reference property to itself. When the prompted filter is applied, the filtered values will be based on the attribute values.

For example, the level Product Line has an attribute of Product Line with a role of _memberCaption. If the Filter Item Reference property value is set to Product Line, report users are prompted to enter Product Line values. If the Filter Item Reference property value is left blank, users are prompted to enter MUNs.

Note: Do not use the Filter Item Reference property with the Select with Tree prompt type. Because a Select with Tree prompt can only filter on a level or hierarchy, setting the Filter Item Reference property will cause an error.