Totaling Particular Records in a Group

[seattlelin]

I have a report that has the following data columns:

Doc Cruise Item No. Cruise Airfare Cruise Airfare
No. No. Revenue Revenue Commiss* Commiss*

1 ABC Cruise $10,000 $120
1 ABC Upgrade $2,000 $120
1 ABC Aifare $1000 $80
1 ABC Airfare $500 $80
1 ABC Extras $2,000 $120

Total $14,000 $1,500 $120 $80

* The record only shows totals, not by line item, therefore I need the total of these items to not be multiplied by the number of times it is in the report.

How can I do something like this?

 

[synapsevampire]

Try posting technical information, such as:

Crystal version
Database/connectivity used
Example data (which you did)
Expected output

Text descriptions of data generally end up creating multiple posts to discover what the data looks like and what the requirements would net.

In your example you show totals, presumably this is an example of what you don't want, perhaps you'd be better served to show what you DO want...

-k

 

[seattlelin]

synapsevampire: Thanks for the tip.

I am working with Crystal 9.0. The database is a SQL database with a OLE data connection.

The data example is above, however, the Total line is what I am looking to achieve-- and have not been able to do. (Ex. with the data set above, I get a total for Cruise Comm of $360 instead of $120 (because the $120 is listed 3 times).

 

[synapsevampire]

This looks like a join issue, and should be resolved accordingly with your dba's assistance.

Since there isn't anything obvious from the data set which indicates that these are dupes, we might make assumptions to eliminate like amounts, but this wouldn't be foolproof as a repeated amount might be justified.

Perhaps if you'd shown the tables used, the relationship, and example data within someone here can help.

-k

 

[dgillz]

If there is a many-to-one relationship between 2 tables, this type of record inflation can occur. Please let us know if this is the case.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

Instead of using a sum on these fields, use the running total expert and select {table.cruisecommiss}, maximum, evaluate for each record, reset on change of group (whichever group level you want the subtotal for--I can't tell whether this would be Doc No or Cruise No, since your example shows them each with the same value throughout).

Repeat for the airfare commission.

-LB

 

[seattlelin]

Thank you for all the comments.

Yes, dgillz, this is a one to many relationship issue between 2 tables.

lbass, I will try maximum in running total (instead of sum which is what I used befored)and let you know how it turns out.

SL

 

[seattlelin]

LBASS,

The maximum subtotal seems to work. Now, how would I come up with a Grand total if I had a report with multiple cruises?

SL

 

[lbass]

Actually, instead of using running totals, I think you could right click on {table.cruiscommission} and insert a summary (maximum). But to then sum the maximums across other groups, I think you would need to use variables. Since you have not specified a group, let's say you have a group on {table.cruiseno}(this assumes that one cruise number could have only one cruise commission and only one airfare commission). Create three formulas:

//(@accumcc} to be placed in the group (cruise number) footer:
whileprintingrecords;
numbervar grtotcc := grtotcc +
maximum({table.cruisecommission},{table.cruiseno});
numbervar grtotac := grtotac + maximum({table.airfarecommission},{table.cruiseno});

//{@displaycc} to be placed in the report footer:
whileprintingrecords;
numbervar grtotcc; //total for cruise commissions

//{@displayac} to be placed in the report footer:
whileprintingrecords;
numbervar grtotac; //total for airfair commissions

-LB