LIST OF TIME/TIMESPAN VALUE TROUBLE

[fgarav]

Hi all,
the enviroment is SQL SERVER 2008 with COGNOS 8.4, . on database i have a table that contain field where is stored timespan of working hour formatted as integer of minutes, ex 480 minutes(integer) = 8:00:000 hour (eight hour) .
I have had serious problem with FRAMEWORK and Report Studio , to produce  formatted output that can be used on Report Studio and can use for do some calculation such Total etc etc.
After create a function that convert this value on time on SQL SERVER side , i have try to produce the report , but when i run it i receive some error ... so my question is.. COGNOS can sum TOTAL of field TIME... if yes can someone teach me how to do that?

[cschnu]

I would sum the integer minutes value and then convert minutes to datetime

[fgarav]

thx for the answer, can u show me how to do that?

[cschnu]

What do you want your final format to look like?

Do you just want the number of total hours? Or are you looking for a time span between two dates?

[fgarav]

thx for the answer cschnu, i'm not interested in timespan between two date , this beacuse each record is the number of hour that a employee have spent in one project or something else.
I need to understand the number of total hour for each person...the format is hour and minute....
thx

[cschnu]

thx for the answer cschnu, i'm not interested in timespan between two date , this beacuse each record is the number of hour that a employee have spent in one project or something else.
I need to understand the number of total hour for each person...the format is hour and minute....
thx

Ok, I would then just sum the integer value of the minutes. Then I would calculate the hours and minutes like so:

minutes/60 and round down to the nearest integer to get the number of hours. Then I would use the MOD function to obtain the remainder which would be the minutes. So your expression should look like this in Cognos:
floor(110/60) - Will give you hours
mod(110, 60) - Will give you minutes

[fgarav]

@cschnu...
first of all thx for the answer  that's you say it's correct, is normal math operation , but the problem is not this.. the problem is in visualizzation ,better when i made calculation,total and so on...
My final result must be if possibile something like this:
row1  8:30 this value is the number of hour worked not 08:30PM
row2  8:30
row3  8:30
row4  8:30
total  33 hour and 0 minutes...
So your calculation it's correct, but how i can format the two field (hour,minutes) that make possible the normal aggregation or total?
If i concatenate the result, i obtain a varchar value and with this data type is not possible use calculation and so..
Hope that i explain the problem in right way..
Thx in advance if u have some tips...

[cschnu]

@cschnu...
first of all thx for the answer  that's you say it's correct, is normal math operation , but the problem is not this.. the problem is in visualizzation ,better when i made calculation,total and so on...
My final result must be if possibile something like this:
row1  8:30 this value is the number of hour worked not 08:30PM
row2  8:30
row3  8:30
row4  8:30
total  33 hour and 0 minutes...
So your calculation it's correct, but how i can format the two field (hour,minutes) that make possible the normal aggregation or total?
If i concatenate the result, i obtain a varchar value and with this data type is not possible use calculation and so..
Hope that i explain the problem in right way..
Thx in advance if u have some tips...

Ok, so I think you need to do your sum calculation separate of your display. So drag a data item from the tool box in your query to display on your report. Your data item should look similar to this

Code Select Expand

floor([WorkLogSQL].[MINUTES]/60) || ':' || mod([WorkLogSQL].[MINUTES], 60)

That will give you your row by row display. So to display this on your report you need to drag the display data item over but it will not work to sum that because it is not an integer so then for the totals replace in the query what it normally a total with this calculation:

Code Select Expand

floor(Total([WorkLogSQL].[MINUTES]) / 60) || ':' || mod(Total([WorkLogSQL].[MINUTES]), 60)

I actually used a singleton to get the total to display in the totals line of my report but I'm guessing you can just edit the data item that it would normally generate in the query and go that way as well.

[fgarav]

thx again for the answer...
i try the tips you suggest me. i create a new data item on query , and to this:
floor(Total([Consolidation].[PERSONALE_FACT].[REDDITO]) / 60) || ':' || mod(Total([Consolidation].[PERSONALE_FACT].[REDDITO]); 60)..
if i run test all it's ok, so this say me that is no error on my formula.
Now on the report , if i put this column from the query ,when i run the report i recive this error :
'sqlPrepareWithOptions' stato='-56' and many other....
what's wrong ?

[cschnu]

floor(Total([Consolidation].[PERSONALE_FACT].[REDDITO]) / 60) || ':' || mod(Total([Consolidation].[PERSONALE_FACT].[REDDITO]); 60)..

Mod uses a comma not a semicolon.

[fgarav]

thx for answer but it's a mistake... the suggestion tips of mod function reporting this:
mod ( expressione1_number_int; expressione2_number_int), and so it's a semicolum.. but if i use it i receive the same errror... it's like if COGNOS can't use cast with ||:||
if i do as you suggest me , same function but with comma , on test on report studio dataitem i receive errror code QE-DEF 0459

[cschnu]

thx for answer but it's a mistake... the suggestion tips of mod function reporting this:
mod ( expressione1_number_int; expressione2_number_int), and so it's a semicolum.. but if i use it i receive the same errror... it's like if COGNOS can't use cast with ||:||
if i do as you suggest me , same function but with comma , on test on report studio dataitem i receive errror code QE-DEF 0459

Break it apart to see exactly which part of the expression you are getting an error on. Try the just the floor function first, if that works then try the mod function and if that works try the concatenation. Cognos is not really informative on which part of your expressions fail so i typically break them apart until I find the point of failure.

Also, I am on Cognos 8.3 here is the tip for MOD


mod ( integer_exp1, integer_exp2 )
Returns the remainder (modulus) of integer_exp1 divided by integer_exp2. The integer_exp2 must not be zero or an exception condition is raised.