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.

http://scn.sap.com/community/businessobjects-analysis-ms-office/blog/2015/11/26/announcing-general-availability-of-analysis-office-22

Together with some functions introduced in Analysis Office 2.1, the functions I am going to introduce are:

  1. Top N/ Bottom N at Design time
  2. Consume HANA Attribute View
  3. Custom Measures at Design time
  4. Move total above members
  5. 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.

/wp-content/uploads/2015/12/1_855124.png

After we got all the setting done, let’s start to explore those functionalities!

This is my original view.

/wp-content/uploads/2015/12/2_855134.png

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.

/wp-content/uploads/2015/12/5_855137.png

/wp-content/uploads/2015/12/6_855138.png

Smart copy to Design Studio.

/wp-content/uploads/2015/12/7_855139.png

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.

/wp-content/uploads/2015/12/8_855140.png

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.

/wp-content/uploads/2015/12/9_855141.png

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!

/wp-content/uploads/2015/12/10_855142.png

/wp-content/uploads/2015/12/11_855143.png

And yes, I can smart copy it to Design Studio.

/wp-content/uploads/2015/12/12_855144.png

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.

/wp-content/uploads/2015/12/13_855145.png

Here I add all of them and smart copy to Design Studio

/wp-content/uploads/2015/12/14_855146.png

Note that the sequence we click the cell will impact how AO calculates.

/wp-content/uploads/2015/12/15_855147.png

Besides, we can add calculation based on “calculation”.

/wp-content/uploads/2015/12/16_855148.png

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.

/wp-content/uploads/2015/12/17_855149.png

/wp-content/uploads/2015/12/18_855150.png

/wp-content/uploads/2015/12/19_855151.png

Here is a random sample formula.

/wp-content/uploads/2015/12/20_855152.png

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.

/wp-content/uploads/2015/12/21_855153.png

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.

/wp-content/uploads/2015/12/22_855154.png

/wp-content/uploads/2015/12/23_855155.png

Finally, smart copy to Design Studio.

/wp-content/uploads/2015/12/24_855156.png

4. Move total above members


With the help of AO, we can move total row/column above member.

/wp-content/uploads/2015/12/3_855135.png

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.

/wp-content/uploads/2015/12/2_855134.png



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.

/wp-content/uploads/2015/12/1_855124.png

/wp-content/uploads/2015/12/2_855134.png

We can type the header after the single quotation mark.

/wp-content/uploads/2015/12/3_855135.png

And then smart copy to Design Studio.

/wp-content/uploads/2015/12/4_856753.png

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. 😛

To report this post you need to login first.

7 Comments

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

  1. Mustafa Bensan

    Hi Alfred,

    Just for clarification, in point 1 for Top N / Bottom N, after performing a smart copy to Design Studio, if the Top N members change in future, is the new Top N automatically reflected in the data source when the application is launched?

    Thanks,

    Mustafa.

    (0) 
      1. Mustafa Bensan

        Hi Alfred,

        Thanks for the confirmation.  That’s good to know that the functionality is truly a “smart” copy then 🙂    Hopefully, in future we’ll see the Filter by Measure feature available directly on the data source through both the Initial View Editor and scripting, rather than being limited to the Crosstab component specifically.

        Regards,

        Mustafa.

        (0) 
        1. varun garaga

          HI Lee,

          Moving total members .

          What will happen exactly  if we check this box in AO . i didnt understand  .could you please brief me it would be helpful to me

          Thanks,

          Vaurn

          (0) 
          1. Alfred Lee Post author

            Hi Vaurn,

            As you can see the image in “Moving total members” section, what will happen is that “Total” will be in the first row instead of last row.

            Best regards,

            Alfred

            (0) 
      2. varun garaga

        HI Lee,

        Thanks for sharing. I wanted to know as you mentioned `(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.)

        HANA BAckend means you mean all calculations will take care by HANA? it will use in memory architecture?

        Thanks,

        Varun

        (0) 

Leave a Reply