I wrote an article earlier this year regarding the use of the @Variable universe function in the END_SQL universe parameter to help DBAs identify Business Objects queries (see related article Identifying SAP BusinessObjects queries using END_SQL) . The @Variable function can also be used in the SELECT clause of objects for display to the user or in the WHERE clause to restrict data. For example, in my presentation Secure Universes Using Restriction Sets, I implemented row-level security on the eFashion universe using @Variable('BOUSER'). Row-level security can also be implemented inside of the universe by the use of a mandatory condition, a great new feature introduced in Designer XI 3.0.
NOTE: The Designer application from XI R2/XI 3.0/XI 3.1 has been renamed in BI 4.0 as the Universe Design Tool.
The SAP BusinessObjects XI 3.1 universe designer manual (click to download from SAP Help Portal) documents for the first time several new system variables. It’s unclear whether the variables were introduced with XI 3.0 (they’re not documented in the XI 3.0 edition of the universe designer manual) or were simply undocumented in previous releases. While on the subject of documentation, allow me to mention that Dave Rathbun elegantly describes several previously undocumented attributes to the @Prompt function (see Dave Rathbun’s article Designer XI 3 New Feature: Extended Prompt Syntax) that are finally documented in the XI 3.0/XI 3.1 universe designer documentation (p. 537-538).
The built-in @Variables for XI 3.1 are BOUSER, DBUSER, DBPASS, DOCNAME, DPNAME, DPTYPE, UNVNAME, and UNVID. To use them, place them inside of single quotes as a parameter to the @Variable function. It is important to note that @Variable is a universe function (along with @Prompt, @Select, @Where, etc.) to be used in the Universe Design Tool (Designer), not a report-level function to be used within Web Intelligence.
I created some objects in a universe to demonstrate each @Variable. Their values can be seen in the Web Intelligence report below. One minor lesson learned during the creation of this blog post: I had originally named the Web Intelligence document Using @Variables, but this wreaked havoc with SQL generation because I was also using @Variable('DOCNAME') in the END_SQL of the universe. A minor recursion problem, apparently. That is why the sample Web Intelligence document is instead named Using AT Variables.
The @Variable('BOUSER') returns the name of the InfoView user running queries in the document, which in this example is DMarks. Prior to XI Release 2, there was a @Variable('BOPASS'), but it has been depreciated due to security concerns. Similar to BOUSER/BOPASS, @Variable('DBUSER') and @Variable('DBPASS') return the username and password only if the user has database credentials enabled in their user profile in the CMC. If the database username/password is defined by a universe connection, these @Variables will be blank.
@Variable can also be used to return information about the current report. The @Variable('DOCNAME') is the saved name of the report. The @Variable('DPNAME') returns the name of the data provider, as defined in the Query properties in the Web Intelligence Edit Query panel. In the screen shot below, I have renamed the default Query 1 to My Data Provider.
The @Variable('DPTYPE') describes the data provider type. I was unable to find an enumerated list in the documentation, but a standard universe on a relational database has an @Variable('DPTYPE') value of DPUNIVERS. I can only speculate that universes constructed from stored procedures or OLAP cubes probably have different values.
The @Variable('UNVNAME') returns the name of the universe as defined on the Parameters tab of the Universe Properties. I lamented that XI R2 did not have a variable (at least not documented) to identify the universe, so this is a welcome addition. In my example, the name of the universe is Dashboard.
The @Variable('UNVID') is a new variable in XI 3.1. It returns the ID of the universe object, which is listed next to the CUID in the CMC. The universe in this example has an ID of 1303.
Beginning with XI 3.1 SP2, universe designers can use two new locale variables. @Variable('PREFERRED_VIEWING_LOCALE') is the user’s Preferred Viewing Locale, the locale chosen by the user to display metadata and data in his reporting tool. @Variable('DOMINANT_PREFERRED_VIEWING_LOCALE') can be used to categorize or roll up preferred viewing locales.
SAP BusinessObjects Business Intelligence 4.0 supports the following XI 3.1 @Variables: BOUSER, DBUSER, DOCNAME, DOMINANT_PREFERRED_VIEWING_LOCALE, DPNAME, DPTYPE, PREFERRED_VIEWING_LOCALE, UNVNAME, and UNVID. BI 4.0 also adds a new variable DOCID and CMC-defined user attributes. The @Variable functions can be used in classic UNV universes created by the Universe Design Tool (formerly Designer) or the Information Design Tool. These functions are documented in the SAP BusinessObjects Business Intelligence 4.0 Information Design Tool User Guide on the SAP Help Portal.
The last item I’d like to bring up isn’t a universe-level @Variable, but a new Web Intelligence function that has been sorely missed and a welcome addition to XI 3.x. The ReportName() function returns the name of the current report tab in the Web Intelligence document. I’ve often wanted to use the name on the report tab in the report title – and now I can. SAP liked this new function so much that it is used for the default report title cell in Web Intelligence 4.0.
@Variables have many applications and I hope this article will help you take advantage of them in your universes.
Great coverage of this topic Dallas, a good reference for any universe/report designer.
- Josh
Great coverage of this topic dallas. I love these stuff.
I have also noticed that you can use the @Variable function (Webi and Deski Only) to reference an @Prompt function. For example, if you have the following function: @Prompt(‘MyPrompt’,'A’,'Class\Dimension’,Multi,Free) used in a query, you can reference it again using @Variable(”) or @Variable(‘MyPrompt’). I have used this in the past to shorten the syntax of “Optional Prompts” (Prior to XI 3.X).
Example:
(@Prompt(‘MyPrompt’,’A’,{‘All’,’Value1’,’Value2’},Mono,Constrained,Not_Persistent,{‘All’}) = ‘All’ OR Table.Field = @Variable(‘MyPrompt’))
DBUSER and DBPASS variables have existed since BOE XIr2 in the “Crystal platform” line of products, and well before that in the “classic BOBJ” line (the one terminating with E6.5) line of products.
I believe BOUSER also existed in XI.
BOPASS hasn’t been “deprecated” per se – it cannot function for architectural reasons in the “Crystal platform” versions of BOE – in particular, the user password is stored as an irreversible hash. So it is not deprecated so much as “not present at all”.
UNVName gets the Universe name from the Universe parameters. I need to get the Connection name from the same screen – our Universes can be pointed to dev, test, or prod, depending on where we are in development. Any way to get the connection name into a Universe variable?
hi Dallas!
i need some help with a simple formula, i need to create a password in Oracle from Designer, i used a table with usernames and i changed to a passwords that in webI report users can type it, but my problem begins when I try to make a password for an administrator, for example:
CASE WHEN USERS_TABLENAME.USER LIKE ‘USER1′ THEN ‘PASSWORD1′ ELSE WHEN USERS_TABLENAME.USER LIKE ‘USER2′ THEN ‘PASSWORD2′…..
HOW I CAN MAKE AN ADMINISTRATOR PASSWORD THAT CAN SEE ALL USERS REPORT??
Antonio, please consider posting your question on the BusinessObjects Board or SAP Forums. Thanks!
what if there are single quotes in the webi document name? Let us say name of the BO report is: New Release F’cast vs Initial Ship Actuals. How will single quotes be escaped? In the current example the word F’cast
Prabhu,
Take a look at the BusinessObjects Board (BOB) or SAP Community Network Forums to see if your question is already answered there.
Regards,
Dallas
How do I retrieve the document properties, especially the Comments box?
Keith, thanks for writing.
I do not know the answer to your question – I would recommend posting it to the BusinessObjects Board or SAP support forums. If it’s not currently a feature, consider posting it on the SAP Idea Place for others to vote on.
Dallas
Any ideas on how to restrict the access to reports data for BO Admin? Something like checking the BOUSER and put a filter for Admin users. But as admin would have access to universe it does not work 100%. Any thoughts?
Vidyali,
Thanks for writing. Please consider posting your question on the BusinessObjects Board.
Thanks,
Dallas
Q. i know @variable allows you to pick an item from a column but how does it allow to to pick for several items in that column in a group
Eric,
That’s a great question to post on the BusinessObjects Board or SAP forums.
Hi Dallas,
I am trying to find the OLAP connection for a BEx query that a WebI report uses in its query …… I do not see a easy way to find it.
Some people suggested to use a variable …… Connection(DataProvider([Obj]))……. and find connection name. But that does not work for BEx objects. This is because BEx objects are shown either as “detail” or as “hierarchy” in query webI panel. To make the above variable work, I need to use [Obj] which is a pure Dimension object…… so with all my best efforts from the LaunchPad using WebI I do can not find which connection is used for a BEx query (forget about locating the folder path for the BEx query.)
What is the alternative? Use CMC to browse to the report>Righ Click>Tools>Check Dependency.
So I think having access to CMC is the only answer for finding which OLAP connection is used in a report that pulls data from BEx.
Do you have any other thoughts or alternatives to find this info?
Aurobindo,
I don’t have a good off-the-cuff answer, so I’ll ask my peers. In general, I’ve been disappointed with the Connection function, even with relational sources. And it’s near useless on multi-source universes. In the meantime, I would recommend opening a support case with SAP. Please post here if you find any answers.
Regards,
Dallas
Hi,
Really interesting article and responses. I’m interested in knowing if there is a way to use @Variable() or any other function at universe level or report level to get the Job Server name that was used to run the report. We have a TEST and LIVE environment, and users would like to see which environment was used to run/schedule the report. Is there any way I can get the job server returned?
Thanks in advance for the help.
Regards,
Owais
Owais, have you considered using the audit database to determine the job server information?
HI all,
can We Use @variable Dimension in Hierarchy to drill down in webi report?
With regards,
Chintan Vora
Chintan, I’ve never needed to do this, but it should theoretically work. You may want to bind a very small extra table to the object so it not only parses but can be added to a report by itself.