Adding Parameters and List of Values to BI and OTBI Reports

Parameters are some conditions you can either hardcode or pass dynamically while running the report.

Example: You want to run the report for Country - India and Balance Calculation Date, you can do this by adding 2 parameters to the report. Let us say we want certain countries to only to appear in the dropdown for country, you can do so by adding a sql query type List of Values. The list of values can then be attached inside a parameter, in this case for parameter Country

Go to Navigator > Reports and Analysis > Catalog Icon > Create Data Model

On the left hand side, you will see List of Values and Parameters options

List of Values

Name	Type	Data Source
Country_Value	SQL Query	ApplicationDB_HCM

Since the type is SQL Query we will write a simple query for querying the countries

SELECT DISTINCT TERRITORY_SHORT_NAME

FROM FND_TERRITORIES_TL

WHERE LANGUAGE = 'US'

Parameters

Passing a default value is optional but helpful. Let us say, you do not need to change the parameters every time you run the report then having default values are helpful. Example: I mostly run the report for India employee and as of date.

Name	Data Type	Default Value	Parameter Type
COUNTRY_CODE	String		Menu
BAL_DATE	Date	{$SYSDATE()$}	Date

     1. COUNTRY_CODE

       Display Label: Country Code

       List of Values: Country_Value

       Number of Values to Display in List: 100

     2. BAL_DATE

      Display Label: Balance Calculation Date

      Date String Format: MM-dd-yyyy (must be Java date format, e.g., MM-dd-yyyy)

Now you will see that you will have the list of country names and date parameter when you run the report.