Skip to Content
Author's profile photo Former Member

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:

hyperlinkWizard.png

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:

1_Material.png

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:

1_Material_Internal.png

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):

/wp-content/uploads/2013/03/2_single_193920.png

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:

3_MSV.png

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:

4_Range.png

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

5_Hierarchy.png

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:

/wp-content/uploads/2013/03/6_compound_193926.png

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

Assigned Tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks for the blog post.

      However, the following isn't completely clear to me:

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

      Am I right when stating that internal key refers to the use of .key function and the internal format refers to the key attribute that it selected in the Query Panel?

      Thus, using .key would be faster than selecting the key attribute from the Query Panel?

      Just making sure I understand correctly. Thanks in advance!

      Kristof

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Kristof,

      Thanks for commenting.

      Exactly, when you use the .key function you get an internal key format. The external format on the other side refers to the key attribute that you can select in the query panel.

      Using .key (instead of using the key attribute) results in a faster parent report because you do not need to retrieve an extra (redundant) attribute for this. Regarding the child report, I do not think that you will see a difference in performance if you start it with the internal or external format.

      Best regards,

      Victor

      Author's profile photo Former Member
      Former Member

      Hi Victor,

      I have followed the above to pass the parameters from Report 1 to Report 2. But, my results are coming slightly different.   I am using the Bex variable "Top Level Employee" for Ex: 'X' who will have few more employees under his org structure for ex: 'A,B'. After applying the document link to the employee number, If I click on the employee for ex: '10003' it should bring me same employee info in Report 2 i.e. '10003' instead it is bringing the info about 'A, B'. Please help me if I need to do anything differently to achieve the desirable results.

      Document Prompts: Employee (text), Key: Employee Key

      I really appreciate your help.

      Thanks,

      madhavi.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Madhavi,

      In that case please open a thread with Information about your BW and BO Version. Screenshots of your BEx Query and Webi Report would be good for common understanding. 

      Best regards,

      Victor

      Author's profile photo Former Member
      Former Member

      The official documentation on OpenDocument at http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_opendocument_en.pdf says on page 26 that range parameters (lsR) are not supported by OLAP Intelligence reports. Yet in your post you demonstrate passing ranges to a Bex-based report. Can you please expand on how to pass ranges and their keys/indexes at the same time to a Bex-based report? Specifically, to a Bex variable. I've tried several combinations of parameters with/without index, single/range value, and haven't been able to get it to work. Not sure if I'm doing something wrong or if this is by design, as the documentation says.

      Thanks.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello Pedro,

      my blog refers to the Web Intelligence product, not to OLAP Intelligence (which was the name of a product long time ago).

      What you would like to do should work fine following the syntax above. I recommend you to open a new thread and explain with some screenshots what have you tried and where you are stuck, version information, connectivity type etc.

      Best regards,

      Victor

      Author's profile photo Henry Banks
      Henry Banks

      there's some good feedback about RRI in this discussion here http://scn.sap.com/message/14379150#14379150 .  the forum SAP BusinessObjects Analysis, edition for OLAP  is where you need to be asking your question

      Author's profile photo Henry Banks
      Henry Banks

      this blog is the real deal . top stuff

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      I appreciate your feedback Henry, thanks.

      Author's profile photo Former Member
      Former Member

      Hi

      I have been using document link since XI 3.1 but in 4.1 i cant make a document link to work to return to the original report when the using new window in BO preferences and actual windows on link preferences.

      Have you experimented this?

      thanks

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Patricio,

      I have not experienced those issues, I encourage you to open a new thread and add some more details and probably screenshots of your case.

      Best regards,

      Victor

      Author's profile photo Former Member
      Former Member

      Hi,

      This post is really precious to me and the behaviour we are expecting from opendocument.
      Do you know what is the solution to pass exclusion in the Opendocument ?

      I want to pass the <>"Not assigned" and it is not working.

      Thank you for your help.

      Eric

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Eric,

      thanks for leaving a comment.

      The documentation does not list a not equal operator, so I dont think you will be able to do that.

      Best regards,

      Victor

      Author's profile photo Former Member
      Former Member

      Thanks Victor for your confirmation.

      The only workaround I have found was to have a prompt "not equal to" in my destination report and I do the opendocument with = "Not assigned". The prompt property is then converted into <>"Not assigned".

      BR,

      Eric

      Author's profile photo Koen Hesters
      Koen Hesters

      Hi,

      I added this document to my document about all open documents link, if it is okay for you.

      Open document lingo (hyperlink) through the whole BI platform incl Design Studio & Lumira

      Grtz

      Koen

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Koen,

      yes, of course fine with me.

      BR

      Victor

      Author's profile photo k deshmukh
      k deshmukh

      Hi,

      I am really happy that i came across this blog. This blog finally provided me solution after struggling for more than three days. The below statement provided me the needed solution.

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

      Once again thanks a lot.