Date Prompt - enter Date or Date with timestamp

building semantic layer
1

Hi,

We have a Date prompt in universe in 3.1 and it is defined as

@Select(AB\Date1) = @Prompt('DATE','D',,mono,free)

But this doesn’t work in BI4.0, so we changed it to -

@Select(AB\Date1) =  (to_date(@Prompt('DATE','A',,mono,free),'MM/DD/YYYY' ))

Now this is working fine in 3.1, it’s just user manually need to enter the date in specific format.

Now they need to have a facility wherein they can either insert the date in above format or also in below format -
MM/DD/YYYY HH:MM:SS AM
So is there any solution for this required in 3.1? And we are in process of migrating to 4.0, so later this should work in 4.0 same as in 3.1.

Please advice.

aniketp :uk: (BOB member since 2007-10-05)

2

I am really very very sorry for post bumping… I found that this is doable on webi. For an e.g.

=FormatDate(ToDate(Trim(If(Pos(UserResponse("DATE1");" ") = 0;
Substr(UserResponse("DATE1");1;10);Substr(UserResponse("DATE1");1;
Pos(UserResponse("DATE1");" ") )));"MM/dd/yyyy");"dd Mmm yyyy")

Now user passes any value say - 01/02/2012 or 01/02/2013 01:02:02 AM, the above code will display only 01/02/2012 at the report level, but it won’t throw any error.

On the same grounds I was trying to create a conditinal object but it is failing. As the field is Date and above code has Pos.

Instr - A universe function can be used in replacement of Pos function.

Please help me…

aniketp :uk: (BOB member since 2007-10-05)

3

It will work, you just have to apply the right service pack, as I have told you before :).

Mak 1 :uk: (BOB member since 2005-01-06)

4

Created a conditional object in 3.1 and it is working fine -

@Select(AB\Date1) = to_date(DECODE(instr(@Prompt('DATE','A',,mono,free),' '),0,@Prompt('DATE','A',,mono,free),substr(@Prompt('DATE','A',,mono,free),1,10)),'MM/DD/YYYY')

Not sure, if this will work on 4.0 or not…

aniketp :uk: (BOB member since 2007-10-05)

5

Well, I know there is a bug with your current version where it cannot accept prompts of the date data type I believe, so it should.

However, as I suggested to you before you should apply the relevant service pack to fix the actual issue.

Mak 1 :uk: (BOB member since 2005-01-06)

6

Thanks Mak for your guidance and help.

I checked the below link but seems the bug is not fixed. BI 4.0 doesn’t support Date prompt. http://wiki.scn.sap.com/wiki/display/BOBJ/WebI+BI+4+Fixed+Issues

Can someone please guide me if this is ever fixed or not?

aniketp :uk: (BOB member since 2007-10-05)

7

Unsure, when we discussed this before, it looked like it may be addressed in SP5, although the issue was not exactly the same as yours.

Mak 1 :uk: (BOB member since 2005-01-06)

8

Hey Mak, thanks for your response. As of now the only issue which I am struggling with is - Date prompts not working in BI4.0.

As per the thread - Date Prompt in BO4

I checked in - http://scn.sap.com/thread/3260551

But it seems the issue is not fixed. I just thought to confirm if it is fixed or not. Can someone please guide me if this is fixed or not, as I am really facing a hard time in migration from 3.1 to 4.0 and need to change many universes because of this limitation.

aniketp :uk: (BOB member since 2007-10-05)

9

Can’t help, sorry. Suggest you check with SAP.

Mak 1 :uk: (BOB member since 2005-01-06)

10

Hey Mak,

Different team had a word with SAP support team and it seems this is still an issue and it is not resolved.

aniketp :uk: (BOB member since 2007-10-05)

11

WOW! You would have thought they would have fixed this one, thanks for the update.

Mak 1 :uk: (BOB member since 2005-01-06)