Dynamic display of date columns based on prompt selection

building reports webintelligence
1

Hi
I have a requirement display dynamaic headers in the webi report based on the user selection.

I am using the below function where “caleder day” is the text ofthe prompt and “Actuals” is the text before the userresponse.

=“Actuals” + UserResponse(“Calender Day”)

date is displaying like Actuals 3/1/2012 5:30:Am;3/22/2012 7:30 AM

I need to remove the time
and place To in the place of ; between the two dates.

I need like below

3/1/2012 To 3/22/2012

I am using xir4 and BEX as a source.
Kindly help me.

Thanks in advance

send2440 (BOB member since 2012-03-03)

2

Do the users need to see the time when they respond to the prompt? If not, you can try the following:

modify the object to display the LOV w/out the timestamp; then perform a simple string Replace() on the report to replace the ‘;’ with the ‘[ To ]’.

If the user must be presented with the timestamp:
[i]1. if you’re going to use one prompt, I suppose you could manipulate the string to strip out the date (i.e. using Left(xx) to parse out the time from the ‘to’ date and then parse out the ‘from’ timestamp by finding the position of the ‘;’ and working backwards to strip out the timestamp, then finally replace the ‘;’ with ‘To’ . not an optimal solution, but i suppose it could be do-able.

  1. present the user with 2 separate prompts (begin date, end date), then simply use formatdate() to format the date to your liking, and then concatenate the two.[/i]

hope this helps.

jresendez :mexico: (BOB member since 2004-05-03)

3

Hi

The problem is user not using the two separate dates.

Thanks

send2440 (BOB member since 2012-03-03)

4

Then I would think that my suggestion in the previous post under option #1 might work for you. Again, I know it would be a bit of a pain to have to parse out the dates, but i have seen it work in the past.

jresendez :mexico: (BOB member since 2004-05-03)

5

Hi

Please provide any example.So that i can approach.

Thanks in advance.

send2440 (BOB member since 2012-03-03)

6

Hi,

Just use =formatdate(Todate([object];“M/d/yyyy hh:mm:ss A”) ;“dd/Mmm/yyyy”)

bosateesh (BOB member since 2011-09-15)

7

I tested my theory and it seems to work.

I created a sample ‘detail’ object with the following definition.

="3/1/2012 5:30:AM;3/22/2012 7:30 AM"

I gave this object the name ‘test’ and set it to be a ‘detail’

I wrote the following formula to parse out the timestamps into what you are looking for. It seems to work fine.


=Left([test];Pos(Trim(Left([test];Pos([test];";")-1));" ")) + " to " + Substr([test];Pos([test];";")+1;Pos(Substr([test];Pos([test];";")+1;Length(Trim([test])));" "))

The result of the formula is a date in the format you are looking for.
3/1/2012 To 3/22/2012 :+1:

jresendez :mexico: (BOB member since 2004-05-03)

8

Hi

Thanks a Lot.Its work to me.

Thanks

send2440 (BOB member since 2012-03-03)