Cognos Analytics

I have a data source in Cognos 12.x  that contains an elapsed time in seconds.  The value should not exceed the number of seconds in a single day (until summed). We have a report that sums these seconds by user for one or more days (based on a filter).  I would like to show the derived total as HHH:MM.

I was initially trying to create calculations in my data module, but realized that was the wrong approach.

So in the "report" I created data expressions for hours and minutes:

Hours = floor([SECONDS]/3600)

Minutes = floor(mod([SECONDS],3600) /60)

I cannot seem to find the right syntax to CAST these values to character on either side of the colon.  Everything I've tried has either thrown and error or displayed alpha-numeric values.

Any suggestions would be welcomed.

Greg

Hi Greg, 
Here is a technique that might help. I'm using ORDER_NUMBER, to be replaced with SECONDS

The 100 + is to make the conversion from number to string always 3 characters long, this allows a substring operation to get the last 2 digits, this will then lead-zero-fill the original numbers up to 2 digits.

I have a data source in Cognos 12.x  that contains an elapsed time in seconds.  The value should not exceed the number of seconds in a single day (until summed). We have a report that sums these seconds by user for one or more days (based on a filter).  I would like to show the derived total as HHH:MM.

I was initially trying to create calculations in my data module, but realized that was the wrong approach.

So in the "report" I created data expressions for hours and minutes:

Hours = floor([SECONDS]/3600)

Minutes = floor(mod([SECONDS],3600) /60)

I cannot seem to find the right syntax to CAST these values to character on either side of the colon.  Everything I've tried has either thrown and error or displayed alpha-numeric values.

Any suggestions would be welcomed.

Greg

I have a data source in Cognos 12.x  that contains an elapsed time in seconds.  The value should not exceed the number of seconds in a single day (until summed). We have a report that sums these seconds by user for one or more days (based on a filter).  I would like to show the derived total as HHH:MM.

I was initially trying to create calculations in my data module, but realized that was the wrong approach.

So in the "report" I created data expressions for hours and minutes:

Hours = floor([SECONDS]/3600)

Minutes = floor(mod([SECONDS],3600) /60)

I cannot seem to find the right syntax to CAST these values to character on either side of the colon.  Everything I've tried has either thrown and error or displayed alpha-numeric values.

Any suggestions would be welcomed.

Greg

------------------------------
Greg Wilburn
Director of IT
Total Biz Fulfillment
Grantsville MD
------------------------------

Usually for a reporting problem, there are a few answers that will also get the same solution.  Here are a few other options to consider... 

_add_seconds

<<Edited - just realised that you may expect there to be more than 24 hours. If so this one isn't for you>>

There is actually a report function called add seconds that will add seconds to a time.   In the example below Seconds is simply a calculation of 11,520 representing a random number of seconds. Add Seconds is a calculation using the add_seconds. The third column is that calc formatted as short time. Within a report we can use formatting to avoid complicated calculations.

The calculation for add seconds is simply:   _add_seconds ( 00:00:00 , [Data Item1] )

This will also work in a data module.

Report Formatting

If you are just after a reporting only solution the you can use report formatting to format stuff on the report layout, avoiding complicated string manipulation within queries.  For example, I often see people trying to concatenate multiple strings of an address fields together to make a single address, where as you could just put the strings together on the page.  

Using your calculations for hours and minutes I have created an Hours and Minutes data item:

I can put these two data items in the same column, with a text ':' separating them:  

I can also format these data items with a number pattern of 00 to make them have leading zeroes.  

The end result is  

Now you have three different ways of achieving the same result. All will have pros/cons. The clever bit is knowing which is appropriate for your particular case.

------------------------------
Marc Reed
Reporting Lead

Original Message:
Sent: Fri February 07, 2025 03:48 PM
From: Greg Wilburn
Subject: How can I convert Seconds to hours and minutes (HHH:MM)

I have a data source in Cognos 12.x  that contains an elapsed time in seconds.  The value should not exceed the number of seconds in a single day (until summed). We have a report that sums these seconds by user for one or more days (based on a filter).  I would like to show the derived total as HHH:MM.

I was initially trying to create calculations in my data module, but realized that was the wrong approach.

So in the "report" I created data expressions for hours and minutes:

Hours = floor([SECONDS]/3600)

Minutes = floor(mod([SECONDS],3600) /60)

I cannot seem to find the right syntax to CAST these values to character on either side of the colon.  Everything I've tried has either thrown and error or displayed alpha-numeric values.

Any suggestions would be welcomed.

Greg

------------------------------
Greg Wilburn
Director of IT
Total Biz Fulfillment
Grantsville MD
------------------------------