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