Visual Design in Analysis Office 2.1 (e.g. Hichert)

[alexander_peter]

Nowadays, we see many customers applying conceptual and visual design rules to their reports. The idea is to immediately comprehend the semantics of the report just based on the design. It is similar to musical scores – there are strict rules how to set and read musical scores. No one would think that you could design scores in different colors or sizes. One of the big influencers of these visual design concepts is Prof. Hichert. He has established a standard with his
SUCCESS rules. You could read more about this here. In particular, you should compare some of the before-and-after examples to immediately understand why it is highly beneficial to apply certain rules for layouting, charting and scaling.

This blog is about applying some of these rules within Analysis Office crosstabs and charts. Note that this takes advantage of the new API for Table Design which is available as of Analysis Office 2.1.

As an example, we would like to introduce three styles to mark actual values for the current year (black), actual values for the past year(s) (grey) and
forecast/budget values (shaded). Note: these styles are just examples (they do not match 100% with Hichert terminology).

The idea is to add an empty row (with small height) after the measure names and apply the respective style directly to the cell under the measure name. We can derive the semantics of the measures based on a specific nomenclature, e.g. ACT Sales 15 is referring to actual sales in 2015 or FC sales 16 is referring to forecast sales for 2016. So we apply the heuristic that if we find “ACT” or “FC” in the measure name, it will apply the style for actual values or
forecast/budget values. We can also check if the current year is part of the measure name, so ACT Sales 15 will apply the style for the actual values for the current year.

See here the original plain crosstab:

After applying the rules, it looks like this:

Note that the measure “Returns Value” does not match any of the nomenclature rules, so there is no specific style applied.

You can also map styles to charts (“Returns Value” is not affected, again):

How can this be realized?

First, we need a VBA function to create the right styles. You can simply record an Excel macro and set the styles. Just take the generated coding and create a function that creates the styles.

Now we need a function to derive the right style based on the measure name:

‘ check for FC

If  Left(measureName, 2) = "FC" Then

     getStyleByText = C_HICHERT_FC

     Exit Function

End If

' check for ACT

If  Left(textVal, 3) = "ACT" Then

     If  InStr(1, measureName, actualYear, vbTextCompare) Then

          getStyleByText = C_HICHERT_ACT_CURRENT
     Else

          getStyleByText = C_HICHERT_ACT_LAST

     End If

End If

Then we need an API call to insert a new line after the measure texts:

' insert line

    Dim RuleId As String

     RuleId = Application.Run("SAPInsertLine", , dataSource, "After", "Dimension", nameOfMeasureDimension)

' set height

      Call Application.Run("SAPFixLineSize", , dataSource, RuleId, height)

And now we apply the styles to the cells of the new empty row.

result = Application.Run("SAPSetFormat", , dataSource, StyleName, "VIRTUALDIMENSION", firstLine, "MEMBER", secondLine)

This should just give you some idea what you can do with the API for Table Design. Analysis Office 2.1 has just been released for Ramp-up.

I thank Olaf Fischer for providing some example coding for this blog !