Adding a sum in the group header

[CABman01]

I have a project to modify a report to include a code that stems from a sum of labor. I need to add the labor for each cabinet plus the accessories added to each cabinet to result in a number that then will be used to to determine a code that will be added to the report in the group header.

Here are examples of the tables:

Cab table
CabID CabLabor
1 100
2 110
3 100
4 40

Accessory table
CabID ALabor
1 5
1 4
1 5
2 5

Here are the codes I was given
C01 = 3
C02 = 1
C03 = 2
C04 = 4
C10 = 100 to 159
C20 = 160 to 499

How would I code this in Crystal 8. I have tried just adding them together, but I get only a blank. Ideas???

Thanks

 

[lbass]

How are the tables linked? Where are the codes coming from? Are they from a table or is this just a guideline for some hardcoding? You haven't explained how the codes relate to any of the shown fields. Are you supposed to add the code, e.g., "C01" if the sum = 3? But then why is there a gap between C4 and C10?

-LB

 

[CABman01]

The tables are linked by the CabID. That is Field for Group Header #1. The code is just that a code. There is nothing between C04 and C10. The codes have some sort of internal meaning to the report reader on the manufacturing floor. Here is what I was looking at for sudo code

For each CabID
LabSum = CAb.CabLabor + Accessory.ALabor
If LabSum = 1 Then
SortOrder = C02
Elseif LabSum = 2 Then
SortOrder = C03
Elseif LabSum = 3 Then
SortOrder = C01
Elseif LabSum = 4 Then
SortOrder = C04
Elseif LabSum > 100 and LabSum < 159 Them
SortOrder = C10
Elseif LabSum > 160 and LabSum < 499 Then
SortOrder = C20
End if

Print out SortOrder in GroupHeader

Next CabID

 

[lbass]

Okay, but this doesn't make a lot of sense to me. Your data will print out like the following, using part of your example:

CABID CABLABOR ALABOR
1 (Grp Header)
1 100 5
1 100 5
1 100 5

2 110 5

So you will need to insert a group on {Cab.CabID} and then insert a maximum on {Cab.Cablabor} and a sum on {Accessory.ALabor}. Then create a formula:

Select (maximum({Cab.Cablabor},Cab.CabID}) + sum({Accessory.ALabor},{Cab.CabID}))
case 1 : C02
case 2 : C03
case 3 : C01
case 4 : C04
case 100 to 159 : C10
case 160 to 499 : C20
default : ""

This will give you the correct designation. I'm concerned about your using the term "sortorder", because you will not be able to sort on this value. Is that your intent?

-LB

 

[CABman01]

LB,

I think it's a language problem that I am having communicating. The SortOrder is name for the code that my customer want's printed out on the report. It's the C01, c02, ..., etc. I don't want to use the sort function in crystal on this code.

The report is for cabinets. Each cabinet has a labor value in the database. It can range from 40 to 110. These values where inserted to the database by the customer. Each cabinet can have from 0 to many accessories attached to it. Each accessory has a labor value from 1 to 4.

I don't understand what you mean in this part:
"So you will need to insert a group on {Cab.CabID} and then insert a maximum on {Cab.Cablabor} and a sum on {Accessory.ALabor}. Then create a formula:"

As I mentioned before the Accessories and Cabinets are listed in seperate tables and linked by CabinetID.

Hope this makes things a little clearer.

 

[lbass]

Create this formula in the formula editor:

Select (maximum({Cab.Cablabor},Cab.CabID}) + sum({Accessory.ALabor},{Cab.CabID}))
case 1 : C02
case 2 : C03
case 3 : C01
case 4 : C04
case 100 to 159 : C10
case 160 to 499 : C20
default : ""

Correct the field names if they aren't quite right, and then drag the formula into the group header.

-LB

 

[CABman01]

Ok ... I'm starting to get something here. I added the word "none" for the default. I have a simple user type question though ... How do I check the values that are being generated. Can I walk the code like in VB?

Here is the formula I created:

Select (maximum({Cabinets.Assembly Labor},{Cabinets.Cabinet ID}) + sum({Accessories.ALabor},{Cabinets.Cabinet ID}))
case 1 : "C02"
case 2 : "C03"
case 3 : "C01"
case 4 : "C04"
case 100 to 159 : "C10"
case 160 to 499 : "C20"
default : "none"

 

[lbass]

Sorry that I forgot the "". I don't know what you mean. If you want to see the value of the summary, then create a formula:

maximum({Cabinets.Assembly Labor},{Cabinets.Cabinet ID}) + sum({Accessories.ALabor},{Cabinets.Cabinet ID}))

Place that in the group header.

-LB

 

[CABman01]

Here is the formula you gave me:

maximum({Cabinets.Assembly Labor},{Cabinets.Cabinet ID}) + sum({Accessories.ALabor},{Cabinets.Cabinet ID})


Here is a sample of the data in the database
Cabinets Table
Cabinet ID Assembly Labor
1 100
2 100

Accessories Table
ACabinet ID ALabor
1 7
2 6
0 7

The tables are linked from Cabinet ID to ACabinet ID
The Group Header creates a new page for every Cabinet ID in the Cabinets table

The formula gives a value of 198 on page 1 of the report and 202 on page 2 of the report.

I need to add the ALabor and Assembly Labor. If there are multiple values of ALabor for each ACabinet ID then I need to added together as well.

I have tried editing the formula like this:

{Cabinets.Assembly Labor} + {Accessories.ALabor}

It gives me a correct value for each Cabinet ID, but it won't add up multiple values of ALabor for each ACabinet ID.

Your help is GREATLY appreciated

 

[lbass]

Instead of showing values by table, please provide some sample data based on putting your fields in the detail section of a new report if necessary so we can see how it repeats, using this layout and doing it for at least two Cabinet IDs:

Cabinet ID Assembly Labor AccessoriesLabor

-LB

 

[CABman01]

Cabinet ID Assembly Labor ALabor
1 100 6
2 100 7
2 100 6

 

[lbass]

Not sure that's a sufficient sample to drive how to develop the summary. In this case wouldn't you expect a result of the following?

CabID Total
1 106
2 113

-LB

 

[CABman01]

That is the result I get from the formula in a new report. However, in the existing report I get the following.

CabID Total
1 198
2 321

 

[lbass]

I have no idea what you mean. Why are you trying this in two different reports? It sounds like the existing report might have more row inflation--maybe because it has an additional table? Or can there ever be an instance where there is a repetition in Cab table caused by another field (Cab.field) so that you have data like this:

Cabinet ID Assembly Labor ALabor Cab.field
1 100 6 x
1 30 6 y

2 100 7 x
2 100 6 x
2 50 7 y
2 50 6 y

-LB




-LB

 

[CABman01]

I tried the code in a new blank report just to get everything right. What's funny/strange is when I add the exact same code in the existing report (it's has a lot of other data that is being pulled later in the report) it gives me a different answer.

There certainly could be repetition. However, I don't think that would cause us trouble here, because of coding this in the new report and getting the right answer.

 

[lbass]

I bet if you check the visual linking expert you will find more tables in the "old" report--which cause record inflation. To determine the necessary formula (or running total elements), you would have to look at the detail level data and identify the pattern of repetition so you can account for it.

-LB

 

[CABman01]

Yep ... that's the ticket, but looking at detail level data and determining the pattern of repitition is beyond you technical capabilities. Ideas??

 

[lbass]

If you want to use the existing complex report, you will need to determine the patterns or you won't be able to do these calculations.

The only other option is to try to add the other tables through subreports so they don't cause row inflation.

-LB