You are here: BI Publisher BI Publisher Data Model : Adding Parameters and List of Values

BI Publisher Data Model : Adding Parameters and List of Values

Rate this item
(6 votes)

This artilce is continuation of previous article Let's Structure the Data

Adding parameters to data model allows user to filter the report data.   BI Publisher supports following types of parameters.

  • Text: Allows user to enter text string.
  • Menu: User can select the values from a List of Values (LOV). The LOV can be bases on fixed values or based on SQL query that fetch the data against pre-defined data source.
  • Date : User can pick the date as parameter from Date Picker (Calender).

Select the Parameters node from the Data Model Navigator and click the + icon to create new parameter. 

Adding new Text Parameter.

Add a parameter P_ORDER_ID to filter the Report based on Order ID. Select the Parameter type as Text. Set the Display Label, this will be display on Report Viewer and Scheduler Parameter UI.
 

Test the new added parameter through Data Model viewer by clicking get XML icon.

Adding new Date Parameter

Add another parameter P_ORDER_DATE to filter Report Data based on Order Date. Select the Parameter type and data type as date. Leave the default date format to set as per your requirement. Date From and Date To can be set to restrict the date between 2 specific dates.

 

Test the new date parameter through Data Model viewer

Adding Menu type Parameter


Parameter Menu type is based on List of Value.  Before creating the lets create the List of Value.

The LOV can be based on fixed data or based on SQL query.

To define a SQL query based LOV, select the List of Values node from Data Model Navigator and click the + icon from right side pane.

Set the name, select the type as SQL Query and select the data source where this query will execute.  Either use the query builder to define query or write the query directly to SQL Query pane. 

In this example, first column ‘name’ is label and the second column id passed as value to underlying parameter.

To define a fixed data based LOV, select the List of Values node from Data Model Navigator and click the + icon from right side pane.

Set the name, select the type as Fixed Data. The Data Source will be null in this case.  Add the new row by clicking + icon from the lower right fixed data section.

 

To add a parameter as menu type, select the type as ‘Menu’ from drop down. Set the Display label and select the right LOV.  Leave the other options value as default.

Test the new menu parameter through Data Model viewer.

 

Set the parameter list of value as Customer_FIX_LOV and test it

At this point, you can select the parameters values but these parameters do not have any impact on data as we are not using these parameters in our query. Modify the SQL query to filter the data based on these parameters.

Navigate to Orders data set in data model navigator and click the ‘Edit Data set’.

 

Update the SQL query to include the Customer Name column and  WHERE clause filter the data based on the parameters. Parameters P_ORDER_DATE, P_ORDER_ID and P_CUSTOMER set as bind variables in SQL query. 


 

Enter the Order ID and review the data and different parameter values and review the xml output.

 

 

The article help us to understand on how to define the Parameters , associate with List of values and use these paramters in SQL queries.  Please stay tune to know more about Data Model.

 

This article is part of BI Publisher for beginners series. You will see many more articles on Data Model design, Template desing, Administration, Web Services, Scheduler. The contents are picked-up from BI Publisher Training documents from  Adiva consulting. If you have any question regarding these articles or training program, please contact This e-mail address is being protected from spambots. You need JavaScript enabled to view it .

Read 34417 times

Leave a comment

Make sure you enter the (*) required information where indicated.
Basic HTML code is allowed.