Since long time ago, we can use Smart Copy & Paste to realize conditional formatting in Design Studio.
(For those who are not familiar with Smart Copy & Paste, here is the tutorial video: https://www.youtube.com/watch?v=e2d3nhGR9Iw)
With the launch of Analysis Office 2.2, there are some new functions with HANA as the data source.
Together with some functions introduced in Analysis Office 2.1, the functions I am going to introduce are:
- Top N/ Bottom N at Design time
- Consume HANA Attribute View
- Custom Measures at Design time
- Move total above members
- Create blank row or column
Before we start, to use Analysis Office 2.2 with HANA, we need to setup HANA HTTP connection first. Details can be found here: http://scn.sap.com/docs/DOC-63479
Then we need to make sure the HANA HTTP connection is also available from Design Studio side. The one with port 8001 is my HANA HTTP connection.
After we got all the setting done, let’s start to explore those functionalities!
This is my original view.
1. Top N/ Bottom N
With Design Studio 1.6, user can define top N in runtime with the context menu of the crosstab. However, from a designer point of view, we still want to be able to do so in design time. With AO2.2, it’s now possible! Note that this option will only be available when choosing the up-left cell of the crosstab.
Smart copy to Design Studio.
I was curious about what will happen if user choose to filter by measure by context menu on top of this data source.
It turns out work well, the “n” that user inputs will overwrite the original “n”. In other words, it will not be the top/bottom “n” of that subset, but the top/ bottom “n” of the full data set. As a result, designer can first define the most common “n” in design time, then let user change it in runtime.
For example, I define the Bottom 1 from context menu.
The result is not T5, which is the Bottom 1 of the Top 5. Instead it’s T6, which is the Bottom 1 of all members.
2. Consume HANA Attribute View
In Design Studio, we can only consume HANA Analytic View or Calculation View. With AO2.2, it’s also possible to consume HANA Attribute View in Design Studio!
And yes, I can smart copy it to Design Studio.
3. Custom calculations
This is another exciting feature. There are 3 kinds of calculation. Let’s explore one by one.
First one is the “Add Calculation”. The option will be available when at least 2 measures are selected by pressing Ctrl.
Here I add all of them and smart copy to Design Studio
Note that the sequence we click the cell will impact how AO calculates.
Besides, we can add calculation based on “calculation”.
Second is “Add Advanced Calculation”. With Advanced Calculation, we can write formula based on the functions provided. Note that different backend offers different functions. Below are all functions available with Analysis Office 2.2 along with HANA backend.
Here is a random sample formula.
Note that my formula is actually [‘Min’ *1.5]. Decimal number seems to have display issue. But the result is correct.
And smart copy to Design Studio.
Third one is “Add Restriction”. In my opinion, Restriction is like member filter for EACH measure.
For example, I only care about T1 and T2 for measure “Latest”, T3 and T4 for measure “Max”, and T5 and T6 for measure “Min”. I can hide unwanted KPIs by using Restriction.
Finally, smart copy to Design Studio.
4. Move total above members
With the help of AO, we can move total row/column above member.
We can also find this option in the Initial View of Design Studio.
It shows when right-clicking the “Columns” or “Rows”.
However, ONLY datasource coming from Smart Copy has this option.
5. Create blank row or column
To be frankly, I haven’t come up with the use case of this function. But maybe someone else will.
We can type the header after the single quotation mark.
And then smart copy to Design Studio.
To wrap up, even though these functions are not native in Design Studio so far, at least they can be realized with the help of Analysis Office. I hope that they will become native in Design Studio in the near future.
Thanks for reading this long blog. 😛