SQL 2005 Reporting Services - Wrong Average Values

问题:

I have a report which basically shows time info.

There are three levels or groups:

 Route
    Fleet
      Vehicle

Under the Vehicle level, the report shows time info for each trip performed by the vehicle, and a time average for the vehicle.

Inside the Fleet level, the report shows time info for each vehicle, and an average for that Fleet.

The same applies for the Route level. It lists time info for all the Fleets that travel through that route, and an average for that route.

The problem is that I am getting some weird Route averages. And I am using the standard Avg function for all the average fields.

For instance, the first line is supposed to show an average of the times in bold. On the second column, it brings the time average value of 01:25:30. What I wished was (01:55:50 + 01:10:20)/2 = 01:33:05.

I guess that instead of considering only the Fleet times in the average, all the times below the first row are being considered.

Any way of achieving what I want?

Thanks in advance

回答1:

Have you actually made groups ?

If your average function is not in the correct table (group) section, it yields results for the section it is in.

Right-click on the left header row, and click "insert group".

回答2:

have you made sure that you've got it averaging the same field at each level? Also, could you post what your table looks like in design view? Another helpful item would be what your sql code looks like