Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OAS Date in DV manual query - Parameter / Filter
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 :)
Best Answer
-
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:
0
Answers
-
Where is your "manual query"? Are you doing a "Direct Database Query" in Answers? Are you using the SQL as a source for your DV data set? What exact product in which version are you using?
0 -
Sorry for the bad answer, I am not quite sure.
I am using OAS DV, manual query in data, join diagram. About version - where can I find it?
0 -
Top right. Click on your user's icon. Then choose "About".
0 -
0 -
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.
0 -
But I don't have a "neutral" date in the table. I only have open date and close_date. It should create a "calendar" somehow, but I can't seem to join it with my data.
And the parameter function in workbook offer me only current date. If there was a function where I could choose any date, it would be the easiest.
0 -
You can either deduce that notion of a "Year" according to your SQL definition already - it'll be presented as just another column.
Or if you are worried about performance and storing your data isn't an issue then you can use data flows to store a data set in which you then create that notion of a year.
It's very straightforward to have the year as part of your query. That's by far the easiest way.
0 -
I'm sorry, but I don't understand your proposal (You can either deduce that notion of a "Year" according to your SQL definition already - it'll be presented as just another column.) very well.
0 -
If you have open date and close date in your DB, why don't you just deduce a "Year" column from it using SQL.
Apparently this is what you call a year:
where open_date <= '31.12.2020'
and nvl(close_date, '31.12.2020') >= '31.12.2020'So why not put that as a column definition with CASE around it and the output being 2020. Then you have a new attribute which represents your year
0 -
@User_VMJII did you give up on this thread or did you do for one of the proposed alternatives?
0