I am reporting on an IT Service Desk. My report is in two groups, Incidents and Service Requests.

 

I wish to obtain the average fix time for Incidents.

 

In the details section, i have the formula

 

@fixtime

{vTASKS_BROWSE.CLSDDATE}-{vTASKS_BROWSE.OPENDATE}

 

and in the group footer I have

 

@AvgResTime

 

For some reason, the result of this formula is always the same for each group, and I don't understand why.

 

Many thanks for your help.

 

Andy

 

 

It has raised another question though (cheeky, I know)

 

@AvgResTime now has two values, one for each group.

 

How would I select one of these values to work with? i.e. there is text in my report that says:

 

"The average resolution time for Incidents is " & @AvgResTime

 

By default, it seems to use the first group value, which is handy. But what if I wanted to use the second value?

 

I would recommend deciding how you want your end result to appear and then working on a solution from there.

 

The "by default it grabs the first record" might be misleading. MY guess is you placed this in the report header which will only evaluate for the first row which in this case is equivalent to the first group. If you place it in the report footer you would get the last row (or equivalent of the last group).

 

A few solutions are:

Create specific running totals to evaluate for only specific records.

Create variable formuals to do the same thing as the RTs.

use a Cross tab. The beauty of a crosstab is that it expands as your gros expand where as the RTs or variable formulas need to be created per group.

 

However I would rework this entirely to be a little more efficient and give you more flexibility.

It appears you are looking at average days for you evaluation.

 

I prefer using datediff to get the value

Now just insert an summary of this field as an average at the group level.

You can also use this in a crosstab or chart in a the report header.

it will end up looking like

 

if you really want to control this for a sepecifc value use a running total

name=av_Inc

field to summarize=@fixtime

type=average

evaluate=use a formula

table.field='incident' (however you identify an incident row)

reset=never

place in report footer

RT's do not work in headers.

 

if it must be in a header there is another trick. Just let me know if you need it.