Scorecard: Step by Step to Nice Visualization (Conditional Format)
Step-by-Step to define a Scorecard with Conditional Format visualization.
Screcard can visualize conditional formats, but scorecard does not have special calculation engine (see DS 1.6 Scorecard – Conditional Formatting | SCN). This means in the practice, you need to assure conditional formats are defined in the data source.
– following example is showing technical aspects, the values are really random –
How to assure Conditional Formats in data source?
You have basically 2 options, based on your source system.
Option 1: Bex Exception in Query Definition (BW)
Option 2: Conditional Formats definition in Analysis Office (BW, HANA)
Here I show you the option 2.
1. Open your data source in Analysis Office
2. Define your drilldown
3. Define your Conditional Formats
( in this example it is defined on second measure with T15 in key, sorry, this test data source does not have texts)
4. In addition I have created second Conditional Format “for Results” to have also the option for the result values.
5. When you finished, use “Smart Copy” and go to Design Studio.
6. There, use Smart Paste on Data Sources
Now, your data source is transferred also with the definitions for conditional formats.
In Design Studio, I want to create simple Scorecard with Bullet Chart column.
7. insert Scorecard and assign Data Source
8. Then, in additional properties you will see
9. and this is also the sequence you always need to define scorecard
First, assure your Dimension selected for Row Scope is correct (see the blog for more on this topic Design Studio 1.6 – View on Scorecard Component)
Second, use always “Generate Initial Scorecard” button
By default, you get the initial scorecard – now you can change what you want (here I concentrate only on the last column)
10. Let us change the type to “Bullet Chart” on column 4. I delete also column 2 and 3.
11. Now, let us check again what is currently selected to display:
Actual Value is the measure 1 (34950 in row 1)
Target Value is measure 2 (957 in row 1)
12. The problem is, somehow all rows loot similar (the visual comparison is not possible).
Explanation: the setting “Auto Value Axis” is ON. This means, scorecard is adjusting the chart axis per row. And for this reason all rows are looking similar.
13. Deselect this property and adjust the values to the lowest and highest value across ALL ROWS!
14. Effect is now a bit better:
15. Now, we want to use Conditional Formatting.
I use the property “Actual Color” and select the result cell to read the conditional format from.
Important: Binding Type is “Conditional Format Value”
16. This is not yet enough, now I know that I will get the values of the conditional format, maximum is 1 to 9 (numbers) and can apply conditional display in scorecard.
(now scroll up to see what was defined for Results – only “1” is set to value > 1000)
I add the conditional display and define only the value “1” to “Good”.
17. and the effect is, FAX is GREEN
I hope this helps some of you to understand how to use conditional formats in Scorecard.
All steps are numbered, so if something is unclear, ask placing the step number.
I have exported the data source to CSV and the example is available here.
You can find in it 2 Scorecards and 2 data sources. The first one will not work offline, the second will display data, but w/o conditions.