System Variable functions / @Variable function in IDT

System variable functions are existing features which are not displayed in Universe designer and added as a separate section in IDT. System variable functions can be assigned to @variable to retrieve the respective values.

It is essential note that @Variable is a universe function along with @Prompt, @Select, @Where, @aggregate_aware to be used in the Universe Design Tool (UDT) & Information Design Tool (IDT). Cannot be used in Web Intelligence report level function and it can be used, if you are using custom level SQL in the query panel.

System variable functions must be placed within single quotes in @variable function and also @variable allowed to use non system variables which are considered as simple @prompt text.

If the <variable name> specified in the @Variable function is unknown to the system, the user is prompted for a value. In this case, the @Variable function behaves in the same way as a single-value @Prompt function with the following settings:

@Prompt(‘<Variable name>,’A’,,Mono,free)

@variable syntax:

@variable(‘Varaible_name’)

Below are the available system variables for to use in @variable function.

BOUSER: Returns the User name used to login into the SAP BI platform (Infoview / BI Launch Pad). Example @Varibale(‘BOUSER’)

DBUSER: Returns the User name used for authorization when connecting to the data sources. The user Name can be defined in CMC to see in @variable function. Example @Varibale(‘DBUSER’)

DOCID: Returns the document (report)ID only if the document is published in the repository and if the document is not published in the repository, the DOCID value is EMPTY. Example @Varibale(‘DOCID’)

DOCNAME: Returns the document (Report) Name regardless of the document of is published or not. Example @Varibale(‘DOCNAME’)

DPNAME: Returns the data provider Name. Example @Varibale(‘DPNAME’)

DPTYPE: Returns the data provider type and it will have different values for standard relation connection universe, Stored procedure based universe and OLAP universes.. Example @Varibale(‘DPTYPE’)

UNVNAME: Returns the universe Name. Example @Varibale(‘UNVNAME’)

UNVID: Returns the universe ID. Example @Varibale(‘UNVID’)

PREFERRED_VIEWING_LOCALE: Returns the information containing about the user’s current language setting and the user’s preferred locale for viewing report and query objects in an application. Example @Varibale(‘PREFERRED_VIEWING_LOCALE’)

DOMINANT_PREFERRED_VIEWING_LOCALE: Returns the information containing about the user’s current language setting the user’s preferred locale for viewing report and query objects in an application. Example @Varibale(‘DOMINANT_PREFERRED_VIEWING_LOCALE’)

To know more about list of available preferred viewing local, please click here.

Preferred Viewing Locale

Below is the sample report created by adding all the available system variable functions.

Note: System variables are not case sensitive.

The @Variable function can be used in the following expressions:
• Table Joins
• Calculated columns in the tables
• Derived tables created in universe
• Object definitions in the business layer
• BEGIN_SQL and END_SQL statements in universe Parameter
• Connection properties, such as the ConnectInit property (except the DELIMITER parameter, which is not supported in connection properties)

In database-specific SQL (multisource-enabled data foundations), all referenced tables or columns must be from the same connection.

Please feel free to post your comments.

Advertisements

One thought on “System Variable functions / @Variable function in IDT

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.