Dynamically reporting based on date or time with presentation variable -- Part 1

In the past, I have come across requirements that need to build 1 report where user can dynamically choose the time period as monthly or yearly based on user input from the dashboard. I have documented the process of building that report in the previous post. However, there was an issue with that approach. It is that this will only work when the 2 columns have the same data type. Now what if users want to see a graph where on the Y Axis would should a range of dates when it is "daily report", and then want to see a range of hours when it is "hourly" report? 

The important thing to have in the report in order to make it valuable to the user is to be able to control the number of plots on the graph if user select 30 days on the graph. In this case, obviously if the hours are also displayed, then the graph will be unreadable. Yet at the same time, they also want to be able to see the measures at hourly basis for a specific day.

Now using column selector on the compound view is one workaround, but it is not the desired solution because we want to be able to use 1 global dashboard prompt to control all of the graphs to be either daily or hourly.  So understanding the required features, we will have to make sure the query does several things to be right. 

First of all, What's being displayed on the Y Axis of the graph should be either dates or hours depending on what users choose from the prompt. That means both columns should be included on the Y Axis however, the content of the two columns should be null when user doesn't select the corresponding time scale. 

Secondly, the measures on the graph should be summed at daily level when it is daily graph, and should be at hourly level when it is hourly graph. 

Thirdly, the hourly prompt should not be used if user wants to query daily reports.

Having realized all these challenges, we have to come up with a way to implement this chart. Although previously I have done something similar by passing presentation variable value into the report and dynamically changing the content of the field with a case statement, this is trickier. According to that method, I would create a dummy column in the report and enter something like "case when presentation variable = 'Day' then day.date when presentation variable = 'hour' then day,hour end". It can not be done here. Because date and hour are completely different data type and can not be used in 1 column. Therefore, something different has to be done.

Another challenge here is that, since the prompt on the dashboard is for selecting range of dates or hours, or is using "is between" operator, how would I pass these values into the report using presentation variable will become an issue. I can no longer prompt the report filter to achieve the same since it is going to be driven by variables. 

After some struggling, I have come up with a solution and it is working great. Although I have only tested it in 10g environment, I believe the same thing applies in 11g.  I will post my solution on the next entry

Until next time