Avatar of townsma
townsmaFlag for Indonesia

asked on 

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
Crystal Reports

Avatar of undefined
Last Comment
Ido Millet
Avatar of Mike McCracken
Mike McCracken

What version of Crystal?

What are you using for rows and columns?

mlmcc
Avatar of townsma
townsma
Flag of Indonesia image

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)


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
ASKER CERTIFIED SOLUTION
Avatar of PCIIain
PCIIain
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of townsma
townsma
Flag of Indonesia image

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
Avatar of PCIIain
PCIIain
Flag of United States of America image

Blurred text
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
Avatar of Ido Millet
Ido Millet
Flag of United States of America image

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

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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo