hh-mm-ss format problem ?

[Ayato]

Hello @ll,

In Cognos report net, I need to format a amount of seconds to hh.mm.ss format.

Any idea how to do that ?

e.g: 120 = 00:02:00

regards,
Ayato

 

[RoMaxx77]

Hi,

we have the same problem and we asked the support about that. They told us there is no possibility at the moment because nobody need that.

 

[djknuddel]

Hi Ayato

try this formula:

concat(concat(concat(concat(Decode ( Length (Floor ( ([T_LOGIN] ) / 3600 ) ), 1,
concat('0',Floor (([T_LOGIN] ) / 3600 )), Floor (([T_LOGIN]) / 3600 ) ), ':'),
Decode ( Length ( Floor ( mod((([T_LOGIN] ) / 60), 60 ) ) ), 1,
concat( '0', Floor ( mod((([T_LOGIN]) / 60), 60 ))) ,
Floor (mod( (([T_LOGIN] ) / 60),60 ) ) )) , ':'),
Decode ( Length ( Round ( mod(mod(([T_LOGIN] ) ,60 ),60 ) )) , 1 ,
concat('0', Round (mod(mod(([T_LOGIN] ),60),60), 0)) , Round ( mod(mod(([T_LOGIN] ),60),60), 0) ))


[T_LOGIN] is the time in seconds.
The result is a string that you cannot calculate to a sum or so. So this solution isn't perfect.

 

[DoubleD]

How about three separate fields concatenated:

Hours: Round-Down([T_LOGIN]/3600)
Minutes: Round-Down(([T_LOGIN]-(Hours * 3600))/60,0)
Seconds: Mod([T_LOGIN],60)

Time: Number-To-String-Padded(Hours,2)+":"+Number-To-String-Padded(Minutes,2)+":"+Number-To-String-Padded(Seconds,2)

In a single calculation it would look like:
Time: Number-To-String-Padded(Round-Down([T_LOGIN]/3600),2)+":"+Number-To-String-Padded(Round-Down(([T_LOGIN]-(Round-Down([T_LOGIN]/3600) * 3600))/60,0),2)+":"+Number-To-String-Padded(Mod([T_LOGIN],60),2)

Pain is stress leaving the body.

DoubleD

 

[Ayato]

Hi @ll,

Thx for your advise, I have build another formula in Excel that is usable in COGNOS using your idea...

=CONCATENATE(ROUNDDOWN((AI26/3600),0),":",ROUNDDOWN((AI26-ROUNDDOWN((AI26/3600),0)*3600)/60,0),":",MOD(AI26,60))


regards,
Ayato

 

[RoMaxx77]

Hi all,

we were using the concat construct from djknuddel but until MR2 there is no possibility to use this. All our reports aren't runable so we had to uninstall MR2 and install MR1 again.

Is here anyone how can help us with this issue?

Thank you.

 

[DoubleD]

RoMaxx77,
Have you tried the calculation I posted above. It should work in MR1.

I am what I am based on the decisions I have made.

DoubleD

 

[RoMaxx77]

Hi DoubleD,

the calculation you posted worked also fine but with MR1. With MR2 it is not possible. We get the ORA-00979 error, QE-DEF-0177 or UDA-SQL-0446.

So we analyzed that problem and if we delete the concat construct, it works. But we need this time format for our customers. We have shut down our live environment within service hours that our customers are able to run reports later this day. So we have uninstalled MR2 and re-installed MR1.

 

[DoubleD]

Sounds like its a problem with MR2 talking to your version of Oracle.
What version of Oracle are you running, and are you using the drivers that Cognos supports?

I am what I am based on the decisions I have made.

DoubleD

 

[RoMaxx77]

Hi,

we use oracle 9.202 and we are using only the drivers that cognos supports. Are there any who were not reported?

 

[RoMaxx77]

supported I mean...

 

[RoMaxx77]

I asked your DBA and he said that we have installed version 9.204

 

[DoubleD]

So basically you'll need to try each of the functions individually to find out which one is failing. Let me know which one does not work with Oracle 9.204 and I'll see if I can come up with an alternative.

I am what I am based on the decisions I have made.

DoubleD

 

[RoMaxx77]

Hi,

this one is failing:

concat(concat(concat(concat(Decode ( Length (Floor ( ([T_LOGIN] ) / 3600 ) ), 1,
concat('0',Floor (([T_LOGIN] ) / 3600 )), Floor (([T_LOGIN]) / 3600 ) ), ':'),
Decode ( Length ( Floor ( mod((([T_LOGIN] ) / 60), 60 ) ) ), 1,
concat( '0', Floor ( mod((([T_LOGIN]) / 60), 60 ))) ,
Floor (mod( (([T_LOGIN] ) / 60),60 ) ) )) , ':'),
Decode ( Length ( Round ( mod(mod(([T_LOGIN] ) ,60 ),60 ) )) , 1 ,
concat('0', Round (mod(mod(([T_LOGIN] ),60),60), 0)) , Round ( mod(mod(([T_LOGIN] ),60),60), 0) ))

 

[DoubleD]

That was djknuddel's formula.

Try this one:
Number-To-String-Padded(Round-Down([T_LOGIN]/3600),2)+":"+Number-To-String-Padded(Round-Down(([T_LOGIN]-(Round-Down([T_LOGIN]/3600) * 3600))/60,0),2)+":"+Number-To-String-Padded(Mod([T_LOGIN],60),2)

If it fails, try a calculation with just Number-To-String-Padded.
Then a calculation with just Round-Down.
Then a calculation with just Mod.


I am what I am based on the decisions I have made.

DoubleD

 

[RoMaxx77]

Where do I find this functions like:

Number-To-String-Padded,
Round-Down ?

Direct in Report Studio?

 

[DoubleD]

I'm not sure how these are labeled in ReportNet. But I'm sure Cognos did not take this functionality away.

I am what I am based on the decisions I have made.

DoubleD

 

[RoMaxx77]

I tested it and does not work within Report Studio.

 

[DoubleD]

RoMaxx77,
Which of the functions are failing?
Number-To-String-Padded, Round-Down, or Mod?

I am what I am based on the decisions I have made.

DoubleD

 

[Draoued]

I did this one in ReportNet with Oracle 9i.

decode(length(Round( 5113/3600)) , 1, '0'|| to_char(Round(5113/3600)),to_char(Round(5113/3600)))
||':'||decode(length(Round((5113 - (Round(5113/3600)* 3600))/60)) ,1, '0'|| to_char(Round((5113 - (Round(5113/3600)* 3600))/60)), to_char(Round((5113 - (Round(5113/3600)* 3600))/60)))||':'||decode(length(Mod(5113,60)) , 1, '0'|| to_char(Mod(5113,60)),to_char(Mod(5113,60)))


Replace the 5113 by your field name.
I noticed that if there is no space around the "-" then the formula gives an error.