Reporting Services MDX Field List and Using Measures on rows

When creating a Reporting Services report on top of an Analysis Services cube using the wizard it automatically creates a Field for each column in your MDX query. Those fields can then be used in your report. For reports based on a relational source the definition of these fields is quite simple, it is the same as the the column name of the originating query. For MDX queries this is very different. If you ever checked the definition of an automatically generated MDX field you will see a lengthy XML snippet instead:
FieldsList

The XMLs may look like these:

<?xml version="1.0" encoding="utf-8"?>
<Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
       xsi:type="Level"
       UniqueName="[Product].[Subcategory].[Subcategory]" />

<?xml version="1.0" encoding="utf-8"?>
<Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
       xsi:type="Measure"
       UniqueName="[Measures].[Internet Sales Amount]" />

As you can see those two are quite different in terms of xsi:type and UniqueName. The xsi:type “Level” refers to a dimension level whereas “Measure” refers to a measure. Depending on the type of field, different properties are available within the report:
FieldProperties

For example the property BackgroundColor is only populated for fields of type “Measure” whereas the property UniqueName is only populated for fields of type “Level”. Measure properties are tied to the CELL PROPERTIES in your MDX query and Level properties are tied to DIMENSION PROPERTIES:

SELECT
NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY { ([Product].[Subcategory].[Subcategory].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

If we remove MEMBER_UNIQUE_NAME from the DIMENSION PROPERTIES we would no longer be able to use Fields!Subcategory.UniqueName in our SSRS expressions, or to be more precise it would simply always return NULL (or NOTHING in terms of Visual Basic). The same of course is also true for the CELL PROPERTIES.

So far this is nothing really new but there are some more things about the fields of MDX queries. There is a third xsi:type called “MemberProperty” which allows you to query member properties without having to define separate measures within your query:

<?xml version="1.0" encoding="utf-8"?>
<Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
       xsi:type="MemberProperty"
       LevelUniqueName="[Product].[Subcategory].[Subcategory]"
       PropertyName="Category" />

Once we add a member property to our MDX query SSRS also automatically creates this field for us. NOTE, this is only possible by manually modifying the MDX!

SELECT
NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY { ([Product].[Subcategory].[Subcategory].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
    [Product].[Subcategory].[Subcategory].[Category] ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

To get the [Category] which is associated to a given [Subcategory] you would usually need to create a separate measure like this:

WITH
MEMBER [Measures].[Category] AS (
[Product].[Subcategory].Properties( "Category" )
)
SELECT

This has the very bad drawback that using the WITH MEMBER clause disables the formula engine cache for the whole query what may result in worse query performance. So you may consider using DIMENSION PROPERTIES instead of a custom Measure next time.

 

There is another very nice “feature” that is also related to the field list. If you ever had the requirement to create a parameter to allow the user to select which measure he wants to see in the report you probably came across this blog post by Chris Webb or this blog post by Rob Kerr. As you know by then, SSRS requires you to put the Measures-dimension on columns, otherwise the query is not valid. This is because the number of Measures is not considered to be dynamic (opposed to e.g. Customers) which allows SSRS to create a static field list. This makes sense as SSRS was originally designed for relational reporting and a table always has a fixed number of columns which are similar to fields in the final SSRS dataset. Using Measures on columns is the way how SSRS enforces this.

As we are all smart SSRS and MDX developers and we know what we are doing we can trick SSRS here. All we need to do is to write a custom MDX query using the expression builder – do not use or even open the Query Designer at this point otherwise your query may get overwritten!
Query_Measures

SSRS also automatically creates the fields for us, but this time the fields are not defined correctly. It creates one field with a very cryptic name and the following XML definition:

<?xml version="1.0" encoding="utf-8"?>
<Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
       xsi:type="Measure"
       UniqueName="[Measures].[MeasuresLevel]" />

As you can see SSRS thinks that this field is of type “Measure” but it actually is a “Level”. After changing this little thing we can access all field properties that are unique to Level-fields like <Field>.UniqueName

So this is the final MDX query and the associated XML field definition:

SELECT
{} ON 0,
[Measures].members
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON 1
FROM [Adventure Works]

Ensure that you have defined the necessary DIMENSION PROPERTIES here otherwise they will not be available/populated within the report!

<?xml version="1.0" encoding="utf-8"?>
<Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
       xsi:type="Level"
       UniqueName="[Measures].[MeasuresLevel]" />

In order to make use of this approach in a report parameter we further need to create calculated fields for our parameter label and parameter value:
Fields_Measures
The definition of the parameter is straight forward then:
ParameterDefinition

You can not only use this approach to populate a parameter but you can also use it to crossjoin Measures on rows with any other hierarchy. This way you can avoid writing complex MDX just to work around this nasty SSRS limitation.

Downloads:

Sample SSRS report: CustomFieldList.rdl

4 Replies to “Reporting Services MDX Field List and Using Measures on rows”

  1. Hey, I apologize for commenting on an old thread, but I keep searching for an answer and it always leads me back here, because to be honest, there just isn’t much information out there about this topic (that I can find).

    I am trying to use the member properties in the same way you are (adding them to the MDX), with one difference. I’m using a hierarchy (DESCENDANTS function) in my MDX query as well. What this does for my report in SSRS is I can’t access the properties using the fields that SSRS autogenerates for me, it throws an warning saying the fields don’t exist and the fields are blank or #ERROR. Any ideas?

    There are a couple things I can do as alternatives that I really don’t want to do: manually edit the XML field source to remove the “[Level 02]” part which seems to fix the problem and I can use the fields as normal, create members in the MDX, or use the syntax (“MEMBER_PROPERTY”). All of these alternatives are either tedious or non-performant.

    Thanks,
    Byron

    • Hi Byron,
      For regular hierarchies I usually use the standard parameter query. if you say that the performance is not sufficient, I assume that the list returns 10.000+ members and this raises the question why you need such a parameter and how the user should ever select from it?

      anyway, to use the XML approach from the post, you would need to add each hierarchy level separately to the DIMENSION PROPERTIES so you can query them in the parameters field list using the XML provided in the post. the MDX would look like this then

      SELECT {} ON 0, [].[].members ON 1 DIMENSION PROPERTIES [].[].[], [].[].[] FROM []

      kind regards,
      -gerhard

      • Gerhard,

        Thanks for the response, what you are saying makes sense. I’m not using this query for the parameters, I’m using it for the main dataset. Let me elaborate a little bit, this is my query:

        SELECT {} ON COLUMNS, {
        DESCENDANTS([DimWithHier].[Hierarchy].CHILDREN)
        }
        DIMENSION PROPERTIES
        [DimWithHier].[Hierarchy].[SomeUserDefinedProp]
        ON ROWS
        FROM [CubeSource]

        In SSRS, it autogenerates a field called “SomeUserDefinedProp” with an XML field source that has
        xsi:type=”MemberProperty”
        PropertyName=”SomeUserDefinedProp”
        LevelUniqueName=”[DimWithHier].[Hierarchy].[Level 02]”

        Using this field in the report throws an #ERROR, however, if I update the LevelUniqueName to just “[DimWithHier].[Hierarchy]” the field works as intended, though I would have to update that everytime I make a change to the MDX and refresh fields in the dataset. The alternative is to ignore the field autogenerated and use the syntax Fields!DimWithHier(“SomeUserDefinedProp”), though this for some reason is much slower than using the field above.

        So, what you are saying is instead of the above, I need to do the below?

        SELECT {} ON COLUMNS, {
        DESCENDANTS([DimWithHier].[Hierarchy].CHILDREN)
        }
        DIMENSION PROPERTIES
        [DimWithHier].[Hierarchy].[Level 02].[SomeUserDefinedProp],
        [DimWithHier].[Hierarchy].[Level 03].[SomeUserDefinedProp],
        [DimWithHier].[Hierarchy].[Level 04].[SomeUserDefinedProp],


        [DimWithHier].[Hierarchy].[Level 10].[SomeUserDefinedProp]
        ON ROWS
        FROM [CubeSource]

        • it can be that SSRS overwrites your manual changes to the fields – i think I had the same issue multiple times

          yes, the idea would be to have e.g. 10 DIMENSION PROPERTIES and 10 matching XML fields
          depending on how the XML fields are populated, you may create another calculated field which selects the one XML field that is populated

          you may also write your query like this:
          WITH MEMBER [MyProp] AS [DimWithHier].[Hierarchy].currentmember.properties(“SomeUserDefinedProp”)

          SELECT { [MyProp] } ON 0,
          Descendants(…) ON 1
          FROM [CubeSource]

Leave a Reply

Your email address will not be published. Required fields are marked *

*