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