Oracle Analytics and AI

User_VMJII

I have manual query like:

select x, y, z, count(*) N

from table_a, table_b

where open_date <= '31.12.2020'

and nvl(close_date, '31.12.2020') >= '31.12.2020'

group by x, y, z

I need to upgrade it so that the user can choose any date in the visualization (instead of the specified '12/31/2020'). I have no idea how, please help me :)

[Deleted User]

Have you tried the very first recommendation already:

Remove the WHERE clause from the data set. Create a Workbook on top of the data set and let users choose the dates they want themselves.

DV is almost too feature-rich and self-service oriented in order to literally do what you want. For example any date-based DV native filter will automatically allow you both list and range selections:

Meaning you will need to switch to something that does less thing automagically and where you have more finegrained control over. Like using the "Dashboard Prompt" viz type:

As you can see I have changed the "Filter Type" property of the visualization so that people are no longer allowed to use the date range option.

That date can then be set and used in a parameter:

And that parameter you can then use in your filter expression: