How to use OpenDocument with BEx variables

While the official OpenDocument guide is a great resource and explains in detail how to use OpenDocument syntax, I have not seen a good documentation explaining how to use OpenDocument in conjunction with SAP BW BEx variables, the fact that KBA 1838045 points to this blog post reinforces my belief that there is a need for a good documentation. In this blog post, I would like to share my experiences in this regard.

Update September 2013: The OpenDocument guide for BI 4.1 has now been greatly enhanced with examples based on BEx variables, make sure you read that as well.

What is the OpenDocument interface?

The OpenDocument interface allows you to open reports that are saved in the BI LaunchPad by using URL hyperlinks. You can take advantage of the OpenDocument syntax and parameterize the report to be opened.

How to pass values to BEx variables in general?

As everything in SAP BW, there is always a Text and a Key for every record in your report. The text is clearly not unique though, it depends for example on the language of the user that is currently viewing the report. On the other side most of the users are not very comfortable seeing and working only with technical keys, you should always pass both: Key and Text.

How do I construct an OpenDocument URL in general?

While you can construct your own URL manually by carefully reading the manual, there is much fancier way: Use the hyperlink wizard! The hyperlink wizard is available only in Web Intelligence Web Mode (DHTML), it is not available in RIA mode (Java) nor in the Rich Client. You can open the hyperlink wizard by right-clicking on a field->Linking->Add Document Link:

After you select a target document in the hyperlink wizard, you will be presented with all the prompts of the target report. In this example I have a prompt (from a BEx variable) on a dimension object named “Material”, an example of a material is a material named “Ten” with key “10”.

How do I pass keys (using external format)?

The following is a correct way of passing a single material by using the dimension objects in a table:

In the first box you should provide a Text (Dimension Object), and in the second its corresponding key. In the screenshot above “Z_SINGLE_OPT_TEXT” corresponds to the description of the BEx variable, on the other side, the generated hyperlink will use the technical name of the variable.

How do I pass keys (using internal format)?

As alternative to using key attribute object, you can pass the internal key by using following syntax:

Normally you would pass an external key (e.g. 10) that SAP BW will automatically convert to an internal key (e.g. 010) before actually doing the filtering on its database. The standard conversion routine for characteristics in SAP BW is called ALPHA and will simply fill in missing characters in numeric keys with zeroes. In this example the length of the material object is 3, if you try to filter with ’10’ the alpha conversion routine will internally convert that into ‘010’ and actually do the filtering with the latter value.

Passing the internal key has several advantages when compared to internal format:

-Better performance in parent report (no need to retrieve key attributes)

-Uniqueness (rare cases but valid)

-Less error prone (see below)

If you use the UserResponse function in conjunction with ‘Index’ to retrieve keys (as recommended in following KBA: 1838045), you are now using the internal format, thumbs up!

How do I pass Single Values?

If you use the hyperlink wizard to pass a single value variable, you will notice that the wizard will use two parameters  lsS for the Text and lsI for the key. If for example you click on a material “Ten” with key “010” using a variable “Z_SINGLE_OPT” (technical name):

You will get a syntax like this:

“…lsSZ_SINGLE_OPT=Ten&lsIZ_SINGLE_OPT=010”

Notice that in this example I have used a fixed value for a better understanding of the needed syntax , but of course you can use dimensions, variables or formulas here.

How do I pass Multiple Single Values?

By separating individual values with a semicolon (;), you can pass multiple single values at a time. For example:

The corresponding syntax equates to:

“…&lsMZ_MSV_OPT=Ten;Fourty&lsIZ_MSV_OPT=010;040”

Notice that lsM is used to pass multiple single values. Notice that in this example I have used a fixed multiple values for a better understanding of the needed syntax , but of course you can use dimensions, variables or formulas here.

How do I pass Ranges?

When passing ranges you must separate the minimum and maximum value with two consecutive dots (..). The start and end of the range must have square brackets that you have to create manually:

Generated syntax:

“…lsRZ_RANGE_OPT=[Ten..Thirty]&lsIZ_RANGE_OPT=[010..030]”

Notice that lsR will be used to pass ranges. Notice that in this example I use a fixed value range for a better understanding of the needed syntax , but of course you can use dimensions, variables or formulas here. When using a variable, make sure that you add the brackets and dots as in following example:

var_toPassKey=”[“+[Material].Key+”..”+[Material].Key+”]”

How do I pass nodes of a Hierarchy?

Passing a hierarchy node with standard text and key objects (external format) works fine most of the times, however I use following syntax (according to KBA 1677950) to avoid potential issues by leveraging the internal format: [Hierarchy].key

In this case you will notice that a “0HIER_NODE/” will be inserted to all non-leave keys in the hierarchy.

Generated syntax:

„…&lsSZ_HIER_OPT=Even&lsIZ_HIER_OPT=0HIER_NODE/EVEN”

Using the internal format can be very important e.g. in cases when you have a leaf and a parent node with the same external key, this case is only unique when you use the internal format for passing the key.

How do I pass compounded objects?

In this case you need to make sure that the key that you are passing is not compounded (more Information in this blog post). You can achieve this by using formulas in the front-end e.g. “=right()” or by selecting the non-compounded key available since BI4 SP05:

I hope you enjoyed this introduction into using OpenDocument with BEx variables