Overview: A SQL Server Analysis Services calculation to retrieve the description, on a single row, for a value which differs from period to period. Useful when retrieving a flattened rowset for the purpose of generating a report from Reporting Services.

In the following example (shown using the cube browser in BIDS), we have values from 0 through 5. When the time period is added to the columns, and a Metric Name label on the rows, our measures line up nicely from year-to-year when browsing a cube:

The above values are meaningless without some sort of description to help the user understand its meaning. However, when we add the description associated to the value, we end up with 2 rows per Metric Name, which is not quite what we want:

Instead of the above, what I want is for the data to remain on one row. This was very important so that I could structure a Reporting Services report correctly. This is an example of what I do want:

I’m sure there’s several ways to do this … I accomplished it using the following calculated member:

Filter([Metric].[Scale Member Name].children,

NOT IsEmpty([Measures].[School Measure Value])

).Item(0).name

To break down the expression:

Filter: Returns the set that results from filtering a set based on a search condition.
.Children: This is passing a Children function to the Member being retrieved by the Item(0).name. If I didn’t specify Children, I would get “All” returned for each row.
NOT IsEmpty: Returns the member name for a non-null measure only.
.Item(0).name: Since the Filter returns a set, we want to use the .Item to return the first tuple of the set only. The .name specifies we want to see the member name.

Please keep in mind that this approach only works with a “strict” decode list – in my case, I have one and only one description that can equate to a value. That data integrity is maintained by the ETL process in my situation, so in the above example I didn’t include a condition to gracefully handle the existence of bad data.

Finding More Information

Kevin Goff’s Blog: When PREVMEMBER alone isn’t enough in MDX

MSDN: Filter Function

MSDN: Children Function

MSDN: IsEmpty Function

MSDN: Item (Tuple)

When defining a KPI in SQL Server Analysis Services, the indicators selected in SSAS are not matched precisely by a KPI-aware client application such as Excel. For some SSAS indicators, the Excel rendering is very similar; for other indicators, a close option is unavailable. The indicators Excel uses are the same as what’s used for its Icon Sets (found under the Conditional Formatting menu).

This discussion focuses on what the indicators selected in an SSAS KPI definition will look like in Excel. We will focus on Status and Trend, as shown in the following KPI configuration screen in SSAS:

KPI Properties

The 4 main properties of a KPI include Value, Goal, Status, and Trend, as follows:

Value:

Uses Indicator: No (numeric value only)

Example:

[Measures].[Growth in Customer Base]

Goal:

Uses Indicator: No (numeric value only)

Example:

Case

    When IsEmpty

         (

           ParallelPeriod

           (

             [Date].[Fiscal].[Fiscal Year],

             1,

             [Date].[Fiscal].CurrentMember

           )

         )

    Then [Measures].[Sales Amount]

    Else 1.15 *

         (

           [Measures].[Sales Amount],

           ParallelPeriod

           (

             [Date].[Fiscal].[Fiscal Year],

             1,

             [Date].[Fiscal].CurrentMember

           )

         )

End

Status:

Uses Indicator: Yes (indicator only; the numeric value of -1, 0 or 1 is not displayed in Excel)

Default Indicator: Gauge

Example:

Case

    When KpiValue( "Revenue" ) / KpiGoal( "Revenue" ) > 1

    Then 1

    When KpiValue( "Revenue" ) / KpiGoal( "Revenue" ) <= 1

         And

         KpiValue( "Revenue" ) / KpiGoal( "Revenue" ) >= .85

    Then 0

    Else -1

End

Trend:

Uses Indicator: Yes (indicator only; the numeric value of -1, 0 or 1 is not displayed in Excel)

Default Indicator: Standard Arrow

Example:

Case

    When IsEmpty

         (

           ParallelPeriod

           (

             [Date].[Fiscal].[Fiscal Year],

             1,

             [Date].[Fiscal].CurrentMember

           )

         )

    Then 0

    When VBA!Abs

         (

          (

            KpiValue( "Revenue" )

            -

            (

              KpiValue( "Revenue" ),

              ParallelPeriod

              (

                [Date].[Fiscal].[Fiscal Year],

                1,

                [Date].[Fiscal].CurrentMember

              )

            )

          )

          /

          (

            KpiValue( "Revenue" ),

            ParallelPeriod

            (

              [Date].[Fiscal].[Fiscal Year],

              1,

              [Date].[Fiscal].CurrentMember

            )

          )

         ) <=.02

    Then 0

    When (

           KpiValue( "Revenue" )

           -

           (

             KpiValue( "Revenue" ),

             ParallelPeriod

             (

               [Date].[Fiscal].[Fiscal Year],

               1,

               [Date].[Fiscal].CurrentMember

             )

           )

         )

         /

         (

           KpiValue( "Revenue" ),

           ParallelPeriod

           (

             [Date].[Fiscal].[Fiscal Year],

             1,

             [Date].[Fiscal].CurrentMember

           )

         ) >.02

    Then 1

    Else -1

End

SSAS Status Indicators

The 9 choices in SSAS translate to 5 displays in Excel.

SSAS Status Indicator selected: Shapes, Thermometer, Cylinder, or Faces

Rendering in Excel:

Pro: Different shapes as well as different colors.

Con: Excel has no equivalent currently for thermometer, cylinder, or faces.

SSAS Status Indicator selected: Traffic light

Rendering in Excel:

Pro: Clear to understand.

Con: Difficult for color blind users to see the difference.

SSAS Status Indicator selected: Road signs

Rendering in Excel:

Pro: Different shapes as well as different colors

SSAS Status Indicator selected: Gauge, or Reversed Gauge

Rendering in Excel:

Pro: Two choices for a “more is better” type of KPI (the status arrow), or a “less is better” type (the reversed choice).

Cons: Not as clear to users what is good (the solid circle for the regular Gauge) or bad (the open circle for the regular Gauge); Excel has no Gauge equivalent to display (although, as a sidenote, I believe gauges should be used judiciously).

SSAS Status Indicator selected: Variance Arrow

Rendering in Excel:

Pro: Clear to understand.

Con: Meaning of this status arrow could easily be confused with a trend arrow.

SSAS Trend Indicators

SSAS Trend Indicator selected: Standard arrow

Rendering in Excel:

Cons: Black & white; only useful for a “more is better” type of KPI.

SSAS Trend Indicator selected: Status arrow, or Reversed status arrow

Rendering in Excel:

Pros: Clear to understand; Two choices for a “more is better” type of KPI (the status arrow), or a “less is better” type (the reversed choice).

SSAS Trend Indicator selected: Faces

Rendering in Excel:

Pro: Different shapes as well as different colors.

Con: Not the typical way that an indicator is presented.

To reproduce the above in Excel, the following pivot table options were used with an Analysis Services data connection to the AdventureWorks 2008 cube:

Additional comments:

Note that each indicator in SSAS will return 3 possibilities: red, yellow and green. Each associates to -1, 0 and 1.
If the SSAS indicator is changed (ex: from Gauge to Shapes) in BIDS, you’ll need to (1) redeploy the cube, (2) refresh the data connection in Excel, and (3) delete the Status (and/or Trend, whichever was modified) from the pivot table and then add it back to the pivot table again. The data refresh alone won’t update the indicator image.

If you’re intending to use Excel primarily to browse the cube, I believe that the defaults for KPI indicators in SSAS should usually be changed. Personally, my favorites for Status are Shapes or Road Signs. My favorite for Trends is the Status Arrow.