Summary on Group information

[aaronburro]

I've got an interesting situation here, so I'll describe it

I'm doing a report where I am showing information on Invoice information, as well as InvoiceDetail information. I am grouping by Invoice and CustomerNumber, with Invoice being on the inside. I'd like to find the sum for FreightCost, which is located in a table linked to the Invoice table, which is of course at the Invoice level in the report, and not the InvoiceDetail level.

Obviously, the Invoice level is a group, and not a detail, so as I am currently doing it, I am summing the freight cost for each detail item, which is fine, as long as there is only one detail item in the invoice. However, that isn't usually the case, so my Freight cost gets inflated.

My question is this: Is there an elegant way to sum on the group and not have it sum for each detail element as well? I have already found a solution to this, but it is far from elegant. I was thinking that there might be an intrinsic method available in CR that already did this that I am blindly missing... thx in advance!

BTW, we are using CR8.5 for MAS200.

 

[lbass]

The usual solution would be either an inserted or a manual running total that evaluates on change of group. You probably should explain your solution and also give some sample data if you want further help. Your description is a little confusing.

-LB

 

[aaronburro]

Aight... example data:

Invoice# Customer# Freight Cost
1 a 500
2 b 50
3 c 200
4 b 35
5 d 75
6 c 100


Invoice# Item#
1 A
1 B
2 A
3 C
4 B
5 A
5 C
6 D

Resulting Sum(according to Crystal) of freight cost for Customer# derived by inserting a summary / subtotal on Freight Cost when Freight Cost is placed at the group footer / header for invoice:

Customer # Sum of Freight
a 1000
b 85
c 300
d 150

What the result should be:

Customer # Sum of Freight
a 500
b 85
c 300
d 75

My solution was a manual sum which is reset to zero at the beginning of each customer# and is incremented at the group Header for the invoice group.

 

[lbass]

I think that's the way to go.

-LB

 

[aaronburro]

that blows :/

do newer versions of Crystal have the ability to do such a sum as I want it, or is it just a limitation of the SQL and/or CR in general?

 

[lbass]

This is not CR's fault--it's because of your database design. You could use also use a SQL expression to return the sum of freight, but I remember that you had trouble with that in a different post.

-LB

 

[aaronburro]

oh yes, I had trouble with a SQL expression earlier, and the SQL expression necessary would be of the same general type that is currently giving me fits :/ :\

 

[synapsevampire]

I think that you could use a maximum({table.freight},{table.customer}) and return the correct amount.

Although it seems that you should be returning at the invoice level to me...

-k