OBIEE Project Case Study 1 ---- Dealing with complex filtering requirement Part 2

So from one of my previous post where I discussed about a requirement I had for implementing a pretty complex filtering logic, I had realized that the logic I implemented there wasn't working 100%. I had discovered a few issues with that and therefore I am here to fix these issues.

Look at the following report as it is. After applying the filters, there are some issues.





Isssue 1: Some records shouldn't be there when the hour range is selected between accordingly on that date.In my case, it's between hour 0 to 2.

Issue 2: Those records that history is Y, they should only show up when the right hour range is selected. In this case, 3:05am should be considered after 3am. So if the end date is 3, then these records shouldn't show up.








Now the diagnosis is that, the filter expression for the hour fields should change.

The idea is that, when the date value passed by the presentation variable from the prompt matches the date value of start date, then we should display the start date hour as it is. That way, we are making sure that on the particular date that the users select, the start hour should be as it is. It will be filtered based on the hour that users select. If it's out of the range, then it should be filtered out. Then we need another condition, that if the end date is null, meaning that the record is still active, then these start hours should be the same as the presentation variables value, making sure that these records should always come through regardless what hourly range users select because they haven't been made history yet. So with this idea, I had to change the filter expression for the start hour to the following expression:

Case when start date = date '@presentationvariable' then hour(start date) else case when end date is null, then @hour else 1000 end end

For the end date expression, it is slightly more complex.

When the start date = the value from the prompt, then we want end date to be as it is except we convert 0 to 24 to satisfied the requirement in the previous post, then when end hour is null then end hour should be the same as whatever value passed from end hour prompt, making sure that it returns from the query regardless what actual values being selected. Last but not least, convert 0 to 24 for end hours.

Having implemented that, we can now retest the reports with the same filtering conditions and see the difference.

Thank you, 

Until next time.


PS: Later on, there was some performance issues discovered and I was forced to work more on this solution. I came up with my final solution to the problem and it was working great.

Please check the next post for the final solution