Design Studio conditional formatting on SAP HANA

When working with Design Studio connecting directly to SAP HANA, I encountered an instance where conditional formatting was needed. The requirement was quite easy; an actual is compared against a maximum.  If the actual is higher it should be displayed in red.

After looking around I found that there is no ‘out of the box’ feature supporting this. I came up with the idea to pass an HTML tag from a calculated column in SAP HANA, so that I could then use CSS to format it as desired in Design Studio. The steps are outlined below.

SAP HANA 

First add a calculated column in the calculation view in SAP HANA. The formula below compares the two values. If Actual is higher than the Maximum, a text string is returned, formatted as follows:

<circle></circle><alert1>4290</alert1>

Basically this formula adds HTML tags to enclose the Actual value of 4290 in this example.

DS_conditional_formula_HANA

if("ACTUAL">" MAXIMUM",'<circle></circle><alert1>'+STRING(ROUND("ACTUAL",0))+'</alert1>','STRING(ROUND("ACTUAL",0))')

Some drawbacks are:

  1. The result is an attribute and not a measure, so the result will not be aggregated to a total.
  2. As Design Studio requires a measure in the data source, so a dummy measure might be needed, this can be hidden later.

DS_dummy_calculation

 

Design Studio

You can hide the dummy measure by setting the column width to zero in the Design Studio cross tab:

DS_column_width

Finally you can add a custom CSS file to your Design Studio document,  to format your conditional value. For example:

alert1 {
position: relative;
float: right;
width: 85%;
color: RGB(212,46,18);
text-align: left;
border-radius: 5px;
}

circle {
display: none;
float: left;
position: relative;
top: 2px;
background: RGB(212,46,18);
border: 4px solid RGB(212,46,18);
border-radius: 50%;
}

This should then give you something like this.

DS_Conditional_Table

 

So in conclusion,  this is a bit of a work-around but it did solve my conditional formatting challenge. I hope you found it useful.

This article belongs to
Tags
  • Design Studio
  • SAP HANA
Author
  • Just Blogger