Averaging time from calculated report items in SSRS

  • Greetings everyone,

    I'm relatively new to scripting in general, and very new to T-SQL and SSRS.

    I've created a report in SSRS for BI designed to show data for call-center ticketing. In this case, I have a column for each ticket's open time, another for its resolved time, and a third that takes the datediff to show what the total elapsed time is (per ticket). That works fine.

    I had to rig up some logic to break down the tickets by severity, so for example, critical = 1, major = 2, etc up to 4. I have nested IIF statements that look at the severity, and determine if each ticket met our SLA's (two hours for critical, eight for major, etc). If the elapsed time fell under the correct time limit for its relative severity, I placed a "1" in another column for that row. If not (a critical ticket resolved in over 2 hours, say), it puts a zero there instead. So far so good. That all works fine, as does taking an average of the number of 1's over the total number of tickets in that severity.

    Now I need to find the average elapsed time for only those tickets which met our service levels (any row with a calculated '1' in it). I started out by making yet another row that repeats the elapsed time if the '1' is present, or puts a '0' if the "met" column has a zero in it. This (I thought) would allow me to add the timespans in the new column and divide it by a count of 1's in the "met" column. Here's where everything breaks down. Firstly, some visual reference:

    This is the original result before I tried to do anything (it works):

    And here's the layout tab of the same:

    I was having trouble with groups and aggregates to begin with (I could add some expressions on the inner code row, but nowhere else), so I removed the groups and saved as a new report, this one stripped down to essentials so I could play with it. (The Average time to resolve column is just the placeholder while I test). Here's the result and the layout:

    SSRS won't let me AVG the times, or convert them to anything, seeing as how they're report items. My first coded attempt was merely:

    =IIF(ReportItems!InLevelCalc.value=1, (AVG(ReportItems!ResolvedTime_1.Value)), 0)

    That didn't work, so I tried this (which worked on its own, giving me the "elapsed time or a zero" column:

    =IIF(ReportItems!InLevelCalc.value=1, (ReportItems!ResolvedTime_1.Value), 0)

    SSRS is either refusing to manipulate the ReportItems!blah.value, or refusing to allow placement outside a page header/footer, or if placed there, refuses to aggregate more than one value. Can anyone give me an idea of how to approach this? The ultimate goal is simply to take the given datetime info, extract the values that meet SLA's, and then average the results for the final value (average time to resolution).

    Much obliged

  • As an update, I've now created a new datasource table which has the calculated time-to-resolve as its own column, so now it's a field value rather than a reportitem value. What used to be ReportItems!ResolvedTime_1.value is now Fields!ResolvedTime_1.value.

  • OK. what is the data type of Fields!ResolvedTime_1.value.

    Are you not trying to use AVG on a date time column.

    convert them into minutes or seconds, treat them as a number and then try to 'average'.

    View Siva Gurusamy's profile on LinkedIn

    "Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply