Using Extended Field Properties for a Hyperion Essbase Cube

New: 12 December 2006

The Hyperion Essbase data processing extension supports extended field properties. Extended field properties are properties in addition to the Reporting Services-supported field properties Value and IsMissing that are available on the data source and supported by the data processing extension. Extended properties do not appear in the Datasets pane as part of the field collection for a report dataset. You can include extended field property values in your report by writing expressions using the global Fields collection that specify them by name.

Extended properties can be predefined or custom properties. Predefined properties are properties common to multiple data sources, which are mapped to specific field property names. They can be accessed through the global Fields collection by name. Custom properties are defined for each data provider and can be accessed through the global Fields collection only through syntax that uses the extended property name as a string.

Use either of the following syntaxes to refer to predefined extended properties in an expression:

  • Fields!FieldName.PropertyName
  • Fields!FieldName("PropertyName")

Working with Field Properties

Extended field properties are retrieved at the same time as the report dataset fields when you run an MDX query using the Microsoft .NET Framework Data Provider for Hyperion Essbase. Fields and the dimension and cell properties specified in the MDX query are retrieved from the data source.

Field properties fall into the following categories:

  • Field properties that are commonly supported by multiple data providers and that map to predefined report dataset field properties. For example, the predefined report dataset Value property represents the default property for the value of a measure or dimension in the data source. The default value for a measure is a numeric value. The default value for a dimension is the user-friendly caption. To include the default value in a text box, use the expression =Fields!<FieldName>.Value.
  • Predefined field properties that are commonly supported by multiple data providers and that appear in the underlying MDX query for a report dataset. For example, the MDX dimension property MEMBER_UNIQUE_NAME is mapped to the predefined report dataset field property UniqueName. To include the unique name value in a text box, use the expression =Fields!<FieldName>.UniqueName.
  • Custom field properties that are supported by a data provider and that appear in the underlying MDX query for a report dataset, but do not appear in the report Datasets pane as fields under that dataset. For example, Long Names is a member property defined for a dimension level. To include the value in a text box, you use the expression =Fields!<FieldName>("Long Names"). Field names in the expression are case-sensitive.

The name for an extended field property appears in the ToolTip when you hover over a field in the Metadata pane in the query designer. For more information about the query designer you can use to explore the underlying data, see Hyperion Essbase Query Designer User Interface.

For more information about using fields and field properties in an expression, see Using Global Collections in Expressions (Reporting Services).

Note

Values exist for extended field properties only if they are included in the MDX expression and the data source provides these values when your report runs and retrieves the data for its datasets. You can then refer to those Field property values from any expression using the syntax described below. However, because these fields are specific to this data provider and not part of the report definition language, changes that you make to these values are not saved with the report definition.

Predefined Field Properties

The following table provides a list of predefined field properties that you can use for a Hyperion Essbase data source.

Property Type Description or expected value

Value

Object

Specifies the data value of the field.

For a dimension property, this is mapped to MEMBER_CAPTION. For a measure, this is mapped to the data value.

IsMissing

Boolean

Indicates whether the field was found in the resulting data set.

FormattedValue

String

Returns a formatted value for a key figure.

Mapped from FORMATTED_VALUE in the MDX expression.

BackgroundColor

String

Returns the background color defined in the database for the field.

Mapped from BACK_COLOR in the MDX expression.

Color

String

Returns the foreground color defined in the database for the item.

Mapped from FORE_COLOR in the MDX expression.

UniqueName

String

Returns the fully qualified name of a level.

Mapped from MEMBER_UNIQUE_NAME in the MDX expression.

Custom Properties

Use the following syntax to refer to custom extended properties in an expression:

  • Fields!FieldName("PropertyName")

The following table shows the custom field property that you can use for a Hyperion Essbase data source.

Property Type Description or expected value

FORMAT_STRING

String

Defined on a measure, this is the FormattedValue available as a String type.

External Resources

Using SQL Server 2005 Reporting Services with Hyperion Essbase

See Also

Reference

Using Global Collections in Expressions (Reporting Services)

Other Resources

Using Expressions in Reporting Services
Hyperion Essbase Query Designer User Interface

Help and Information

Getting SQL Server 2005 Assistance