Custom Prompt at Universe Level (IDT or UDT)
Sometime we may get requirement from User to select Free text for column where we don’t have ‘LOV’s available.
Something like in attached screen shot
e.g: Running report for “Last Day” Or “Last Week” on specific Date column
We can’t hard code for single selection as report will run only for that selection and re-usability can’t be achieved.
Here I am going to explain how we can create custom filter condition at Universe (IDT or UDT) so that custom LOV’s can be used to run report with multiple options. This filter condition can be used in report so user will get option to select period for which report should run. Below are steps.
I have used Oracle Database for this example, but same can be replicate with little modification with any Database.
Step1 : Login to IDT/UDT
Step 2: Create Filter With Name “Period Selection” (or give any Name)
Step 3: Write below formula in Filter Definition.
In this formula you can add as many option you want based on requirement and modify formula. For this example i have used 4 options.
Step 4: Validate the formula and you are ready to use in reports. You can create same Filter for all Date columns and use in report based on requirement.
Once you refresh the report it will ask you for prompt where you can select any value from LOV’s and report will run accordingly.
You can make use this of feature to Schedule same report with different option and deliver to end user based on period.
E.g: You can use “Last Week” to send report on Every Monday with Last week Data.
You can use “Last Month” to send report on Every 1st of Month with Last Month Data. etc.
What you have to do is just select according option in Scheduling Parameter option and schedule report.