Skip to Content

BI4.1 Business Layer Enhancements – Create Display Format

Hello All,

In the this document, am explaining how to create custom display formats for business layer objects using create display format option, within information design tool. You can create, edit, or delete the display format for multiple business objects at once.


Go to Universe Designer & open the universe –> click the Business layer –> (1)Select the object –>Click on the Advanced tab –> Click on the Show Values button, Its displays the Planned Prod Date default display format (M/D/YYYY HH:MM:SS AM).

      Display Format1.png

But user would like to see the date format as “DD/MM/YYYY”.  To concert date format to from “M/D/YYYY HH:MM:SS AM” to “DD/MM/YYYY”,

Method 1: Use the predefined function TO_CHAR

Add the To_Char (object name, required format) [To_Char (PLANNED_PROD_DATE,’DD/MM/YYYY’]) in the SQL Definition Tab –> Save and select show values button for observation of required display format values appears.

    Display Format2.png

Method 2: Use the custom display format option.

Select the object –> Click on Advance tab –> Click on Create Display Format button –> Select the Date-Time –> click on custom Format –> Select Day/Month/Year Formats according to the requirements –> OK, OK –> Click on the Show Values button for Observation of the correct data           appears, Save your universe and export it

    Display Format3.png

       Same process follow for numeric values custom format. I hope this will be useful for create custom date and numeric required format in IDT.

You must be Logged on to comment or reply to a post.
  • Hi,

    To customize this into a date format, would the data type of the field on the database should be a date for sure? I am getting this date as a string and would want to customize it as a date. I changed the dimension to date, but am not able to create a display format. Whenever I create the display format, I don’t get any results in the query.

    Kindly help.

    Thank you!

    • Hi

      Data type of the field on the database should be a string also it’s supports.

      Select the Date Type as : String , and then you can  define required date format at custom format editor. To check click on the Show values

        • Click on Create Display Format button – then will get Format editor pop-up – then click on the custom Format –

          Select the Day, Month,Year  from Token Categories, and check the preview bottom of the Format definition. .

          Then give some test under Undefined  then press OK, OK

  • Hi,

    Method 1 worked fine. Method 2 did not; it gave me the formatted date but added #FORMAT to the end. very annoying. I would have preferred using the custom formatting and would like to know why I get the #FORMAT but at least I can correct the issue.

    Thanks for the tip though.