Discussions
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!
Comments
-
Thanks @Kevin Twilio worked perfectly, been looking for something like this since OBIEE was released. Just to add on for some other, I wanted to see info from the prior week, i.e. more than 7 days back and less than 14 days back I used this and have been getting consistent results:
"Form"."Form Submission Date/Time" > TIMESTAMPADD(SQL_TSI_DAY,-14,CURRENT_DATE) AND
"Form"."Form Submission Date/Time" < TIMESTAMPADD(SQL_TSI_DAY,-7,CURRENT_DATE)
-
I use:
"Email Send Date Attribute"."Email Send Date" > (current_timestamp -14) -
This is all great and I'm so glad to have found this because I have a ton of reports that need dynamic dates.
But, this is not layman-friendly at all. A typical Insight Classic user would not have thought of this at all and would be stuck on one of the most basic of tasks. Is this what we are left with? I hope not as future Insight users are in for a rude awakening on how unnecessarily complex it has become to create and run a basic report.
-
Thank you very much for this as this is very helpful. Have you figured out how to set up a subscription using these SQL filters?
-
Thanks for this. I don't see the Email Send Date option to filter on within this report.
-
Thanks for this. I don't see the Email Send Date option to filter on within this report.
If you click on the "More Columns" link on the bottom of the filter, you'll find it in Attributes/Email Send Date Attribute.
-
Good morning,
I'm unable to get the SQL filter suggested above to work as of this morning (it has been working quite well). Anyone else having an issue?
Also, @JazmineDuke-Oracle, I don't see an option for adding dynamic date filters to reports in Insight and thought this was going to be included in 18B. Can you tell me where I should be looking?
Thanks!
-
Good morning,
I'm unable to get the SQL filter suggested above to work as of this morning (it has been working quite well). Anyone else having an issue?
Also, @JazmineDuke-Oracle, I don't see an option for adding dynamic date filters to reports in Insight and thought this was going to be included in 18B. Can you tell me where I should be looking?
Thanks!
Hi @Nikki Vaughn-Oracle, hope you got your SQL filter working again.
You will start to see dynamic dates added starting with our 18C release (August 2018). See also -
Thanks,
Alexa
-
Great post! Very helpful!!
I've been using the following to report on form submissions during the last 6 months and is working well for me:
"Form"."Form Submission Date" >=TIMESTAMPADD(SQL_TSI_DAY, -182, CURRENT_DATE)
-
Thank you very much for this as this is very helpful.
-
Thanks for this!
-
Great post! Really helpful!
Also,
If you want to pull the campaign reports of odd/even previous month then you can try below codes as well.
Campaign Start Date : 1st day of the month : TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
AND
Campaign Start Date : Last day (28th/29th/30th/31st) of the month :TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
Cheers,
