Using the @Variable Function

Objectives

After completing this lesson, you will be able to:

  • Use the @Variable Function

The @Variable Function

The @Variable function is used in the WHERE clause to call the value assigned to one of the following variable types:

  • SAP BusinessObjects system variables

  • Report variables

  • Language (Locale) variables

  • Operating system variables

Insert the @Variable on the operand side of the condition in the WHERE clause for an object. The query retrieves the value for the variable.

Note
  • The @Variable is a monovalue function and cannot be used with the IN or INLIST operators.

  • When the same @Variable function is executed several times in a query, the prompt only appears once.

You can use the @Variable function with the @Prompt function in the same query when the @Prompt function is monovalue

The @Variable Function Syntax

The @Variable function has the following syntax: @Variable('VariableName')

The variable name must be inside single quotes. For example, the @Variable syntax to return the BOUSER value is @Variable('BOUSER‘).

@Variable Property Descriptions

Variable nameDescription

BusinessObjects system variables

  • BOUSER – user login

  • DBUSER – database user name

  • DBPASS – database user password

Values for the BusinessObjects system variables. The returned data is then restricted based on that BusinessObjects user's login. Values for the BusinessObjects declared database user.

Report variables

  • DOCNAME – the name of the document

  • DPNAME – the name of the Data Provider

  • DPTYPE – the type of the Data Provider

  • UNVNAME – the name of the universe

  • UNVID – the ID of the universe used

These variables can be referenced in, for example, the Begin_SQL parameter that will be executed before the SELECT statement. These variables can be used for audit purposes concerning the use of the database (For example: To determine which report query or which universe is used most frequently).

Language variables

  • PREFERRED_VIEWING_ LOCALE

  • DOMINANT_PREFERRED_ VIEWING_LOCALE

Language variables

  • PREFERRED_VIEWING_LOCALE - User’s Preferred Viewing Locale. This locale is the same locale chosen by the user to display universe metadata

  • DOMINANT_PREFERRED_ VIEWING_LOCALE – The Dominant Locale of the user’s Preferred Viewing Locale. This variable prevents the users from having to translate data in all locales (fr_FR, fr_BE, fr_CA, …). For example, in a situation where translations are available in fr_FR, if the user locale is fr_BE or fr_CA, since those locales share the same dominant locale (fr), they can reuse translations in fr_FR.

Operating system variables

You can enter Windows environment variables to obtain information about your installation.

The @Variable Function with SAP BusinessObjects System Variables

Use this function to restrict data based on the identity of the BusinessObjects user currently logged on.

Note

The BusinessObjects login parameters must be the same as the database login parameters.

The User Name assigned to each BusinessObjects user is held as the following BusinessObjects system variable:

  • BOUSER – The username

This variable appears in the User Identification box when the user performs a logon to a Business Objects product.

@Variable Example:

In the Business Layer for a human resources database, you want to restrict the data to the values authorized in the database for each user. This restriction would allow you to control what employee information each user is allowed to see. This information is defined by their database profile.

You use the @Variable function as follows:

Employees.Employee Name = @Variable('BOUSER')

When the users create queries, the data is returned only for the value in the database tables that matches the BOUSER value.

The @Variable Function with Locale Variables

Use the locale variables of the @Variable function to define the locale settings so the end user reporting tool retrieves reports and displays information in the appropriate locale. Your database tables must contain a column declaring the languages for rows that contain translations of the data. A locale defines a language and a geographical area, the way data is sorted, and how dates are formatted and other particular formats.

There is a list of Local Codes and Dominant Locale Codes in the translation management tool Guide. The settings you can define are:

  • @Variable('PREFERRED_VIEWING_LOCALE')
  • @Variable('DOMINANT_PREFERRED_VIEWING_LOCALE')

Product Table Example

Assume the database has a PRODUCT table with translations of the product names in many languages. The universe designer needs to return the product names in a specific language, based on the end user’s locale variable.

Translated Product Table

Product IDLOCALEProduct_Name
DC1212en_GBDigital camera
DC1212fr_FRApparell photo numèrique
DC1212de_DEDigitalkamera
DC1212es_ESCámera digitales

An object that dynamically displays the product name in the appropriate language would then have the SELECT statement SELECT PRODUCT.Product_Name FROM PRODUCT WHERE PRODUCT.LOCALE = @Variable('PREFERRED_VIEWING_LOCALE')

At query time, the Product Name for the user’s specific language Locale is displayed.

The @Variable Function with Report Variables

You use the @Variable function to include report variables in a SQL statement. These variables can be referenced in the Begin_SQL parameter that will be executed before the SELECT statement. This report variable can be used for audit purposes concerning the use of the database, for example, to determine which report query or which universe is used most frequently.

The variables can be referenced in:

  • The definition of an object: SELECT and WHERE clauses

  • Filters

  • The Join expression

  • The Begin_SQL parameter

Log in to track your progress & complete quizzes