Discussions

Eloqua’s Tip Tuesday: Changing date field appearance in Eloqua Reporting

JodyMooney-Oracle
JodyMooney-Oracle Outbound Product Management, EloquaTorontoPosts: 421 Employee
edited Nov 16, 2022 2:42PM in Eloqua

Welcome to “Tip Tuesday”! Each week we post interesting tips and tricks for Eloqua users, on Tuesdays. To see more Tip Tuesday posts, head here.

This week’s tip is brought to us from long time Eloqua user, @Jacob Esson.

Did you know that you can change the way the date field is formatted in Eloqua Reporting?

Maybe you’d like to change the date format in your reports so that they look consistent with how dates are displayed in your region. Or you may want to alter the date format because you have import jobs running that require specific. For example, if you have created an FTP job via an agent to ingest this data and your warehouse requires a specific format, you can alter the date field appearance to ensure they match, and the job completes as expected.

Well, the good news is that Jacob has shared with us a variety of ways to modify the appearance of date fields in Eloqua’s reports!

Let’s walk through how:

Through Column Properties

Open the report you’d like to modify. Then from the settings wheel, select Column Properties.  


Then choose the Data Format tab. Select “Override Default Date Format”


Then select date format that is most applicable to your date requirements for example (dd MMMM yyyy h:mm:ss tt) so that it places that format into the Custom Date Format field (but it still appears greyed out)


AND then, select the last option in the drop down list “Custom” in the Date Format selector.


Once you do that, Custom Date Format field then becomes editable and you can remove what you don’t want included. For example, you can remove the timestamp by just leaving "dd MMMM yyyy" left in the Custom Date Format field.  


Through Column Formula

If you’re new to creating columns with a custom formula, be sure to start here.

For this example, Jacob has provided us with a sample formula where the timestamp is changed to mm-YYYY. Within the New Calculated Measure dialog, you can enter your new custom formula in the Column Formula section.


For the first validate if the month <10

  • CASE WHEN MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time") < 10

If that is the case we concatenate (using the ||) the value '0' with the Month Number as 1 digit

  • THEN '0'||CAST(MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS VARCHAR(1))

for the month numbers from October to December as two digits

  • ELSE CAST(MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS VARCHAR(2)) END

We then concatenate (using the double ||) and the value '-' (in between single quotation marks with the year

  • ||'-'||CAST(YEAR("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS CHAR)

Formula:

CASE WHEN MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time") < 10 THEN '0'||CAST(MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS VARCHAR(1)) ELSE CAST(MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS VARCHAR(2)) END||'-'||CAST(YEAR("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS CHAR)

If you would like to use this field on other subject areas, please replace the "Email Group Subscribe Attributes"."Email Group Subscribe Date/Time" in the formula.

 

Additional Resources:

Thanks for the great tip @Jacob Esson!

Have an Eloqua tip you would like to share for a future “Tip Tuesday” post? Drop me (@JodyMooney-Oracle) a message via Topliners with your tip including any relevant screenshots/links to help share your favorite tips with fellow Eloqua users? If your tip is selected, we’ll tag you in the post too!


Group Product Manager, CX - Marketing: Eloqua

Post edited by JodyMooney-Oracle on

Comments

  • Kyle_Murphy
    Kyle_Murphy Posts: 19 Red Ribbon

    Hi Jody -


    Thanks for this. To confirm, by adjusting the date field appearance, will it also roll-up results based on the new formats? For example, we have a weekly insight report that shows all emails that sent in the last 7 days; however, with the native date format, any automated campaigns/emails show up multiple times on the report based on the hour that was sent. If we change the date format to by MM/DD/YYYY (without consideration to hours/minutes) will it adjust the report so that all emails sent within the same date format value (day) be on a single line?

  • Otilia Antipa-Oracle
    Otilia Antipa-Oracle Principal Product Manager Posts: 107 Employee

    Hey @Kyle_Murphy

    The cast function will allow you to change the date type so you can surpress all emails with the same date to a single line.

    I believe you are using the Email Send Date, so I've put an example below to show you. The first column is Email Send Date with CAST applied (CAST("Email Send Date Attributes"."Email Send Date" AS DATE) ) and the second column is the column as is.



  • Kyle_Murphy
    Kyle_Murphy Posts: 19 Red Ribbon

    Thanks Otilia!