Insight (OBIEE) Tips | Removing the timestamp from a date
Have you wondered how to get rid of a timestamp in a date column? Here’s a method..
Here is a simple email report that shows the total sends on a given date.
Subject area: Email Analysis by Send Date
The problem is that i don’t want to see the total sends by day and time… I want to a total by day.
Let’s fix it!
Email Send Date vs Calendar Day attributes
First off, instead of Email Send Date, use the Email Send Calendar attribute called Day. Why? This attribute rolls up all emails sends by day. Whereas the Email Send Date is the date and time of the email send.
Here is the updated column selection:
When I look at the results though, it is still showing the time! Argh.
Remove the time
I know that some of you just love using SQL by now You can remove the time from the column using the CAST() function.
On the Criteria tab, click the Settings icon on the Email Send Calendar column. Then choose Edit formula.
Change the formula to:
CAST("Email Send Calendar"."Day" AS date)
Et voila! The time is removed!
Make the column heading reader friendly
When I added that CAST() function, it changed my column header. As a final cleanup, I’ll update the column heading.
On the Results tab, edit the table view.
In the Layout section, click the settings icon and choose the Format Headings option.
Change the caption to be the desired heading.
After applying the changes, here is the final report.
Remove the drill down interaction
Did you also notice that when I applied the CAST() function, the send date became a link?
This drill down link interaction doesn’t make sense in this report since the Day attribute is rolled up to midnight. So, let’s remove it.
Back on the Results tab, edit the table view. Then in the Layout section, click Settings > Column Properties.
On the Interaction tab, change the interactions to None.
And here are the final results! SHIP IT!
- The date format (m/d/yyyy) comes from Eloqua’s Insight setup. To change the date format, you can apply your CAST() function and then update the column properties to use your desired format.
- However, if you do not want to use CAST() and simply try to override the Data Format using a custom date option, this is not supported by Insight. You will get an error when try to save the analysis.