Dear all,

Recently I was told that working with characteristics for statistical evaluations is a nightmare on BEx queries. Let’s have a brief look at financial ratings for that purpose. They are usually represented as a sequence of characters like “AAA” and SAP provides a standard object/characteristic for that on BW. So far so good. But now the accountants ask if you could show average ratings for given aggregations on a report.

The calculation should be quite simple:

• Transform the ratings into meaningful numbers (e.g. 1 for AAA) so that you can perform arithmetical operations on them.
• Once you are done, transform the value back into its character representation. That is the hard part on BEx query level.

I want to show you one Design Studio SDK based approach to transform arbitrary texts on your dashboard to solve the task described above. It might also be useful for other use cases as well.

The starting point

Like I described before, let’s assume we have a data feed showing something like below:

The first key figure shall be transformed into “C” and the second one into “A”. My first idea was to use smart CSS selectors to identify the numbers and replace them with Text. That line of thought would have worked if there were a selector to identify special content on the HTML dom tree but there is not. The W3C drafted a version for this particular use case but it was discarded eventually. So what next? I need means to perform complex selections and manipulations on certain elements and components on my dashboard at dedicated points in time (for example when the data is actually loaded). That means JavaScript is the key to fulfilling this task.

The solution is rather simple but powerful

I implemented a Design Studio SDK component called Replacer, which allows you to define a mapping table for the values you want matched and replaced. In addition to that you have to put a CSS selector to identify the desired html element. This should be as selective as possible because otherwise you might change more values than you initially wanted to. As a last step you have to put the ID of the target dashboard component you want to perform the changes on. In my case this is the standard crosstab with id “CROSSTAB_1”.

On the JavaScript coding, thanks to jQuery, I am finally able to search for values on the HTML dom tree. I take the dashboard designer’s data mapping and CSS selection to corner the desired value and voila the key figures are text again. 🙂 This approach however is not limited to that. You can see that on the following example. It is possible to manipulate whatever value on the component there is.

     Fig.1 Original values of CROSSTAB_1

     Fig.2 Mapping example

The first selector targets values on the header of component. The second one targets the first column of the table row on the key figure section. The third selection alters the column title of the characteristic for the rating. The successful replacement can be seen down below.

The context for all selections is defined by the technical id of the component. In my case that is CROSSTAB_1.

     Fig.3 Result on CROSSTAB_1 after mapping

To actually run the “Replacer” you need to call the method replaceAccordingToMapping. You will have to call it after the target component and its data is loaded. “Replacer” makes sure to wait for the data to arrive before it runs the replacement logic, cool isn’t it?

You have to use regular expressions on the mapping table to realize more complex selections.

Final Words

The manipulation functionalities of this new component are only limited by your creativity. You can target any object on the HTML dom tree you want. You can identify it using CSS and its content to finally shape the representation to your needs. Of course messing with the inner workings of your dashboard and other components might cause side effects if your selections are not selective enough. Knowledge about CSS selectors and regular expressions is key to use this component reliably and to its full extent.

You can get the component by installing the latest SCN SDK repository package. The SCN blog entry below can give you directions on how to work with our awesome Design Studio SDK package:

http://scn.sap.com/community/businessobjects-design-studio/blog/2014/12/10/scn-design-studio-sdk-development-community

Like all our SCN community SDK components the code is published open-source on GitHub:

https://github.com/org-scn-design-studio-community/sdkpackage

As always feel free to leave comments and ask lots of follow up questions.

Yours

Martin