Recreate a Report Variable Formula as an Object in Universe

system · May 1, 2015, 1:40pm

Hi guys,

Before I begin, I would like to thank you all for this forum as it is the best resource available for troubleshooting and advising on BOXI!

My query revolves around a variable object I created within a report that subtracts 2 dates and formats the result into the time format the user wants.

I was stuck on this too (I have only been using BOXI for 6 months and being trained ‘on the job’), but you guys came to the rescue with the formula I needed (credits calculate difference between two times in webi and Subtracting Dates, thank you @mikca and @Listserv Archives!). The variable formula is like below. Its rather long and can probably be tidied up itself:

=If IsNull( 
( 
( 
( 
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ;"HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 + 
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ;"HH:mm:ss" ) ; 4 ; 2 ) ) * 60 +
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ; "HH:mm:ss" ); 7 ; 2 ) ) ) 
- 
( 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 + 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 4 ; 2) ) * 60 + 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 7 ; 2 ) ) ) 
) 
) / 60 + (DaysBetween([Result DateTime (Date &amp; Time)] ; [Vetted DateTime (Date &amp; Time)]) *24 * 60 )) Then "00:00:00" Else FormatNumber((Floor(Truncate(Mod( 
( 
( 
( 
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ;"HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 + 
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ;"HH:mm:ss" ) ; 4 ; 2 ) ) * 60 +
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ; "HH:mm:ss" ); 7 ; 2 ) ) ) 
- 
( 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 + 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 4 ; 2) ) * 60 + 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 7 ; 2 ) ) ) 
) 
) / 60 + (DaysBetween([Result DateTime (Date &amp; Time)] ; [Vetted DateTime (Date &amp; Time)]) *24 * 60 ) ; 3600) ; 0) / 60) ) 
;"00")+":"+FormatNumber(Floor(Mod(Mod( 
( 
( 
( 
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ;"HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 + 
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ;"HH:mm:ss" ) ; 4 ; 2 ) ) * 60 +
ToNumber( Substr( FormatDate([Vetted DateTime (Date &amp; Time)] ; "HH:mm:ss" ); 7 ; 2 ) ) ) 
- 
( 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 + 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 4 ; 2) ) * 60 + 
ToNumber( Substr( FormatDate([Result DateTime (Date &amp; Time)] ; "HH:mm:ss" ) ; 7 ; 2 ) ) ) 
) 
) / 60 + (DaysBetween([Result DateTime (Date &amp; Time)] ; [Vetted DateTime (Date &amp; Time)]) *24 * 60 ) ; 3600) ; 60) ) ;"00")

Rather than having this available on the one report, the user would like it available at universe level, and I thought it might be (almost) as easy as copy and paste, but no such luck.

Can anyone help guide me on how to create this as an object at universe level, or if it is even possible at all?

Many Thanks

TheOriginal (BOB member since 2015-05-01)

system · May 1, 2015, 6:11pm

Welcome to B:bob:B!

The syntax depends on a database that you use. Which one do you use?

Marek Chladny (BOB member since 2003-11-27)

system · May 5, 2015, 7:53am

Hi Marek, thanks for the welcome and reply!

It is an Oracle DB, version 11.

TheOriginal (BOB member since 2015-05-01)

system · May 5, 2015, 8:13am

Hi,

Calculate the number of seconds between the 2 dates:

(date1 - date2) * 86400

Then convert the number of second into hours:minutes:seconds format using one of these suggestions:

I haven’t tried the suggestion myself, don’t have an Oracle database to test them now.

Marek Chladny (BOB member since 2003-11-27)

system · May 5, 2015, 2:53pm

Brilliant Marek, thank you!

In case it helps anyone else, the code I used is below.

For the minutes between 2 dates:


to_char(round(to_number(@Select(Time Periods\Vetted DateTime (Date &amp; Time))-@Select(Time Periods\PID DateTime (Date &amp; Time)))*1440),'99') || ' Mins'

For the time between 2 dates formatted as HH:mm:ss:


TO_CHAR(TRUNC(((@Select(Date1) - @Select(Date2)) * 86400)/3600),'FM9900') 
|| ':' ||
TO_CHAR(TRUNC(MOD(((@Select(Date1) - @Select(Date2)) * 86400),3600)/60),'FM00') 
|| ':' || 
TO_CHAR(MOD(((@Select(Date1) - @Select(Date2)) * 86400),60),'FM00')

TheOriginal (BOB member since 2015-05-01)

system · May 18, 2015, 10:59am

To continue this thread, does anyone know how to get the resulting value returned as a Date object, rather than a character object? Our user needs this returned as a Date object to perform sorts and add into charts.

I have tried the below but get the error : ORA-01810: format code appears twice


to_date(to_char(round(to_number(@Select(Time Periods\Vetted DateTime (Date &amp; Time))-@Select(Time Periods\PID DateTime (Date &amp; Time)))*1440),'99'), 'dd/mm/yyyy HH:mm:ss')

Many thanks for any help or guidance you guys can provide.

TheOriginal (BOB member since 2015-05-01)

system · May 18, 2015, 12:50pm

MM is Month
MI is Minutes

You have MM twice, which is a common mistake.

Dave Rathbun (BOB member since 2002-06-06)

system · May 19, 2015, 8:40am

Brilliant, thanks Dave. Its always the simple things that are overlooked!!

I gave you the wrong code snippet in my post, but I was able to transfer this into another object I was developing!

The code I mentioned in my previous post was used to calculate the total minutes between 2 date objects. In case anyone finds it useful in the future, I used the following code to do this:


to_number(to_char(round(to_number(@Select(Time Periods\Vetted DateTime (Date &amp; Time))-@Select(Time Periods\PID DateTime (Date &amp; Time)))*1440)))

To continue the date theme, I have been going around in circles to convert the below into a Date object. Like above, it is to work out the time between 2 date objects, but this time format it as HH24:MI:SS and set it as a date object. So far I have only be able to create it as a character object which doesn’t suit the user who needs it as a date object.

As a character object, the time calculates correctly and is formatted as HH:MI:SS e.g. 00:27:00. I was hoping it would convert easily to a date object.

Currently I am getting the error: ORA-01850: hour must be between 0 and 23.

I have tried converting each character into a number then to_date() but no luck. I’m not sure if the ‘:’ between the hours, mins and secs is throwing it off.

Or maybe I have just over-complicated things entirely!

Anyone any ideas?


to_date(
TO_CHAR(TRUNC(((@Select(Time Periods\Vetted DateTime (Date &amp; Time)) - @Select(Time Periods\PID DateTime (Date &amp; Time))) * 86401)/3600), 'FM9900') 
|| ':' ||
TO_CHAR(TRUNC(MOD(((@Select(Time Periods\Vetted DateTime (Date &amp; Time)) - @Select(Time Periods\PID DateTime (Date &amp; Time))) * 86401),3600)/60), 'FM00') 
|| ':' || 
TO_CHAR(MOD(((@Select(Time Periods\Vetted DateTime (Date &amp; Time)) - @Select(Time Periods\PID DateTime (Date &amp; Time))) * 86401),60), 'FM00')
, 'HH24:MI:SS')

Again, many thanks for any help or guidance anyone can give.

TheOriginal (BOB member since 2015-05-01)

system · May 20, 2015, 10:28am

[quote=“TheOriginal”]Brilliant, thanks Dave. Its always the simple things that are overlooked!!

I gave you the wrong code snippet in my post, but I was able to transfer this into another object I was developing!

The code I mentioned in my previous post was used to calculate the total minutes between 2 date objects. In case anyone finds it useful in the future, I used the following code to do this:


to_number(to_char(round(to_number(@Select(Time Periods\Vetted DateTime (Date &amp; Time))-@Select(Time Periods\PID DateTime (Date &amp; Time)))*1440)))

TheOriginal (BOB member since 2015-05-01)