BI4.1 Business Layer Enhancements – Create Display Format
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).
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.
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
Same process follow for numeric values custom format. I hope this will be useful for create custom date and numeric required format in IDT.
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.
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
Apaprently, as soon as I create the display format no values show up. Please find attached. am I doing something wrong?
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
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.