Wednesday, January 4, 2012

Graphical BI Reports in SAP CRM

An often heard request is to have graphical reports in SAP CRM.
A logical request, as good Customer Relationship Management partly relies on knowledge of the customer, which sometimes is best shown using a graphical report.

There are of course several out-of-the-box reporting functions in SAP CRM, such as interactive reporting, which uses a BI-client on the CRM server and contains standard reports on several objects in the CRM system, and also the in EhP1 introduced Xcelsius Dashboards and BO Explorer integration.

But for those of us who are not yet on EhP1, do not have BO's latest BO Explorer, would like to report on more than just CRM data, and have a functioning BI system in place... Here is how to implement a BI graphical report in SAP CRM using a WebTemplate.

Web Templates
Web templates can be created in SAP BW in the Web Application Designer (WAD). In a Web template, you determine from which data provider(s) and in what way the BW data is displayed. You can show the data as a table (the result of a query) but also as a graphic (line, pie chart, column bar, etc). All these settings are thus part of the web template.

Basically, all information concerning what is reported on, and what the report looks like is stored in the template. An end-user will not be able to for instance change what the report looks like. You can define variables in the template, but these will be filled by the assignment block embedding the WebTemplate.

As mentoined, it is also possible to show the data from the query as a graphic. Suppose you have several key figures in your query like:
  • Sales turnover last 5 years per material group
  • The margin per customer. 
You can assign a stacked column bar to the turnover key figures for each material group (via restricted key figures in the query) and a line graph to the margin.


When a webtemplate is ready, it will be executed in the browser. The selection parameters from all the queries in the webtemplate are displayed and needs to be entered. After that the webtemplate can be executed and all defined graphs and data tables will be displayed. The input parameters (customer, credit control area, etc) which are entered manually in the webtemplate, will be passed from CRM when the template is embedded in the customer factsheet or customer overview screen.

Embedding in SAP CRM
Now that we have the WebTemplate in BW, we still need to embed this in a CRM screen. Logical examples would be to embed this in an assignment block in the Customer Overview screen or in the Customer Factsheet.

A specific component called GSBIRP has been supplied specifically for this function. Also, some customizing has been made available in the IMG in SAP CRM.

First we do the customizing in IMG
Customer Relationship Management under UI Framework --> UI Framework Definition --> Display SAP NetWeaver BI Reports in CRM
Then we add a usage to component GSBIRP in the runtime repository on the component we would like to embed the report in in the webclient component workbench in the CRM system. Add Interface View MainWindow with inbound plug DISPLAY.

So, now we have added the component GSBIRP to the component we would like to show the report or graph in, but we still have not told the system which WebTemplate we would like to see, nor have we passed parameters to the webtemplate. In order to do this, you have to redefine the WD_USAGE_INITIALIZE of the component controller of the component where the GSBIRP has been embedded. Apparently, this needs to be done by enhancing the component. If anyone knows a better way, feel free to contribute :-).

WD_USAGE_INITIALIZE
So, we enhance the WD_USAGE_INITIALIZE, and we add the following coding:

METHOD wd_usage_initialize. 
DATA: lr_context TYPE REF TO cl_bsp_wd_context_node. 

CASE iv_usage->usage_name. 
   WHEN <'your usage description'>. 
       TRY. 
          lr_context = iv_usage->get_context_node( '<your usage desc>' ).
          lr_context->set_s_struct( 
               attribute_path = '' 
               component = 'REPID' 
               value = '<Your report name>' ) .
        CATCH cx_root. 
       ENDTRY.
   WHEN .............
   ENDCASE. 
ENDMETHOD.

Great! So now we have a working report in for instance the customer overview screen... But what is the use if don't pass any parameters to the WebTemplate. We can now only show the same report on every page, independent of the context it is currently in... So let's see how to pass a parameter from the CRM system to the WebTemplate.

As you can see when specifying the WebTemplate's name, we used a method called set_s_struct with parameter component = 'REPID'.

In order to add parameters to the webtemplate call, we use the same method, but now with component = 'ADDPARAMETER', so:


          lr_context->set_s_struct( 
               attribute_path = '' 
               component = 'ADDPARAMETER' 
               value = '<The string with parameters you would like to add>' ) .


Building a 7.x webtemplate parameter string
In case of a 7.x BI WebTemplate, the string of parameters will have the following layout:


In order to make life easy, I would suggest to implement the following method which will take care of building the whole parameter string.

class ZCL_BI_IF definition 
public 
final 
create public . 

*"* public components of class ZCL_BI_IF 
*"* do not include other source files here!!! 
public section. 

methods ADD_PARAM 
    importing !IV_PARAM type STRING 
                  !IV_VALUE type ANY .
methods GET_PARAM 
   returning 
       value(RV_BI_PARAM) type STRING .

METHOD add_param. 

me->gv_index = me->gv_index + 1. 

CONCATENATE 
  me->gv_bi_param 
  '&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_' 
   gv_index 
   '-VARIABLE_TYPE=VARIABLE_INPUT_STRING' 
   '&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_' 
    gv_index 
   '-VARIABLE_TYPE-VARIABLE_INPUT_STRING=' 
   iv_value 
   '&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_' 
   gv_index 
   '-VARIABLE=' 
   iv_param 
INTO me->gv_bi_param. "#EC NOTEXT 
ENDMETHOD


METHOD get_param. 
CONCATENATE 
'&BI_COMMAND_1-BI_COMMAND_TYPE=SET_VARIABLES_STATE' 
me->gv_bi_param 
INTO me->gv_bi_param. 
CONDENSE me->gv_bi_param NO-GAPS.
rv_bi_param = me->gv_bi_param. 
CLEAR: me->gv_index, me->gv_bi_param. 
ENDMETHOD.

Now use class ZCL_BI_IF method ADD_PARAM to create the parameter string where you simply add the iv_param and iv_value as parameters to the method, where iv_param is the fieldname and iv_value is the parameter value where for instance iv_param = 0me_customer and iv_value = [customernumber].

Now when calling set_s_struct with component = ADDPARAMETER, retrieve the string you have built up using the get_param method.

A Fully functioning enhanced WD_USAGE_INITIALISE would now be:
METHOD wd_usage_initialize. 
DATA: lr_context TYPE REF TO cl_bsp_wd_context_node. 

CASE iv_usage->usage_name. 
   WHEN <'your usage description'>. 
       TRY. 
          lr_context = iv_usage->get_context_node( '<your usage desc>' ).
          lr_context->set_s_struct( 
               attribute_path = '' 
               component = 'REPID' 
               value = '[Your report name]' ) .
* get the current context 
coll_wrapper = me->typed_context->partner->get_collection_wrapper.
entity ?= coll_wrapper->get_current( ).
if entity is not initial.
entity->get_property_as_value(

EXPORTING iv_attr_name = 'BP_NUMBER'
IMPORTING ev_result = lv_partner ).

endif.


[use the add_param method here].
          lr_context->set_s_struct( 
               attribute_path = '' 
               component = 'ADDPARAMETER' 
               value = '[use the get_param method here') .
      CATCH cx_root. 
       ENDTRY.
   WHEN .............
   ENDCASE. 
ENDMETHOD.

Special Thanks.

Special thanks to colleague and friend Sander Boers of XCLR8 for supplying the code for building the parameter string for the 7.x webtemplate and Sander van Dillen of Acorel for his contribution on the ins and outs of WebTemplates :-).
 


1 comment: