How to get custom totals into a crystal reports cross-tab.
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.
Thanks
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.
Thanks
Last Comment
ASKER
Version 2008 with SP3
For rows and columns I guess you mean in the the cross-tab?
No. Bookings Nights Booked Avg Rate
Business Source Count(BusinessSource) Sum(Days) Avg(@AvgRate)
Totals Count(BusinessSource) Sum(Days) Avg(@AvgRate)
For rows and columns I guess you mean in the the cross-tab?
No. Bookings Nights Booked Avg Rate
Business Source Count(BusinessSource) Sum(Days) Avg(@AvgRate)
Totals Count(BusinessSource) Sum(Days) Avg(@AvgRate)
CR2008 allows you to add calculated columns to the cross tab.
See this question for an explanation
https://www.experts-exchange.com/questions/27045957/Need-to-add-a-calculated-field-to-divide-two-fields-to-get-percentage-in-Cross-Tab.html?cid=1576
You probably need to add the rent as a column and then calculate in the cross tab
mlmcc
See this question for an explanation
https://www.experts-exchange.com/questions/27045957/Need-to-add-a-calculated-field-to-divide-two-fields-to-get-percentage-in-Cross-Tab.html?cid=1576
You probably need to add the rent as a column and then calculate in the cross tab
mlmcc
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
The problem with this is, the customer is very fussy, and he only wants the original columns in the cross-tab, I cannot add a total receipts column, unless there is a way to add it, but hide it.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If he insists then you will have to build a manual cross tab toget the result he wants.
mlmcc
mlmcc
In the column for the "wrong" average room rate, override the value displayed by using an expression for the Display String property.
Crystal Reports
Crystal Reports is a business intelligence application from SAP SE. It is used to graphically design data connections and report layouts from a wide range of data sources including Excel spreadsheets, Oracle, SQL Server databases and Access databases, BusinessObjects Enterprise business views, and local file-system information. Report designers can place fields from these sources on the report design surface, and can also deploy them in custom formulas (using either BASIC or Crystal's own syntax), which are then placed on the design surface. Crystal Reports also supports subreports, graphing, and a limited amount of GIS functionality.
36K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
What are you using for rows and columns?
mlmcc