Skip to Content

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:

/wp-content/uploads/2015/06/hichert1_722205.png

After applying the rules, it looks like this:

/wp-content/uploads/2015/06/hichert2_722206.png

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):

/wp-content/uploads/2015/06/hichert3_722213.png

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 !

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Martin Kreitlein

    Hallo Mr. Peter,

    nice to see what you can do with VBA, but – I think many customers have the same opinion – we would rather prefer to see a real progress on the Idea List: SAP BusinessObjects Analysis edition for MS Office: Ideas

    There are several ideas with a lot of votes and even already 2-4 years old – but still under Review….

    Many of the requests contain basic functionality, and we as customers don’t understand what the issue or difficulty is with that list?

    One progress could be to post a regular, official SAP Statement at least into the ideas which are “under Review”.

    Thanks for your understanding,

    M. Kreitlein

    (0) 
    1. Aaron Benner

      Very good points.  VBA is always to be avoided as it causes additional issues and dependencies with Excel versions, etc.

      For example we are rolling out EPM to Surface Pro which has only Windows 8 and .Net 3.5 is no longer installed.  Huge headaches with the new, nonstandard clients!! 

      “Just say no to VBA”

      (0) 
  2. Ulrich Seidl

    Hello Alexander,

    thank you for sharing your ideas concerning Hichert-SUCCESS respectively IBCS.

    I am one of the “Hichert Certified Consultants” (http://www.hichert.com/certifications/267 and http://www.hichert.com/company/533) working with this concept for many years.

    Maybe you might be interested to hear about some of the differences between your example and our concept:

    1. Messages help better understanding -> SA 1  INTRODUCE MESSAGE ( http://www.ibcs-a.org/standards/119)

    2.     Scaling and units   -> UN 1.2  Unify numbers, units, and dates (http://www.ibcs-a.org/standards/103)

    3.     Labels – directly in the chart -> SI 5  AVOID DISTRACTING DETAILS SI 5.2  Avoid long numbers (http://www.ibcs-a.org/standards/153)

    1. a. Helping lines often cause “noise”  – SI 3  REPLACE WITH CLEANER LAYOUT -> SI 3.1  Replace grid lines and value axes with data labels (http://www.ibcs-a.org/standards/151)
    2. b. SI 1  AVOID UNNECESSARY COMPONENTS -> SI 1.2  Avoid colored or filled backgrounds (http://www.ibcs-a.org/standards/149)

    4.     Use structure not time display  -> EX 1.1 USE CORRECT CHART TYPE -> Charts with vertical category axes (http://www.ibcs-a.org/standards/136)

    5.     Scenarios, apply to all variables (Return values for example refer to periods as well) -> UN 3.2  UNIFY SCENARIOS (http://www.ibcs-a.org/standards/108)

    1. a.     EX 4  ADD COMPARISONS -> EX 4.2  Add variances (http://www.ibcs-a.org/standards/146)

    6.     ST 2  BUILD NON-OVERLAPPING ELEMENTS -> ST 2.2  Build non-overlapping business measures (http://www.ibcs-a.org/standards/126)

    Gross Sales -Returns=Net Sales

    7.     Instead of one chart with 4 columns you could design four bar charts (one chart for each variable e.g. sales in 2014, Sales in 2015, sales Forecast for 2016, returns values(ACT) )

    8. CONDENSE – INCREASE INFORMATION DENSITY -> CO 4  ADD ELEMENTS -> CO 4.2  Show multi-tier charts (http://www.ibcs-a.org/standards/158)

    9.     Or another possibility: Return values as a line (RV % of gross sales) within the bar chart…..

    If you would really like to follow the rules, you could try to design a chart as shown in IBCS-Template 04 or 06 (http://www.hichert.com) or some examples of Zebra.bi (www.zebra.bi)

    This will help you in finding

    1.     The right chart type – structure -> bars

    2.     Do the right comparisons with previous year and forecast

    3.     Put the numbers directly on the bar

    4.     Invrease readability by condensed information

    5.     Avoid noise

    6.    

    Congrats and keep on going with SUCCESS

    Kind regards,

    Ulrich Seidl

    01--06C.png

    02--04C.png

    03-Zebra-MCh.gif

    04-Zebra-WF.png

    (0) 

Leave a Reply