How to add a dynamic date filter into the new Eloqua Insight (OBIEE)
Hello All, the new Eloqua Insight (OBIEE) is coming and Classic Insight will be deprecated next year (). Unfortunately, there are a few features that were obvious to do in Classic that aren't so obvious in the new Insight. One of them being how to create a dynamic date filter (i.e last 1 day, last 7 days, last month, etc). In the new Insight, the options look to be for static ranges only. This is a problem if you have scheduled email reports that you want to see only recent data. An example we have is to see all the emails sent in the last week and the metrics around them that we receive on a weekly basis. We have found a way to use dynamic filters using SQL. Please see how we did it below.
1. Find the report you want to add the filter to and hit 'Edit'
2. Click on the add filter icon
3. Select the date field you want to be dynamic. If the field isn't immediately there, click on the 'More Columns' option at the bottom
4. Set the operater to 'is greater than or equal to', select any date for the Value, and then check 'Convert this filter to SQL'
5. Remove everything after the '>=' symbols
6. In this scenario, we will be getting everything within the last 1 day. Replace what you deleted above with this code (not including quotes): "TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)". The "-1" represents the amount of days and you can change the number depending on how many days you want to look back.
7. Click 'OK' and you now have a dynamic date filter.
Please note that this is something that we found ourselves and may not be officially supported by Eloqua. Please do not contact support with questions on how to do this. Happy to to field any questions you may have on this through the thread. Thanks!