Detail Section:Printing subtotals of group of data by row w/ no subreports

208 Views

CR Xi

Tables: Opportunity (fields Opportunity.OpportunityID)

Opportunity_Job

Selection (fields Type, Quantity, Rate and Extension)

A) Opportunity:Opportunity_Job are 1:1; Opportunity.Job:Selection are 1:Many (Hence a change in OpportunityID changes the focus to another set of Selection table entries associated with the OpportunityID.)

B) I have setup a Left Outer Join from the Selection table to the Opportunity table via OpportunityID for a Link Type of "=" (I eliminate the Opportunity_Job table for the purposes of the report)

C) This is a report without any subreports

D) Details section of CR

I have a table (Selection) that has several fields. The Selection table contains all the entries for a job that deals with labor, material, equipment etc.

I'd like to subtotal all the entries in the Selection table by Job.

Here are the fields I have that are of interest:

Type (Labor, Material, etc.)

Extension

For example, data could look like this

Type Extension

Labor 100.00

Labor 140.00

Material 50.00

Material 7.00

Labor 10.00

My RT_TotalLabor field should then provide 250.00 and the RT_Material field should provide 57.00

If have setup a parameter field (@TotalLabor) and a RunningTotals field (RT_TotalLabor)

The conditional logic for @TotalLabor is "IF trim({Selection.Type})="Labor" then {Selection.Extension} else 0". I've also tried "IF trim({Selection.Type})="Labor" then SUM({Selection.Extension}) else 0". The RT_TotalLabor is setup to summarize on @TotalLabor via SUM and to evaluate on Opportunity.OpportunityID and reset on change of field of Opportunity.OpportunityID.

I'm not getting the proper selection of data.

How do I select the Labor only for the TotalLabor parameter field and get the correct RT_TotalLabor? I will also need to have a field, @TotalMaterial, with a RT_TotalMaterial to SUM the materials related to the Job. Additoinally, these fields will be subtotalled and gross totalled but I should be able to handle that once the totals by Opportunity is done.

TIA

Edited by: total1 on Aug 8, 2011 9:58 PM