time interval (hh:mm:ss)

[bnbushan.cognos]

Hi All,

I've got a column of integer data representing milliseconds, that I'd like to be able to display as a time interval (hh:mm:ss) in my cognos 8.3 report..

For example, i have a integer 60000 in my database which is representing milliseconds. I should display this on the report like: 0H:1M:0S , that means one minute

Any idea please

Thanks,
Bhushan

[volviep]

I found this solution at http://www.experts-exchange.com/Database/Reporting_/Q_23040666.html based on a field containing seconds.

Well, you want to do all your rollups using the "seconds" value that comes from your cube, just becuase the math is so much easier.  Then you can fairly easily break the value you're getting from the cube into seperate dataitems to represent "Hours", "Minutes" and "Seconds" with expressions like:

"Hours" = cast([TimeValue] /3600 as Integer)
"Minutes" = cast(Mod([TimeValue],3600)/60 as integer)
"Seconds" = Mod([TimeValue],60)

For the display/validation part, the question becomes how are they doing this?  If its in a crosstab, it becomes trickier, but in a simple list you could just unlock the cell and manually position the hours, minutes and seconds calculated above in the cell (with a text ":" between 'em).

what I figured is that you will also need to change the data format of each dataitem to Number with "No. of Decimal Places" set to 0, "Minimum No. of Digits" set to 2 and "Padding Character" set to 0.