In this blog I will show you how we can add parameters in data model.
Pre-requisities:
Before you start you should have
- Access to Oracle Cloud Environment
- BI Developer Role
Please read the previous blog on the basics of developing a BI Report in Oracle Cloud. You can read it here.
Add a Parameter
The first thing that we need to do is to navigate to the Data Model Editor. Select the data set and then click on the edit option (Pencil icon). Check the below image for more details.
Once you click on edit, the Edit Data Set window will be shown where you can edit the SQL. In our example, lets add a condition to display the GL Periods based on a given year. We will add the below condition in the SQL:
WHERE period_year = :p_period_year
:p_period_year is the bind variable.
Click OK and you will be prompted with the below dialogue box.
Select the bind variable so that the system creates a corresponding variable. Click OK. The system creates a variable named p_period_year and navigates to the Parameters page. Lets change the display label at the bottom of the parameters page to “Period Year”.
In the Parameters page, you will be able to:
- Select Data Type
- Set Default Value
- Select Parameter Type
- Select the row placement
- Display Label
- Set the Text Field Size
Save your changes and then click on Data Sets and then on the Data Tab. Now you should be able to see the Parameter that was defined. Enter a value of 2016 and click on “View”. The output shows periods belonging to 2016 period year.
Click on “Save as Sample Data”. We will work on the creating a list of values in the next blog.
You can add multiple parameters to your query. Once you add the bind variables, the system will prompt you to automatically create corresponding parameters.
If you have questions please feel free to ask in the comments section or you can connect with me on twitter.