Business Objects: Universe Parameters

Overview:

This is one of universe designer SQL generation parameters. The main use of this parameter is to enable/ disable ANSI92. This means that the SQL generated code from the report query will be in ANSI92 format if this parameter is enabled (Yes) other wise it will follow the old SQL ANSI89 standards. before we start we need to know what is the difference between ANSI92 & ANSI89

ANSI92 Vs. ANSI89

Both of them are SQL standers. Most people are familiar with the old ANSI89 one because it require less type effort. While ANSI92 introduced many new data types and a lot of enhancement on the syntax especially on joining table part. ANSI92 code should be more readable and less chance to encounter errors. One of the most attractive changes is to isolate table joining part from where clause.

ANSI89 sample SQL statement:

SELECT DISTINCT
BIDM.RB_DM_CLM_TRANSACTIONS.AS_OF_MONTH,
TRANS_COA_HIER.LEAF_DESCRIPTION
FROM
BIDM.RB_DM_CLM_TRANSACTIONS,
OFDM.OFS_RPT_COA_HIER TRANS_COA_HIER
WHERE
( BIDM.RB_DM_CLM_TRANSACTIONS.COMMON_COA_ID=TRANS_COA_HIER.COMMON_COA_ID(+) )

ANSI92 Sample SQL Statement:

SELECT DISTINCT
BIDM.RB_DM_CLM_TRANSACTIONS.AS_OF_MONTH,
TRANS_COA_HIER.LEAF_DESCRIPTION
FROM
OFDM.OFS_RPT_COA_HIER TRANS_COA_HIER RIGHT OUTER JOIN BIDM.RB_DM_CLM_TRANSACTIONS ON (BIDM.RB_DM_CLM_TRANSACTIONS.COMMON_COA_ID=TRANS_COA_HIER.COMMON_COA_ID)

Note(s):

As you can see in old ANSI format we have used the (=) to represent Join and (+) to represent outer join.
In ANSI92 we used (RIUGHT OUTER JOIN) and (ON) operator to represent table join. as you can see it is more readable and clean but on the other hand you need to type more code.

Parameter definition:

As per SAP user guide, you will find the following definition:

Parameter Name:	ANSI92
Parameter Possible Values:	Yes/ No
Default Value:	No
Description:	Specifies whether the SQL generated complies to the ANSI92 standard.
Parameter Value = Yes:	Enables the SQL generation compliant to ANSI92 standard.
Parameter Value = No:	SQL generation behaves according to the PRM parameter OUTER_JOIN_GENERATION

How to set this parameter:

Open Universe parameters by click on the corresponding icon or from file menu.
Navigate to Parameter tab (Last tab)
You will find a screen as shown below. You can select the required parameter from the upper panel and the parameter name and current value will be displayed on the bottom.
You have 3 buttons:
1. Add: if you want to add new parameter.
2. Replace: Edit the current value for the selected parameter with the new entered one.
3. Remove: Delete that parameter