How to customize the BW Key type date format in Web Intelligence

[Balaji]

hi experts,

I want customize the two different date formats from BEx to WEBI, I have two different date formats in BW end,

1.Dimension type

2.key type

In Webi, for dimension type objects I can able to customize easily using  =FormatDate([ BW Dimension Field Obj Name];"dd/MM/yyyy")

I am facing issue for Key type

In Webi, when I try to apply the same approach for Key value it showing an error

=FormatDate([BW Key Field Obj Name];"dd/MM/yyyy")

Error is

The expression or sub-expression at position 12 in the 'FormatDate' function uses an invalid data type. (IES 10037)

required to convert using any Todate function or any other option is in place

Need your inputs

Regards,

Balaji

[sateesh_kumar1 · Accepted solution]

Hi Balaji ,

It is always recommended to use INPUT_DATE /INPUT_DATE_TIME in date formatting .

If it is only to get format like you mentioned then use =replace([Date - key],".","/") or use

=FormatDate(ToDate([Date - Key];"dd.MM.yyyy");"dd/MM/yyyy")

[Balaji · Accepted solution]

hi Sateesh,

Thanks for your inputs,

for fist one - =replace([Date - key],".","/")

Error in the first , which is available after end of Key Text,

Error is displayed as - Unrecognized input "," at position xx (IES 10018)

this second option which you are mentioned worked for me

=FormatDate(ToDate([Date - Key];"dd.MM.yyyy");"dd/MM/yyyy")

Regards,

Balaji

[tanveer1]

Hi Balaji,

You have to convert your Key to a Date usint Todate() function

if your values are like 01.01.2015 then use below formula

= todate([Key];"dd.MM.yyyy") ..... this will return the value as 01/01/2015

after this you can customize as per your need using formatdate() function

Thanks,

Tanveer.

[Balaji]

hi Tanveer,

I tried in the below format,

=ToDate([ BW Date type Key Field Name];"dd/MM/yyyy"),  when I validate the formula there is no error , but output is showing as #ERROR

Regards,

Balaji

[tanveer1]

can you show me what are he values in the [ BW Date type Key Field Name]  is it like 01.01.2015 if so please use below formula or else let me know the format ??

as per my above post did you try

=  todate([Key];"dd.MM.yyyy")

[Balaji]

hi Tanveer,

when I use the BW Date Key field in Webi, the date is showing as 25.05.2015, but I have the requirement to show the date as 25/05/2015,

tried the option which you are given, but the output is #ERROR

actually the given format  is working fine for dimension date objects, but not working for key format date type

I think some alternate we need to try it.

regards,

Balaji

[former_member211235]

Hi,

You need to convert it to ToDate and then use FormatDate.

Hope it helps...!!

Grtz

-Anila.