Insight (OBIEE) Tips: SQL quick tip
For more Eloqua Insight Resources, please visit: https://community.oracle.com/topliners/discussion/4470627/eloqua-insight-resource-page
Recently, I was looking into a blind form submit challenge where anti-malware protections were causing a lot of fake form submits. We wanted to make sure that in form submission data reporting, we were only getting the valid data.
Based on the email recipients and the use case, we decided to only report on the last form submit from a given contact.
OBIEE SQL filter to achieve this:
"Form Data"."User Form Submit ID" = MAX( "Form Data"."User Form Submit ID" By "Contact"."Eloqua Contact ID")
Got any SQL tips for Insight users?
I am creating a consolidated Form Submissions Report, and there will definitely be instance where [email protected] submits more than one form.
With this SQL filter, i might only get one row of submission from [email protected] even though he has submitted more than one form. Can you advise on this, please.
Hi, are you looking to only get the latest per form?
If [email protected] completes 4 form submission (e.g. below)
My report should only be reporting 3 form submission - based on the latest form submit done per form
@rnordin In the Form Submission subject area, try
"Form"."Form Submission Date" = (MAX( "Form"."Form Submission Date" By "Form"."Form ID","Contact"."Eloqua Contact ID"))
@Alexa Kalapaca-Oracle unfortunately no, i am still seeing duplicate entries made by the same contact to the same form. I.e. the example i shared above.
what columns are you including in the results? If you're using Form Submission Date/Time, that is probably too granular for the filter suggestion I gave. Adjust the filter to use the same form submission date column you're using in your criteria pane.
And just a suggestion on your filters above - filtering on the Date/Time attribute is very granular - i.e down to the minute and could slow down your report. Adjust it to use a higher level like an attribute from the Form Submission Calendar (i.e Day) or even the Form Submission Date (rolls up to the hour)
@Alexa Kalapaca-Oracle , got it. My current report is based on Form Submission Date, i will try to roll it up to Form Submission Calendar (i.e Day)