troubleshooting Question

How to get custom totals into a crystal reports cross-tab.

Avatar of townsma
townsmaFlag for Indonesia asked on
Crystal Reports
8 Comments2 Solutions3417 ViewsLast Modified:
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.

Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros