How to Highlight Bars in a Time Series Chart?

Owned by Gergina Georgieva (Unlicensed)
Last updated: Mar 11, 2016
3 min read

Sometimes, it is convenient to highlight one bar in a chart. So, in the following example, we will color the bar for the current reporting period is a different color:

 

Obviously, in the next reporting period – or after switching the Point of View to another period – we want another bar to be highlighted.

 

To solve this, we have built a list with two cube-calculations and applied a special format for coloring and stacking bars.

In fact, each bar in the chart is a stack of 2 bars: a blue one and a yellow one. For all periods, except for the current period, the blue bar has a height of 0 and the yellow bar represents the periodic Sales. For the current period, it’s the other way around: the blue bar is the periodic Sales and the yellow bar is 0.

To switch between a value of 0 and the Sales, we created the following pair of cube-calculations[1]:

 

SalesCurr:

CASE
WHEN     [PER].[PER].CurrentMember IS <<@@CUR(PER)>>
THEN      [ACC].[ACC].[Sales]
ELSE       NULL
END

SalesNonCurr:

CASE
WHEN      [PER].[PER].CurrentMember IS <<@@CUR(PER)>>
THEN       NULL
ELSE       [ACC].[ACC].[Sales]
END

 

These cube-calculations effectively illustrate two different meanings of ‘Current Period’.

  1. With [PER].[PER].CurrentMember we take the context of the underlying MDX query: what Period (PER) member is set in Rows, Columns or Where. So for the last bar of the chart [PER].[PER].CurrentMember is December.
  2. With <<@@CUR(PER)>> we refer to the period context of the report. This context can be fixed or changed via the Point of View bar. In lists this is also known as @@CUR. <<@@CUR(PER)>> is not standard MDX syntax but it will be replaced on-the-fly by the right period member.

[1] The examples are taken from a Microsoft Analysis Service cube, hence using the Microsoft MDX syntax. For Essbase or SAP BW slightly different statements are required.

 

Now, the trick is that only for the selected month (February in the first chart) these two meanings are equivalent. That means that for cube-calculation SalesCur, only for February we will get periodic Sales back and nothing (‘NULL’) for the other months. For SalesNonCurr it is the other way around.

We get the visual effect by using the cube-calculations in the following list, used as Chart 1 >> Bars List in a report created with the Multiple Bar & Line Charts:

 

 

 

The Linked Format blue >> and yellow >> are defined as follows:

 

 

An extension of this conditional highlighting could be a chart where bars until the current period are blue and beyond the current period they are yellow:

 

To achieve this, the cube-calculations must be altered as follows:

 

SalesCurr: 

CASE
WHEN       RANK([PER].[PER].CurrentMember,{[PER].[PER].AllMembers}) <=
                  RANK(<<@@CUR(PER)>>,{[PER].[PER].AllMembers})
THEN        [ACC].[ACC].[Sales]
ELSE         NULL
END

 

SalesNonCurr:

CASE
WHEN       RANK([PER].[PER].CurrentMember,{[PER].[PER].AllMembers}) >
                  RANK(<<@@CUR(PER)>>,{[PER].[PER].AllMembers})
THEN        [ACC].[ACC].[Sales]
ELSE         NULL
END

 

The RANK function is checking the order of a member within a set of members (‘{}’). This can be used within a Period dimension where the ranking of members is unambiguous.

 

 

Â