How can I get an average for a row in a matrix

Question

Have a matrix report now that has Position, Hours and Wages for a location for a month.
There may be any number of locations...that's why I'm starting with a Matrix report...the user may chose up to 50 locations to view.

    
                   Location 1         Location 2
               Total Hrs Amount     Total Hrs Amount
    Position1  441.68    $4,201.46  556.73    $6,103.67

We would like to get an average by position for that month, like so

                   Location 1         Location 2            Avg
               Total Hrs Amount     Total Hrs Amount       Avg Hrs 
    Position1  441.68    $4,201.46  556.73    $6,103.67    499.20

Can't quite figure out how to get this to work in SSRS 2005...

Answer 1

You could have a calculated field with formula (rs!localtion1Hrs + rs!location2hrs) / 2.

OR

The query used to show the report can have this field as calculated column.

Answer 2

Pretty sure I've solved this by using the following as the DataSet for the report and using a matrix in the report layout.

Basically I did what shahkalpesh said above..."The query used to show the report can have this field as calculated column."

select 
  'Avg' as LocationID, 
  'Avg' as Description, 
  AccountDesc, 
  @PayrollYear as Year,
  @PayrollMonth as Month,
  avg(s.TotalHrs) as TotalHrs, 
  avg(s.Amount) as Amount from 
(
 select LocationID, 'Avg' as Description, AccountDesc, 
 @PayrollYear as Year, @PayrollMonth as Month,  
 sum(TotalHrs) as TotalHrs,
 sum(Amount) as Amount from vwPayroll
 where LocationID in (select value from dbo.ParmsToList(@PayrollLocIds)) 
 and Year = @PayrollYear and Month = @PayrollMonth
 group by LocationID, AccountDesc, Year, Month
) as s
group by AccountDesc

union all

select 
  LocationID, 
  Description, 
  AccountDesc, 
  Year, 
  Month, 
  Sum(TotalHrs) as TotalHrs, 
  Sum(Amount) as Amount 
from vwPayroll
where LocationID in (select value from dbo.ParmsToList(@PayrollLocIds)) 
and Year = @PayrollYear and Month = @PayrollMonth
group by LocationID, Description, AccountDesc, Year, Month