Semantic Layer

Using @Variable Functions in the Universe

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: Starting with BI 4.0, the Designer application from XI R2/XI 3.0/XI 3.1 is now known as the Universe Design Tool.

The SAP BusinessObjects XI 3.1 universe designer manual describes 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.

variablereferencetable

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.

@Variable Web Intelligence Report

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 for security reasons. 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 CMC Database Credentials

@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.

@Variable Renamed 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 it’s a welcome addition. In my example, the name of the universe is Dashboard.

@Variable Universe Parameters

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.

@Variable Universe ID

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.

Dallas Marks

Dallas Marks

I am an analytics and cloud architect, author, and trainer. An AWS certified blogger, SAP Mentor Alumni and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting.

Related Posts

47 thoughts on “Using @Variable Functions in the Universe”

  1. Great coverage of this topic Dallas, a good reference for any universe/report designer.

    – Josh

  3. 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’,’ClassDimension’,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’))

  4. 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”.

  5. 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?

  6. Pingback: Tips and Tricks: Identifying Business Objects queries using END_SQL - My Life With BusinessObjects

  7. 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??

  8. 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

  9. How do I retrieve the document properties, especially the Comments box?

    1. 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

  10. 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?

  11. 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

  12. 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?

    1. 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

  13. 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

  14. HI all,

    can We Use @variable Dimension in Hierarchy to drill down in webi report?

    With regards,
    Chintan Vora

    1. 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.

  15. Hi Dallas, Is there a way to create a universe object like UserResponse() function of Webi ?

  16. Great blog. Would you mind sharing the select statement to create a universe dimension using @variable, I.e @variable(‘BOUser’) ? I am getting a syntax error however I tried. Thanks.

    1. Tom,

      Thanks for writing. By default, the object will not parse in the Universe Design Tool or Information Design Tool but should work when used in Web Intelligence. You can trick the object into parsing by associating extra tables in the object definition. For example, when using Oracle you can add the SYS.DUAL table, which only contains 1 row, to the object definition.

      Regards,
      Dallas

      1. Thanks, Dallas. Before pulling my hair out all night again, this is what I will put into the object definition: Select @Variable(‘BOUSER’) from sys.dual. Is the syntax correct ? Thanks again.

  17. Hi

    How to restrict LOVs for a perticular user as we generally restrict LOVs in Universe for all the users. Can you please explain how to do it for a particular user.

  18. Pingback: 2014 Year In Review

  19. Hi Dallas Marks,
    Org. level data restriction works fine in BW but come out unrestricted in Web intelligence. Please, what is the solution?

    1. I don’t have a good answer for this question. I’d encourage you to open a SAP support incident or post the question on the BusinessObjects Board or the SCN Web Intelligence forum.

  20. Hi Dallas,

    I have used the @variable to accept text input to be displayed in the report footer.But I can enter only 80 chars in the prompt field. Is 80 the max limit of this text prompt or can I change the max number of chars entered some where in the universe?

    Thanks,
    Subodh

    1. Subodh,

      Thanks for writing. I can’t remember a situation where I needed such a large prompt value. I can’t find any references in the documentation clarifying that a limit exists. Does using @PROMPT have a different or better result?

      Regards,
      Dallas

  21. Hi Dallas,

    so we are facing where we have tokenized data int eh database of the warehouse ( PCI data) we are needing the ability to tokenize the incoming where clase values for certain fields in teh where clause. is ther a way to set rules to apply a custom function to incoming where clause fields on the fly in a universe ?

  22. Hi Dallas,

    Can you please guide me how should I use Upper function with @Variable? I have a security table that I am trying to use (Schema_name.Table_name0.NETWORK_ID) = Upper (‘abc1’) I am getting no result when I run the query in Toad. However without Upper function I can see the results.

    Thank you Moin

    1. Moin,

      Unfortunately, I cannot provide technical support. I recommend posting your question on the BusinessObjects Board, an appropriate SAP Community Network forum, or opening an incident with SAP support.

      Regards,
      Dallas

  24. Hello,

    We are performing a migration from BO XI to BO 4.2.
    On BO XI version the BO authentication is performed with BO username and password but, the DB access uses a BO user concatenated with “.DB” suffix.

    Whenever the user logs into BO XI, the nominal user is stored in an internal BO (BOUSER) variable.
    The concatenation of this variable with the nominal user is defined on all connections created in the Universe Designer (@variable(‘BOUSER’).DB).
    When a query is executed in Desktop Intelligence, the DB is accessed with .DB.
    However, we cannot replicate this logic on BO 4.2 version because the variable @variable(‘BOUSER’) is not being recognized as the BO User.
    Could you help me find a solution/alternative on BO 4.2 for this?

    Thanks in advance,
    Inês.

    1. Ines,

      Thanks for writing. Can you elaborate on what @variable(‘BOUSER’) is doing in BI 4.2? Is it blank? Which support pack and patch are you using? I’ve tested BOUSER in BI 4.2, so perhaps it’s your patch level?

      I see in your questions that BOUSER is surrounded by fancy single quotes. Make sure in your universe that the quote character is the standard one – sometimes Outlook or another application decides to automatically change the quote characters to something fancy (but not valid in the universe) when you cut and paste code samples.

      Dallas

      1. Hello Dallas,

        Thanks for the quick response.

        I’ve performed the following steps:

        1- On IDT, cnx Connection, I selected the option “Use BusinessObjects credential mapping”
        2 – On CMC, I chose the option “Enable Database Credentials” and I’ve filled the database credentials with
        Account name= @variable(‘BOUSER’) and respective DB password
        3 – When I ran a query on Webi the following error message appeared “ORA-01017: invalid username/password; logon denied”. My undestanding is that it’s not recognizing this variable. How could I overcome this issue?

        1. Inês,

          All @variables are only resolved in the universe designer – they won’t be resolved in the CMC. For using database credentials, you’ll want to enter the literal Oracle user name, even if it’s identical to the SAP BI user name, into the CMC field.

          @VARIABLE(‘BOUSER’) is commonly used to force a condition into a WHERE clause where the user is only allowed to see certain data, controlled by some kind of lookup table (that you add to the universe) that maps the username to the subset of company data that the user is allowed to see. Another interesting place to use the @variable is in the universe’s END_SQL parameter, which I’ve described in this article. I recommend the END_SQL trick for all universes.

          Hope this helps,
          Dallas

  25. Pingback: Identifying SAP BusinessObjects queries using END_SQL – Dallas Marks

Comments are closed.