Introduction

Many a times we have a BI Report which has more than one input parameters and the data value is returned only when at least one of the parameter value is provided. In such cases an end user would expect to get prompted when they do not provide any input but as of today (release 13 20C application version of Oracle HCM Cloud Application) this feature is not delivered out of the box.

But no worries, we have an alternative solution and we would demonstrate the same here. Hopefully it would be useful.

So, without further ado let’s get started.

Configuration / Setup Steps

We would have to have a perform a series of steps to accomplish the same. The steps are:

1. Main Data Set Query (SQL Query to fetch actual Data)
2. Creating Message ( User Friendly Message to be displayed)
3. Message Data Set Query ( SQL Query to fetch the message from Message Dictionary)
4. Configure the BI Template 

Main Data Set Query

For this example, we will create a very simple sql query which will have two input parameters and it will fetch details from per_all_people_f table.

Creating Message

We will create a new custom message within Oracle HCM Cloud Application which  will be displayed when the parameter values are null. One may decide to use a static text too but one of the advantage of using a message is that one can dynamically change the message content from the application as per their requirement.

Navigation to create new custom message:

Login to Application -> Navigator -> Setup and Maintenance -> Search (Manage Messages)-> Create

Message Data Set Query

We will create a separate data set to fetch message text.

One point to note here is that this data set will only return data if both the parameter values are NULL.

Once the data sets are created we will create a data model and the data model diagram would appear as below:

Configure BI Template

We will now create a BI Template which will have three sections namely Layout Grid (Report Header which contains Report Name, Run Date and Parameter Values passed) , Repeating Section 1 ( Details of Actual Data) and Repeating Section 2 (Error Message Data)

The completed template would appear as below:

And with this report creation is complete.

Verifying Results

We would have to run three different scenarios to ascertain whether the report meets business requirement.

Scenario 1 : Pass Value for One Parameter (Person Number)

We will only pass a valid value for person number and the person id field should be left blank

Scenario 2 : Pass Value for Second Parameter (Person ID)

We will only pass a valid value for person id and the person number field should be left blank

Scenario 3: Do Not Pass Any Value for Parameters

In this scenario, we will not pass any value for any input parameter

Summary / Conclusion

So basis the above results we can conclude that we can configure some setups to ensure the end user gets a actionable message when NULL values are passed to BI input parameters.

The solution also provides an opportunity to change the message as per specific needs as the same can be changed within the application using “Manage Messages” task.

Hope, the workaround is useful.

Thanks all for your time and have a nice day ahead.

Downloadable Files:

AtLeastOneParameterValueReqd.xdo.catalog

AtLeastOneParameterValueReqd_dm.xdm.catalog

Ashish Harbhajanka

Add comment