Discussions

Insight (OBIEE) Tips | Using Insight to filter on form submission value

Otilia Antipa-Oracle
Otilia Antipa-Oracle Product ManagerPosts: 106 Employee
edited May 6, 2022 2:17PM in Eloqua

Are you just dying to know how to filter on a form submission value? Look no further than this post!

Doing this type of filtering is a 3 step process.

Step 1: Gather the information you need
To begin with, identify a few things:
  • The name of the form
  • The form field you want to filter on
  • The form field value that you want to filter
Here is an example of what I'm talking about.



Step 2: Create a report that filters for the form data you want
  1.  In Insight, open the Form Submission Data report for editing.
  2. Create filters for the Form Name, Field Name, and Form Field Value that you gathered in Step 1.
  3. Save the report using a new name and location in the catalog.


Step 3: Create your final form submission data report
  1. Open the original Form Submission Data report again for editing.
  2. Create a new filter on the User Form Submit ID.
  3. Change the filter operator to "Is based on results of another analysis" and then choose the analysis you saved earlier.
  4.  Set the remaining values to: Relationship: Is equal to any, Use values in Column: User Form Submit ID
  5. Now save this -using a new name and location in the catalog. This is your final report!
  6. Preview the results, and you're done.


Final thoughts
  • Use the existing Form Submission Data report for these reports. The pivot tables are set up just right, and it saves you recreating a new report.
  • In Step 3, be sure to use the User Form Submit ID as the column to match.
  • As always, you should consider also using a date filter if you are going to deliver this report via an agent.



Post edited by JodyMooney-Oracle on

Comments

  • Hi @Alexa Kalapaca-Oracle, thanks for the tip! I have a question - if I have a form where I have fields that are not moved into any Contact or CDO fields, but I still want to be able to report out and filter on them, is there a solution to this? I saw that your solution uses a Form Field as a filter, but the displayed fields come from the Contact table. Thank you!

  • Hi @Alexa Kalapaca-Oracle, thanks for the tip! I have a question - if I have a form where I have fields that are not moved into any Contact or CDO fields, but I still want to be able to report out and filter on them, is there a solution to this? I saw that your solution uses a Form Field as a filter, but the displayed fields come from the Contact table. Thank you!

    Yup for sure. The Field Name attribute and the Form Field Value metric allow you to report on a form's fields and data. Have a look at the Form Submission Data report for an example.

  • Brad Dunzer
    Brad Dunzer Posts: 32 Red Ribbon
    edited Jun 14, 2018 6:31PM

    Yup for sure. The Field Name attribute and the Form Field Value metric allow you to report on a form's fields and data. Have a look at the Form Submission Data report for an example.

    Alexa this is great. But just like James I would like to apply this to form data that has not moved beyond the form. When I try and apply your method above the returned results are only the field that I have used to filter the data.

    For example without your filter method I just have a filter for form name and the returned metrics are all of the fields in the form. Lets say 20 fields listed in columns

    Now I add the filter of Form Field Value is equal to "Blue" - I then add the filter Field Name is Equal to "Color"

    When I then go and view the results I only get the Submit Date in one column and the next column is Color. and the rows are the instances of the word "Blue"...but the rest of the fields are not showing as additional columns

    Post edited by Unknown User on
  • Alexa this is great. But just like James I would like to apply this to form data that has not moved beyond the form. When I try and apply your method above the returned results are only the field that I have used to filter the data.

    For example without your filter method I just have a filter for form name and the returned metrics are all of the fields in the form. Lets say 20 fields listed in columns

    Now I add the filter of Form Field Value is equal to "Blue" - I then add the filter Field Name is Equal to "Color"

    When I then go and view the results I only get the Submit Date in one column and the next column is Color. and the rows are the instances of the word "Blue"...but the rest of the fields are not showing as additional columns

    Hi! Yes, because the form field data is actually a row (you’ll notice that if you export it to CSV especially), when you filter on the value, it will only return rows with those values. Not sure if that made sense… but you can achieve the same result by using another analysis as your filter.

    So, create an analysis for the Form Submission Data subject area. Filter this analysis for the field/value.

    Then create the 2nd Form Submission Data analysis. Use the results of the first analysis as the filter. Use the Form Submit ID as the filter field. This will return all the form submission data that meet the results of your other analysis.

    Check out this tip:

    Hope that helps!

  • Brad Dunzer
    Brad Dunzer Posts: 32 Red Ribbon

    Hi! Yes, because the form field data is actually a row (you’ll notice that if you export it to CSV especially), when you filter on the value, it will only return rows with those values. Not sure if that made sense… but you can achieve the same result by using another analysis as your filter.

    So, create an analysis for the Form Submission Data subject area. Filter this analysis for the field/value.

    Then create the 2nd Form Submission Data analysis. Use the results of the first analysis as the filter. Use the Form Submit ID as the filter field. This will return all the form submission data that meet the results of your other analysis.

    Check out this tip:

    Hope that helps!

    @Alexa Kalapaca-Oracle

    Never have used an analysis to filter another analysis...this should be fun to try...I will let you know !!!

    Thank you for the help

  • Brad Dunzer
    Brad Dunzer Posts: 32 Red Ribbon

    @Alexa Kalapaca-Oracle

    Never have used an analysis to filter another analysis...this should be fun to try...I will let you know !!!

    Thank you for the help

    @Alexa Kalapaca-Oracle

    I want to make sure I was clear. The forms I am trying to filter upon have fields that are not part of the Contact or Account record. So I do not have field attributes that I can drag and drop onto the layout.

    Here is a sample of fields that return without any filters applied other than the form name itself.

    raw-data.png

    I can filter the above on column Bank Branch and it returns the single column of Bank and the value that I filtered upon -- example "123"...I save this as the Analysis that I will use to filter.

    filter.png

    I can then use this above report as a filter upon the same full report, but it only returns the single field again.

    Filtered.png

    It this a result of how I am using the filters on the NEW report where I want to have all of the columns returned? Remember these columns are not in the Subject Areas as they are just fields in the form and not part of the Contact or Account record.

    I have tried many variations of editing the filter being applied.

    filter-structure.png

  • @Alexa Kalapaca-Oracle

    I want to make sure I was clear. The forms I am trying to filter upon have fields that are not part of the Contact or Account record. So I do not have field attributes that I can drag and drop onto the layout.

    Here is a sample of fields that return without any filters applied other than the form name itself.

    raw-data.png

    I can filter the above on column Bank Branch and it returns the single column of Bank and the value that I filtered upon -- example "123"...I save this as the Analysis that I will use to filter.

    filter.png

    I can then use this above report as a filter upon the same full report, but it only returns the single field again.

    Filtered.png

    It this a result of how I am using the filters on the NEW report where I want to have all of the columns returned? Remember these columns are not in the Subject Areas as they are just fields in the form and not part of the Contact or Account record.

    I have tried many variations of editing the filter being applied.

    filter-structure.png

    Hi again (you need to add your name to your profile! )

    In your second analysis, be sure to use the User Form Submit ID as the filter field:

    sample.png

  • Brad Dunzer
    Brad Dunzer Posts: 32 Red Ribbon

    Hi again (you need to add your name to your profile! )

    In your second analysis, be sure to use the User Form Submit ID as the filter field:

    sample.png

    @Alexa Kalapaca-Oracle

    Didnt notice my profile was just a number how rude of me - Brad Dunzer is the name

    I will give your suggestion a try

  • Brad Dunzer
    Brad Dunzer Posts: 32 Red Ribbon

    @Alexa Kalapaca-Oracle

    Didnt notice my profile was just a number how rude of me - Brad Dunzer is the name

    I will give your suggestion a try

    @Alexa Kalapaca-Oracle

    That worked perfectly !!!

    So the thought is create a filter that ultimately gives you the form submission IDs...as those will be unique. Then ask the new full report to only show the data for the Submit IDs.

    Fantastic!!!! This will help tremendously with our Shared Forms

  • Brad Dunzer
    Brad Dunzer Posts: 32 Red Ribbon
    edited Jan 15, 2019 7:02PM

    @Alexa Kalapaca-Oracle

    That worked perfectly !!!

    So the thought is create a filter that ultimately gives you the form submission IDs...as those will be unique. Then ask the new full report to only show the data for the Submit IDs.

    Fantastic!!!! This will help tremendously with our Shared Forms

    @Alexa Kalapaca-Oracle

    I have another requirement for form data. I want to Count the number of times values appear in a column.

    1. The form returns 20 columns
    2. One of the columns has campaign IDs in it
    3. I want to create totals for the number of times those values appear.
    4. The value will be ever growing as this is a shared form.

    For instance field elqCampID will be filled with id's like 24590 and 23605. I want to know how often each value appears in the column.

    Much like a Pivot table in Excel would group like values i eventually want to have a total for each.