With Oracle Eloqua's Insight, there's a lot more you can do than meets the eye. Simply ask your Customer Administrator to add an Analyzer license to your user, and you can get started. In this video, I will show you how to take advantage of an advanced technique to create timespan-based reports that would not otherwise be possible. Normally, you can't automate reports like "Campaign Analysis Overview - Last Month", but this technique lets you get around that restriction.
The video references some SQL statements that you'll need to use when setting up these reports. Here are those statements:
Use these statements to set up reports for last month:
- First day of current month (use with "less than" filter):
- TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1 , CURRENT_DATE)
- First day of previous month (use with "greater than or equal" filter):
- TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
Use these statements to set up reports for last year:
- First day of current year (use with "less than" filter):
- TIMESTAMPADD(SQL_TSI_DAY, EXTRACT(DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
- First day last year (use with "greater than or equal" filter):
- TIMESTAMPADD(SQL_TSI_YEAR , -1, TIMESTAMPADD(SQL_TSI_DAY, EXTRACT(DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
About the Author
More Content by Andy Shaw
