Webi Date Format

[kelly_stone1]

Hello - I am on Business Platform 4.0 SP4 Patch2 and using Webi with a OLAP connection to a multiprovider on a BW system and run a BEx query.  I am retrieving a date field (OBILL_DATE) which defaults to dd.mm.yyyy.  I want it in the format mm/dd/yyyy.  When I right click on the column,  I do not get the option "Format Number" like I do on other number columns.  How can I change the format? I have tried both Webi Rich Client and Webi from the BI Launch pad.  When looking in BW (RSA1), using Analysis for Excel, or using CR for Enterprise it is fine.  Any help will be appreciated.

[vinay_lohakare5 · Accepted solution]

Hello Kelly,

You infoobject is a date so you wont get the formate number function.

You will have to use the Formate Date function to formate the 0bill_date in WEBi

Thanks,

Vinay

[kelly_stone1 · Accepted solution]

Hello Vinay - In the "format number", you have the ability to "format date"....  there is not a separate format date.  If so please supply specifics.

Kelly

[vinay_lohakare5 · Accepted solution]

Hello Kelly,

Whats the data type of 0bill_date?

If its a number: You will get the option of format number

If its a string or date : you wont get the option to format number

Solution:

If its a date:

Create a variable

VAR1 = FormatDate(0bill_date();"MM/dd/yyyy")

if its a string:

You will have to first convert it to date using function ToDate (conver string to date) and then use the function FormateDate to get the date.

You can use the new variable as the column.

OR

If you convert it to data type date and use it in the column you will get the option for formatedate too.

Thanks,

Vinay

[kelly_stone1 · Accepted solution]

Vinay - I am thinking it may be a Webi bug.  It is a date field coming out of BW, so the number format option should show up, but it does not.  I am having to bring over the date key part of the dimension or the dates do not show up at all...and I have to bring that over before any other key figures or I get an error.  There are no issues with Analysis for Excel or CR for Enterprise running same query.  Time to open a message I guess.

Thanks,

Kelly

[Former Member · Accepted solution]

Hi Kelly,

if I understand your problem correctly this could help...

Insert the regular dimension field [OBILL_DATE] into the table.

Create a new variable, e.g. "varDisplayBillDate" with qualification "Measure" with a formula looking something like:

=FormatDate(ToDate([OBILL_DATE];"dd.MM.yyyy");"MM/dd/yyyy")

Insert the new variable (e.g. varDisplayBillDate) into the table

right-click on the regular dimension field [OBILL_DATE] and hide dimension

Regards, Uli

[kelly_stone1 · Accepted solution]

Thanks Vinay and Uli - although I believe there is still an issue with Webi at this time, with your replies, I was able to get the date format via a variable.  Thanks.

[Former Member · Accepted solution]

Hi Kelly,

Did you hear anything from SAP regarding this. Date field from coming out of BW as mm/dd/yyyy is getting converted to yyyymmdd in webi. We have more than 100 reports that got affected by this issue after the upgrade.

Thanks

[kelly_stone1 · Accepted solution]

Jha - No... I have not heard...

Regards,

Kelly

[former_member198519 · Accepted solution]

Thanks Kelly

[Former Member · Accepted solution]

Hi Kelly,

I was wondering what the OSS message is you raised, so i can refer to it also. We have the same problem. I can not create a variable as you did, because im using webi (based on a bex query) for my dynamic recipients where the date is used as a parameter. So variables are not shown in publication.

Kind regards,

Raymond

[Former Member · Accepted solution]

Raymond - Unfortunately I can't find out how to get to past messages...

Kind regards,

Kelly