How to sum a Time Measure in Cognos Analytics 11.1

Question

I am unable to sum phone call times that are being stored in a SQL fact table

I have a SQL table of phone call times, by person, which are being used in a Cognos Analytics Data Module.

I would like to use this data in a crosstab and report a total call time in HH:MM:SS for each person.

SQL column is formatted time(0) when I try to Total the time, Cognos throws a [MSR-VRF-1202 The column "Call Length" has the data type "time" and can't be aggregated] error.

What is the correct way to display a total of Call Times.

UPDATE

I have managed to overcome half the problem by using a number of query calculations in Cognos Anaytics. I updated the SQL fact table to include a new measure of time in seconds then used the query items below.

HH as FLOOR(TOTAL([CALLTIMEINSECONDS])/3600)

MM as FLOOR(([CALLTIMEINSECONDS]-[HH]*3600)/60)

SS as [CALLTIMEINSECONDS]-([HH]*3600)-([MM]*60)

HH1 as IF ([HH]<10) THEN ('0'+CAST([HH],VARCHAR(4))) ELSE (CAST([HH],VARCHAR(4)))

MM1 as IF (MM<10) THEN ('0'+CAST(MM,VARCHAR(2))) ELSE (CAST(MM,VARCHAR(2)))

SS1 as IF (SS<10) THEN ('0'+CAST(SS,VARCHAR(2))) ELSE (CAST(SS,VARCHAR(2)))

ALL TIME as [HH1]+':'+[MM1]+':'+[SS1]

This works in individual Crosstabs rows however will not summarise and also does not work on any charts.

Thanks ACF, first ever post on a forum, of any kind, wondered what I was doing wrong with the question! I have a JDBC connection from Cognos Analytics to SQL server 2017 DB. — Jon B, Feb 16 '19 at 12:50
First, there's nothing wrong with your question. Second, What is the data provider you are using? Last, are you storing the length of time of the call or the actual time the call took place? — Johnsonium, Feb 19 '19 at 16:48

score 1 · Answer 1 · answered Feb 16 '19 at 16:54

Why would two individuals take the time to read and vote a genuine request for assistance down? I thought this was a place to share knowledge and experience. Clearly these people need to get out more, a life, or get laid. Sorry I can’t help but hopefully someone else will

score 0 · Answer 2 · answered Feb 20 '19 at 01:11

I'll assume

you would use the terms time and duration synonymously.
you are using MS SQL Server.

Adjust your final solution accordingly.

My solution in SQL is:

format(sum(DATEDIFF(second, TIMEFROMPARTS(0,0,0,0,0), [CallDuration])) / 3600, '0#') + ':' + 
format(sum(DATEDIFF(second, TIMEFROMPARTS(0,0,0,0,0), [CallDuration])) / 60, '0#') + ':' + 
format(sum(DATEDIFF(second, TIMEFROMPARTS(0,0,0,0,0), [CallDuratoin])) % 60, '0#')

A Cognos-based solution may involve multiple queries. Here are the basic pieces:

Convert the times to numbers.
Sum the numbers.
Convert the summed numbers to times (or time parts -- hours, minutes, seconds).
Format the output.

If you calculate the parts separately, the formatting can be done on the page rather than the query Cognos as 5 text items, some with a Source type of Text and some with a Source type of Report Expression or Data item value. In other words, 3 query calculations and 2 text items.

<hours>:<minutes>:<seconds>

How to sum a Time Measure in Cognos Analytics 11.1

2 Answers2