This blog is a work around to pass query parameters in background to a BEx report deployed in Enterprise portal. 

To explain this work around simply, the below scenario is taken as example.

Consider a scenario where there are many customers (Info object 0SOLD_TO). Customer will log into Enterprise portal and run their Account statement Report. The customer’s Login ID (Should be same as customer Key) will be passed as filter value to 0SOLD_TO automatically in background. This will prevent customer from accessing others data.  

The following are the step by step instruction to do this work around. 

Create the necessary report in BW with 0SOLD_TO in free characteristics.  

Create a Web template. For creating Web templates use Web application designer. There will be standard web templates like 0ANALYSIS_PATTERN. Remove unnecessary buttons like filter, Navigation pane, New analysis from that template and save as a new template with a name 0ANALYSIS_PATTERN_1. 

Next create an iView of type "BEx Web Application iView" in Enterprise portal.  

In the iView give query technical name, Web template technical name as shown in the below screen shot.

 To restrict report in background we need a string like the one below.

“BI_COMMAND=&BI_COMMAND-BI_COMMAND_TYPE=SET_SELECTION_STATE&BI_COMMAND-TARGET_DATA_PROVIDER_REF_LIST-TARGET_DATA_PROVIDER_REF=DP_1&BI_COMMAND-CHARACTERISTICS_SELECTIONS=&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1=&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1-SELECTIONS=&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1-SELECTIONS-ELECTION_1=SELECTION_INPUT_STRING&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1-SELECTIONS-SELECTION_1-SELECTION_INPUT_STRING=05A05325&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1CHARACTERISTIC=0SOLD_TO&PAGE_ID=1_elZDdTRmeUY4TUtTc21FQm1GUHhjQS0tMFlHQ01IaTZuczd1bWRTQVZsUks5QS0t&REQUEST_ID=7”

Whenever we restrict a query with a parameter, this kind of string passed in background. There is a work around to get this string. For that you need the following things,

 (i)                  A web template with filter option – to give restriction on characteristic so that string can be captured.

 (ii)                A Web Debugging Proxy - which logs all HTTP(S) traffic between your computer and the Internet. This will capture the required string in text view.

  Change the template name to 0ANALYSIS_PATTERN in iView and execute. Give restriction to query as below.   

For this purpose only 0SOLD_TO was kept in free characteristic. After getting the restricted output, go to Web debugging proxy. There you get the string.

Once the string is captured, that can be modified to meet our requirement. The user login ID can be obtained from the command <User.LogonUid>. Replace Restriction parameter value with the command as shown below and remove the PAGE_ID at the end of the string. So the result string will look like this.

“BI_COMMAND=&BI_COMMAND-BI_COMMAND_TYPE=SET_SELECTION_STATE&BI_COMMAND-TARGET_DATA_PROVIDER_REF_LIST-TARGET_DATA_PROVIDER_REF=DP_1&BI_COMMAND-CHARACTERISTICS_SELECTIONS=&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1=&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1-SELECTIONS=&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1-SELECTIONS-ELECTION_1=SELECTION_INPUT_STRING&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1-SELECTIONS-SELECTION_1-SELECTION_INPUT_STRING=<User.LogonUid>&BI_COMMAND-CHARACTERISTICS_SELECTIONS-CHARACTERISTIC_SELECTIONS_1CHARACTERISTIC=0SOLD_TO"

This modified string should be added to iView Application parameter as in the below screen shot. 

iView HTTP Request method should be set to POST method as in the below screen shot. 

Again change the iView name into 0ANALYSIS_PATTERN_1.

Now, when a customer opens the report it will be restricted to his account only.

ISSUE :

Time zone difference causes incorrect data loading from data source -   0CSM_CASE_ATTR.

SOLUTION:

The case extractor 0CSM_CASE_ATTR is being used by many customers successfully with no issues in data extraction. 

In the extractor 0CSM_CASE_ATTR the default time zone is UTC. This is the architectural design where all time fields in the Basis Case Management application are in UTC format. The table SCMG_T_CASE and SCMG_T_CASE_ATTR would have all time fields in UTC format during save/retrieval or any other operation.

In the extractor 0CSM_CASE_ATTR we use the ABAP statement 'CONVERT DAT' which converts the timestamp from local time zone to UTC format. No other format would be supported because then it would cause inconsistencies with the database.

Initially in a similar issue reported by another customer we first were exploring the idea of increasing the time range interval in RSA2 transaction in the customer system directly. But then this range has been set from beginning and has worked fine for all the customers till now. So the issue would not be resolved by this action.

Below notes explain about the Time Zone issues ~
198411  - Current data and information about time zones
741734  - Incorrect times due to the time zone settings
481835  - Analyzing the time zone settings
101726  - Incorrect times and time zone setting
15913    - Timezone problems (local vs. global time)

Refer to the below notes which talk about Daylight Saving issues ~
126692  - Daylight saving time rules are incorrect
102088  - Reducing downtime when changing from summer to winter
7417      - Conversion between winter time and daylight saving

Go through each of the notes in detail (most would have manual instructions to be performed and nothing should be skipped as it could result in inconsistencies).

Additionally if after applying all the above notes the issue still persists then the below notes would be useful -
<1> For issues on CRM cases refer to note ~
       1470994  - 0CRM_CASE_ATTR: additional fields
<2> For issues on Basis Case extraction refer to notes ~
       1281000  - retrieval of data from case extractor taking a lot of time
       1159696  - Not able to get the data using case type from case
       996360    - Case Extractor not able to pull more than 1 data package.

Motivation

In SAP BW, usually there is no easy way of performing set operations (union, intersection, outer joins etc.) of two queries. This is usually a manual activity. Basically, post processing using two query results is difficult. Infosets can be used in some scenarios but these need additional modelling steps and not as flexible. This blog illustrates how we can fetch values from one query and then exclude these selected values from another query.

Business Example

Using technical content, you want to find out all infocubes that exists in the system but with no data being fetched from them over the last two years (via queries say).

Let X = {A1, A2, .. AN} be a query that delivers all the cubes in the system.

Let Y = {A1, A2, A3} be the query that delivers all the cubes in use over the last two years i.e. some data was fetched from them.

You want to find Z = X - Y = {A4, A5 .. AN}.

Steps

1) In query X, create a customer exit variable of type Selection option on characteristic A.

2) Write the customer exit coding in FUNCTION EXIT_SAPLRRS0_001 include ZXRSRU01. We calculate the variable values set at i_step=1. (How customer exit variables are processed is out of scope of this blog)

3) Code sample

Initialization of the request object (Every query execution is represented in BW via a request object).

DATA:

l_r_request TYPE REF TO cl_rsr_request,

l_s_compkey TYPE rszcompkey.

*** Initialization with query Y to be executed from within the ABAP code ***

l_s_compkey-objvers = rs_c_objvers-active.

l_s_compkey-infocube = 'INFOCUBE'.

l_s_compkey-compid = 'QUERY_Y'.   *** executing query Y

l_s_compkey-comptype = rzd1_c_comptp-report.

4) Get the internal query id for QUERY_Y

* Get the compkey

CALL FUNCTION 'RRI_REPDIR_READ'

CHANGING

c_s_repkey = l_s_compkey

EXCEPTIONS

entry_not_found = 1.

IF sy-subrc <> 0.

MESSAGE s792(brain) WITH l_s_compkey-compid.

EXIT.

ENDIF.

5) Create the Request object

CREATE OBJECT l_r_request

EXPORTING

i_genuniid = l_s_compkey-genuniid

EXCEPTIONS

OTHERS = 1.

 6) Call the query Y from within ABAP code

* get the query definition

CALL METHOD l_r_request->get_initial_state

IMPORTING

e_t_dim = l_t_dim

e_t_mem = l_t_mem

e_t_cel = l_t_cel

e_t_atr = l_t_atr

e_t_con = l_t_con

e_t_fac = l_t_fac

e_t_prptys = l_t_prptys

EXCEPTIONS

x_message = 8

OTHERS = 1.

* Set the request

CALL METHOD l_r_request->set_request

EXPORTING

i_t_dim = l_t_dim

i_t_mem = l_t_mem

i_t_cel = l_t_cel

i_t_atr = l_t_atr

i_t_con = l_t_con

i_t_fac = l_t_fac

i_t_prptys = l_t_prptys

EXCEPTIONS

no_processing = 1

x_message = 8.

* read data

CALL METHOD l_r_request->read_data

IMPORTING

e_warnings = l_warnings

EXCEPTIONS

no_processing = 1

x_message = 8.

*close the request

l_r_request->p_r_olap->FREE( ).

7) After fetching the results, assign the result set elements {A1, A2, A3} as exclude 'E' to the exit variable.

* Get the text table from the output handle of the request

ASSIGN l_r_request->n_sx_output-text->* TO <l_th_text>.

loop at <l_th_text> into l_s_txt_n.

clear l_s_range.

l_s_range-low = l_s_txt_n-CHAVL_EXT.

l_s_range-sign = 'E'. *** Excluding

l_s_range-opt = 'EQ'.

append l_s_range to e_t_range.

endloop.

......

e_t_range contains the variable values which will finally be submitted to OLAP to fetch the results of query X excluding values of Y i.e. X - Y.

Thus, in this way you can perform other set operations simply in the customer exit coding itself.

 P.S. For more details on executing BW queries from within ABAP, please refer: http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/43db5ee1-0701-0010-2d90-c3b991eb616c

Abstract

SAP NetWeaver BW 7.30 introduces a new test transaction for creating, running and analyzing MDX statements. The following article explains how you can use the test environment to efficiently use the query language MDX.

Motivation

MDX is a query language for accessing multidimensional data sources. It is the central technology of SAP BW’s Open Analysis Interface.

MDX allows dimensional applications to access OLAP data in a generic and standard-based way. Besides external reporting clients from other vendors, MDX is also used by SAP’s own products, for example by BusinessObjects Web Intelligence or BPC.

As a language, MDX offers a variety of functions that potentially result in very complex statements. Customers or client applications that create their own statements often lack of good editing- and tool support. Therefore, SAP BW 7.30 offers a new test transaction for composing, executing and analyzing MDX statements.

The new test transaction MDXTEST is typically used by developers (working on MDX-based integration for SAP BW), administrators and consultants.

Hands on MDXTEST

The new test transaction MDXTEST consists of three parts:

1. Pane Section
2. Editor
3. ResultSet Renderer

Pane section

The pane section on the left side of the transaction consists of three sub sections.

Metadata browser

The metadata browser exposes the ODBO related metadata of the selected Cube. The selected objects (for example Members or Hierarchies) can be dragged onto the MDX editor. This improves and accelerates the construction of MDX statements. The user sees all the available objects that are available for defining statements.

Function library

The function library provides a list of all available MDX functions and methods. For each function or method, a corresponding code snippet can be added to the editor by drag and drop. The functions in the browser are arranged by on their return types, for example Member, Tuple or Set.

Statement navigator

The statement navigator provides a list of stored statements. By double clicking on a statement, the statement is read from the persistency and displayed in the MDX editor. This allows the user to easily find the stored MDX statements.

Editor

The central part of the test transaction is the editor pane. The editor itself provides a set of new functionality that is known from the ABAP editor such as mono-spaced font for indentation and formatting, line numbering or drag-and-drop of function templates.

Pretty Printer

Most MDX statements are generated by clients. These statements are often not in a readable format. Most of them need to be manually formatted to get a better understanding of statement structure. In addition, the statements are typically quite complex and often consist of a composition of multiple functions. Formatting and restructuring of the statement consumes a lot of time. A built-in pretty printer transforms the text into a “standard” formatting.

ResultSet Renderer

The result of a MDX query is displayed in a separate window to analyze the statement and its result in a decoupled way. Besides the data grid, additional information about the axis and details about MDX-specific statistic events are added to the query result.

Executing a MDX statement

Once you’ve constructed your MDX in the editor, there are two ways of executing the statement:

1. Default: The status bar provides a default execution button. The statements are executed via the multidimensional interface and the default settings.
2. Expert mode: If you need to run the MDX statement via a different interface, then the expert mode is the right choice. The expert mode is available via a button right next to the default execution button.

The expert mode provides the following options:

• Interface: It’s possible to run a MDX statement via several APIs. The most common interface is the default multidimensional API. In addition, it’s possible to run the statement via the flattening or XML/A interface.
• Row restriction: The flattening API allows you to restrict the range of rows that are about to retrieved. Besides a fix from- and to-number, it’s also possible to define a fixed package size. This setting is only available if the flattening API is chosen.
• Display: The rendering of the result can be influenced by the display setting. In general, you can switch off the default HTML rendering. This might be handy if you run performance measurements and you would like to exclude the rendering overhead.
• Debug settings: there are a couple of internal MDX-specific debug-breakpoints which are typically only used by SAP support consultants.

Summary

In this article, you learned about the new central UI for testing MDX statements. The various components of the test environment support you in creating, executing and testing MDX with minimal effort.