The use of comments in WebI is a recurring topic, particularly when comments should be stored in and retrieved from a BW source system.

Let’s discover the original solution developed by Aosis.

1. The context

Several technical notes deal with this subject but most propose to use comments stored directly in the WebI report (see http://scn.sap.com/docs/DOC-48935 ) . This kind of solution, even if it is appropriate for several usages such as the distribution of a single WebI report within the company, does not allow to deal with situations where a real centralized storage of the information is required in order to be able to reuse it in several dynamically built reports.

In order to make the comments (or other text information) available to all the applications, SAP proposes its “Commentary Solution” which stores the data directly on the BW backend (see http://fr.slideshare.net/PeterScheffelt/sap-bi-bo-commentary-solution ).

The comments are stored as documents in the BW knowledge database and each document is associated to a set of infoobjects which constitute the unique key to retrieve the document. SAP proposes the integration of this solution for different reporting tools as Dashboards or Crystal Reports. The retrieving or the storage of the comments is possible thanks to a web service developed by SAP and installed of the BW backend.

As WebI doesn’t allow the call of web services, the integration of this solution is not directly possible. We will now discover how this solution was integrated by Aosis in several WebI reports.

2. Integration of SAP “Commentary Solution” with WebI reports

Aosis developed several interactive dashboards using SAP Dashboards product (former Xcelsius). These dashboards allow to select data, represent them as tables or charts, navigate from report to report and store and retrieve user comments. The comments are stored using the SAP “Commentary Solution” which has been enriched in order to manage comments workflow and status.

Yet, Dashboards product appeared unsuitable when it came to generate a standalone multipage report that could be printed or distributed in order to be exploited in an off-line mode (without connection to the BW server).

WebI product has been selected for this usage but a solution had to be developed in order to retrieve all the relevant comments.

As the existing web services cannot be used by WebI, the data has to be retrieved using only a standard Bex query.

The main issue with the use of a standard Bex query, is that BW (until version 7.4) support a maximal length of 60 chars for the characteristics. This make necessary to transfer the comments using blocs of 60 chars and then rebuilt the original comment in WebI. The “slices of comments” could be stored as columns in the query, but this requires to fix a maximal length for the comments and make the provision of a sufficient number of column to store all the slices.

We will then store the “comment slices” as query rows, generating automatically as much rows as necessary to store the complete comment. Then the next challenge will be to concatenate all the rows in one single variable that can be displayed as a label in the report.

2.1. Building the Bex Query

A Bex query is not able to retrieve directly a document stored in the BW knowledge database and slice it in 60 chars packets. This kind of job must be done by a function module.

The developed solution is the creation in BW of a Virtual Infocube based on a Function Module. Our Bex query will access this virtual infocube through a dedicated multiprovider and, every time the query called, the associated function module is started, it fills the infocube and the data can be retrieved. Within the infocube we create two specific dimensions, one to store the characteristics that represent the comment unique key (it might be for example calendar month + KPI), and one to store comment data. Comment data uses to field, one 60 chars characteristic to store the slice of text, and one integer that stores the slice number in order to be able to rebuild the original data concatenating the slices in the right sequence.

Then the process is rather simple:

  • the query is called from the WebI report, passing in the prompt the required key associated to the needed comment,
  • the virtual infocube function module is started (it receives the prompt values). At this stage it has been decided to use directly the SAP provided function module to retrieve the document content, but any other self-developed function module could also easily do the job,
  • the virtual IC function module receives the comment data and split it in 60 chars packets using a iterative process, adding an new record (together with the associated packet number) in the virtual IC,

the Bex query retrieves all the rows from the virtual IC and send back the data to the WebI report.

blog2014-08_bex-query

2.2. Processing the Bex query returned data in the WebI report

At this stage we have run the following datasource:

blog2014-08_webi-query

and all the needed data available in a WebI measures. The data are structured the following way:

Line number comment Comment line
1 Part_1
2 Part_2
…… …….
n-1 Part_n-1
n Part_n

 

The last challenge is now to concatenate all the rows within one single variable, not knowing how many rows can be returned.

This can be done easily defining the three following variables (warning: variable type must be necessarily as specified) and a recursive formula:

  • Dimension : CommentLineTmp=[Comment line]
  • Measure : KpiCommentElement =[ CommentLineTmp]
  • Measure : KpiComment=[KpiCommentElement] Where([CommentLineTmp]=[CommentLineTmp])

We are done! The final comment is now available in [KpiComment] measure.

blog2014-08_webi-comment

3. Further steps

Some additional tunings are not presented in this paper and may be required, as for example the handling of Carriage Return / Line Feed which may interfere with the process if they are not stored in the BW document using the right coding convention (the one expected by WebI). The easy solution is to replace these CR/LF in the BW function module by a dedicated escape sequence (i.e. \n) before splitting the data in 60 chars packets, and then replace this sequence by Chr(10) applying the WebI Replace function to the concatenated comment.

The solution can be easily extended to take into account (in a specific dimension of the virtual IC and in the Bex query) additional fields that could have been created to enrich the original SAP solution (as comment author, status, …).

This paper makes reference to the SAP “Commentary Solution“, but it is applicable in the same way if the user develops his own low level document storing function.

Only retrieving of the comments is presented in this paper as the same principle cannot be used to store comments in the BW repository. The storage could be implemented from WebI using an OpenDocument jump to a Dashboards report passing the values for all the comment keys. Then this Dashboard can store the comment calling the appropriate web service. But this could be a topic for a next paper…

 

 Christian Choinkowski