I have a report that uses a cross-tab to summarise informattion form a table. Int his table we have room nights and rates from a hotel. Sample data below.
BookingDate Description BusinessSource TotalRent Days Reference
2011-05-19 Superior Suite Accommodation 789.00 3 HRB0162
2011-05-17 Superior Suite Agent Direct 789.00 3 HRB0132
2011-05-18 Poolside Room Agoda 0.00 0 HRB0146
2011-05-19 Poolside Room Agoda Special 1680.00 7 HRB0163
2011-05-18 Deluxe Suite Agoda Special 3340.00 11 HRB0150
2011-05-16 Deluxe Suite Agoda Special 1002.00 3 HRB0126
2011-05-16 Poolside Room Agoda Special 1680.00 7 HRB0127
2011-05-16 Superior Suite Agoda Special 2184.00 7 HRB0128
In the cross tab I need to group by the source, then for each source show the total room nights, sum(Days), but also find the average room rate per source, and the overall average room rate, and this is where I have a problem.
I have a calulated field which is avgrate which is TotalRent / Days, but when I use the average it is giving me the average of the averages, where I actually need sum(TotalRent) / sum(Days).
I have tried different ways to do this, but have not been sucessful. Can anyone offer some advice where to start looking, or how to do this.