Can anyone plz hlp? 2

[jadepatel]

Hi,

I have created a virtual table using the 'Add Command' when specifying a database.

The problem im having is that the data that is returned from the query is causing problems when calculating formula sum fields!!
The data is regarding a room that a guest can book and also the equipment that they order within that room.
A guest can order many rooms and also many pices of equipment withing each room.
Here is an example of the data that would be returned by the query the report is based on. The data the report is based on is all the bookings a SINGLE guest has made.
The roomcost is worked out using a formula field. RoomPrice/Night x LengthOfStay (LOS)
Equipment cost is worked out by multiplying the equipment price/day x LengthOfStay (LOS)

here is an example of the data the query will return for the bookings of a single guest:

RoomNo LOS RoomCost EquipBooking Cost
1 2 £10 x 2 VCR 8x2
1 2 £10 x 2 TV 10x4
1 2 £10 x 2 Trouser press 4x2
2 2 £20 x 2

The problem i have is that when i try and calculate the sum of the RoomCost i get a total of 100 (20+20+20+40) instaed of 60 (20 + 40).
It is counting the roomcost of room1 3 times!!!
How can i force the formula field to only count it once??

Can anyone plz help me?

 

[lbass]

You could use a running total. Select {@RoomCost}, sum, evaluate on change of field {table.room#}, reset never (or on change of group (guest). You have to have the records sorted by room# or else group on {table.room#} and evaluate on change of group (Room#).

-LB

 

[jadepatel]

Hi,

This is what i have tried and i cant seem to get the correct value.

As the query returns 3 rows for room 1 i get a total of 60 instead of 20!!!
The row for room1 has to repeat as the guest has made 3 equipment bookings within that room.
Room 2 shows the correct value as there is only a single row for this.
This is the formula field i used. I have grouped by RoomNo in my report.
LOS represents the length of stay.

Sum ({@RoomCost x LOS},{DSMaxBooking.RoomNo})

RoomNo LOS RoomCost EquipBooking Cost
1 2 £10 x 2 VCR 8x2
1 2 £10 x 2 TV 10x4
1 2 £10 x 2 Trouser press 4x2
2 2 £20 x 2

Can anyone plz help me resolve this??
I have been stuck on this problem for ages.
Many thx

 

[lbass]

You can't use a summary formula in this case. Please try the running total I suggested and place it in the detail so you can see how it is accumulating and then report back with your sample data showing the running total results. There is no reason that I can see that this would not work.

-LB

 

[jadepatel]

Hello Ibass,

How would i use the method u suggested? Im a complete beginner to crystal reports and am just fumbling my way around. I tried looking up running totals in my book and help files but had no luck!!

Is this a method via coding or a wizard.
Would really appreciate a walk-thru if u have time

thx in advance

 

[lbass]

It looks like you might be using CR 9.0, and I don't have that, so the location might be different, but what you want is the running total wizard. In 8.0, you would go to insert->field object->running total fields->click on the new icon. This brings up the wizard. Enter a name for your running total and then choose your formula {@RoomCost} from the list on the left and click the > to move it into the "Field to summarize" area. Then choose "Sum" as the "Type of summary".

If you have a group on {table.RoomNo} then choose "On change of group" for the "evaluate" section and select the Room Number group.

If you have an outer group, e.g., on {table.guest}, you would then reset on change of group (Guest). If there are no outer groups, choose "Reset Never."

Not sure the running total expert is set up exactly like this in 9.0 either. If someone who has 9.0 reads this and sees significant differences, maybe they could jump in with help on how the wizard is different in 9.0.

You want to place the running total in the group (Guest) footer or in the report footer if you have no outer group. You can also temporarily place it in the detail section if you want to see how the formula is accumulating--this is sometimes helpful for troubleshooting.

Please feel free to ask further questions.

-LB

 

[davemckie]

Hi all,

I need a formula that will count the number of records per group. Can anyone reply with a sample?

thanks in advance
dave

 

[jadepatel]

Hi Ibass,

I tried to left click on my report and select insert>field object.

The problem i have is that the 'field object' is not available in the menu. For some reason the option is greyed out!!!

The options i have are:
Special field,
Text Object,
Subtotal,
Grand total
Summary,
group,
section
crosstab
subreport
line
box
chart
picture

Would any of these be suitable?
The version of crystal reports i have is the one that ships with visual studion.NET 2002

 

[lbass]

I'm not familiar with that application. Try looking on the "insert" menu if there is one, or maybe in "build"--I'm not sure. If you can find a "field explorer", it would be listed in there. I think you will just have to search through some of the menus, unless someone else can jump in and help.

Alternatively, you could create a manual running total by creating three formulas:

If you have a group on {table.guest}, then create a reset formula:

//{@reset} to be placed in the group (guest) header:
whileprintingrecords;
numbervar roomcost := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar roomcost;

if onfirstrecord or
{table.roomno} <> previous({table.roomno}) then
roomcost := roomcost + {@roomcost};

//{@display} to be placed in the group (guest) footer:
whileprintingrecords;
numbervar roomcost;

davemckie-

The easiest way to count records in a group is to right click on a recurring field and choose insert summary->count. This will give you the total count per group. If you have duplicates, then use a running total. A simple running total (using the running total editor) would select a recurring field, count, evaluate for each record, reset on change of group.

-LB

 

[Katy44]

In Cr9 the running total option is in the Field Explorer - you would choose to add a new one.