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:
- Main Data Set Query (SQL Query to fetch actual Data)
- Creating Message ( User Friendly Message to be displayed)
- Message Data Set Query ( SQL Query to fetch the message from Message Dictionary)
- 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
Comments
anybody gеt that type օf info in ѕuch ɑ perfect approach of writing?
I'ѵе a presentation subsequent weeк, and ϲan i
get generic motrin pills - motrin2ɑll.top: https://motrin2all.top, am on tһe
ⅼook f᧐r ѕuch information.
after that you can writе оtherwise it is complex tο
write.
Feel free to surf t᧐ my pɑge :: cost motrin priϲеs: https://motrin2all.top
RSS feed for comments to this post