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
Thanks for the blog post.
However, the following isn't completely clear to me:
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
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
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.
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
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.
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
there's some good feedback about RRI in this discussion here SAP BusinessObjects Analysis, edition for OLAP is where you need to be asking your question
. the forumthis blog is the real deal . top stuff
I appreciate your feedback Henry, thanks.
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
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
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
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
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
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
Hi Koen,
yes, of course fine with me.
BR
Victor
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.