March 1, 2011 at 10:23 am
Hi there,
Hopefully someone can assist on this one.
I have managed to convert a field to read a response time from seconds into hh:mm:ss.
I need to go one step further and include the information for the average call time. So logically this would be total response time/call count.
However I keep getting the #Error each time because of the field format I believe. I have tried all of the different formats but nothing is working.
I have used this expression:
=sum(Fields!response_time.Value)/count(fields!callref.Value)
When I use the following [Max(response_time)] this works fine and the formatting doesn't error.
Does anyone have any pointers to where I am going wrong? I know it is the formatting, should I be adding some kind of convert again into the expression?
Many thanks in advance!
March 1, 2011 at 12:56 pm
Could you supply the table definition, some sample data and desired results from that sample data.
To do so easilu\y click on the first link in my signature block to learn how to do that quickly
March 2, 2011 at 4:31 am
My apologies I need to remember to just include everything from now on and that it's never too much information.
Ok so for the table here is the query used to get the initial information:
SELECT
callref,
cust_name,
logdate AS 'Logged On',
closedate,
custom_f AS 'Subject',
CONVERT(CHAR(9), DATEADD(SECOND, resp_time, '00:00:00'),108) AS [response time],
CONVERT(CHAR(9), DATEADD(SECOND, fix_time, '00:00:00'), 108) AS [fix time],
CONVERT(CHAR(20), DATEADD(second, closedatex, '19700101'), 103) AS [complete date],
closedby
FROM opencall
WHERE (status IN ('6', '16', '18')) AND (suppgroup IN ('group1', 'group2'))
AND (closedate BETWEEN @startdate AND @enddate)
and closedby = 'User1'
ORDER BY closedby, 'Complete Date'
From here, I need to get the average time across a user's calls. This is where I used
=sum(Fields!response_time.Value)/count(fields!callref.Value)
However this is when I get the #ERROR I assume due to the text box properties. I have it set to number but I assume this is not correct?
I do have one other question. For 2 of the calls the response time is 223:52:54 (if you calculate it and states this in the system we are currently running the reports in), however when the data is extracted in Reporting Services it shows as 07:52:54. I assume it has something to do with it being more than 24 hours has anyone else experienced this? I am happy to move this last bit to a new thread if that would be better.
Manh thanks for any assistance.
March 2, 2011 at 4:46 am
karen.blake (3/2/2011)
I do have one other question. For 2 of the calls the response time is 223:52:54 (if you calculate it and states this in the system we are currently running the reports in), however when the data is extracted in Reporting Services it shows as 07:52:54. I assume it has something to do with it being more than 24 hours has anyone else experienced this? I am happy to move this last bit to a new thread if that would be better.
For this part, it's due to you essentially getting to "time" part of a "datetime". So you'd need to calculate it yourself rather than using the inbuilt formatting.
e.g.
= (Fields!response_time.Value / 3600) & ":" & ((Fields!response_time.Value Mod 3600) / 60) & ":" & ((Fields!response_time.Value Mod 3600) Mod 60)
karen.blake (3/2/2011)
SELECTcallref,
cust_name,
logdate AS 'Logged On',
closedate,
custom_f AS 'Subject',
CONVERT(CHAR(9), DATEADD(SECOND, resp_time, '00:00:00'),108) AS [response time],
CONVERT(CHAR(9), DATEADD(SECOND, fix_time, '00:00:00'), 108) AS [fix time],
CONVERT(CHAR(20), DATEADD(second, closedatex, '19700101'), 103) AS [complete date],
closedby
FROM opencall
WHERE (status IN ('6', '16', '18')) AND (suppgroup IN ('group1', 'group2'))
AND (closedate BETWEEN @startdate AND @enddate)
and closedby = 'User1'
ORDER BY closedby, 'Complete Date'
From here, I need to get the average time across a user's calls. This is where I used
=sum(Fields!response_time.Value)/count(fields!callref.Value)However this is when I get the #ERROR I assume due to the text box properties. I have it set to number but I assume this is not correct?
For this part, I'm guessing the error is because you're attempting to divide a "CHAR" by an "INT". Convert the "CHAR" into a numerical datatype then try again.
March 3, 2011 at 3:16 am
Thanks skcadavre, that's really helpful.
For the first part I just need to get rid of the decimals but I know that will be a format issue.
For the second part I will give it a go!
March 11, 2011 at 3:07 am
skcadavre (3/2/2011)
karen.blake (3/2/2011)
I do have one other question. For 2 of the calls the response time is 223:52:54 (if you calculate it and states this in the system we are currently running the reports in), however when the data is extracted in Reporting Services it shows as 07:52:54. I assume it has something to do with it being more than 24 hours has anyone else experienced this? I am happy to move this last bit to a new thread if that would be better.For this part, it's due to you essentially getting to "time" part of a "datetime". So you'd need to calculate it yourself rather than using the inbuilt formatting.
e.g.
= (Fields!response_time.Value / 3600) & ":" & ((Fields!response_time.Value Mod 3600) / 60) & ":" & ((Fields!response_time.Value Mod 3600) Mod 60)
Ok, I do have one teeny tiny problem (not her again I hear you groan!) in that the first number has decimal places after it. One call was open for 233:52:52 but it is coming up as 233.8811111111:52:52
I have tried the formats in the actual field and searched for how to convert this but I either get errors in Reporting Services or it doesn't work.
Any ideas?
March 11, 2011 at 3:48 am
I'm unable to reproduce that result - can you let me know what the response_time.value was please?
March 11, 2011 at 3:53 am
skcadavre, thanks the value for the response time was 805972 if that helps....
March 11, 2011 at 4:20 am
Hmmm, strange. Never come across this issue before.
This would solve the problem: -
=floor((Fields!response_time.Value / 3600)) & ":" & floor(((Fields!response_time.Value Mod 3600) / 60)) & ":" & floor(((Fields!response_time.Value Mod 3600) Mod 60))
March 15, 2011 at 6:15 am
skcadavre, thank you so much for this! I now have more reading to do on the Floor function too.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply