Need help with a subtotal formula

[knagy]

I am hoping someone will be able to help me. I am working in Crystal Reports 8.5 and have created a Job Cost Report. The report has the following columns:

Closing Current Contract Internal Billed
Budget Estimate CO CO Amount
Site 800,000 850,000 50,000 0 250,000
Const 900,000 901,000 0 1,000 450,000

The Site and Const lines are Group totals, the details are hidden, but shown when you drill down. So a drill down of Site would look like:
Closing Current Contract Internal Billed
Budget Estimate CO CO Amount
02-05 400,000 450,000 50,000 0 150,000
02-06 400,000 400,000 0 0 100,000

My problem lies with the Change Orders (CO). In the Cost Code database, the Approved Change Order field shows a total of all change orders regardless of type. That makes this field useless to me since I need to break out the change orders by type. So I went to the Change Order database. The problem I have in this database is that the change orders are listed individually. For example, if there were 3 change orders that make up the 50,000 of Contract CO's, each one is listed seperatly. There is no subtotal field. So I need to figure out how to separate the change orders by type and only show a total of each type in the details section, which is normally hidden. Is this possible?

 

[synapsevampire]

In general you'll get better results by posting technical information:

Database/connectivity used
Example data (not what the report currently looks like, the data)
Expected output

you state that you need to " how to separate the change orders by type and only show a total of each type in the details section", however you state that the details is a drill down, so that would mean that you need to include the fields in the details.

Perhaps you need another grouping that is a drill down whioch is by the CO type, and then have summaries for each type in there... hard to know from your posting.

-k

 

[knagy]

The output desired is what I have shown. The drill down is for the Cost Codes, not the Change Orders. There may be several change orders that flow into one cost code. The change order information is in JCM_Commitment_CO while the Cost Code informtion is in JCM_Cost_Code. I am able to link the two databases by the Job number. What I am unable to do is group the change orders and display the total in the details section which is based on the cost code.

 

[lbass]

I can't tell what your first column field is "02-05"--is that a date? Or some type of ID? But it looks like a reasonable field to insert an additional group on. Then you would create two formulas like:

//{@ContractCO}:
if {JCM_Commitment_CO.CO} = "Contract" then {JCM_Commitment_CO.Amt}

//{@InternalCo}:
if {JCM_Commitment_CO.CO} = "Internal" then {JCM_Commitment_CO.Amt}

Place these in the detail section and then right click on each and insert a summary. Drag the other fields you want displayed into this new group footer and then suppress the details section and the group header.

You could also potentially use SQL expressions if there is some reason you feel you can't add this grouping, but to help you with that I would have to know more about your fields.

-LB