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

[Former Member]

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

[Former Member]

I am not too sure of the terminology

however if what you want is

Header

Type Extension

details

Labor 100.00

Labor 140.00

Material 50.00

Material 7.00

Labor 10.00

Footer

RT_TotalLabor = 250.00 RT_Material =57.00

then you do not need RT(running totals) or parameters just stright formulas

@labour and @materials

the formulas you already have all all you need to do is summarise as sum and you are done.

it looks like you have tried to over complecate a simple requirement.

[abhilash_kumar]

Hi,

What is the purpose of the parameters here? Is it to filter records based on a particular Job type?

If this is true, group the report on Job Type, create a parameter field and add a record selection formula. This would make sure the report displays data only for the selected Job Type.

Next, create a simple summary for the Job Type and display it on the group footer.

In case you do not want to filter the records and still use Running totals, make sure you set the 'Evaluate' to 'For each record' and the 'Reset' to 'On Change of group'; select the Job type group here.

This will ensure that your RTs display correctly for every group.

- Abhilash

[Former Member]

Your suggestions are well advised...

I've decided that the best approach would be to do this subtotalling via a subreport but I currently have an issue with what I'm doing.

In the subreport, I've created some Formula fields @TotalHours and @TotalMaterial for starters.

I have noticed another report written by someone else who did a similar approach BUT their subreport has a Footer that SUMmarizes the @TotalHours and @TotalMaterial fields. I see the {Sum of @TotalLabor} and {Sum of @TotalMaterial}. This second report provides the correct totals.

Ok, now I get lost from this point... I go to Insert>Summary to select the field to summarize. In the second report that totals correctly, I see the @TotalHours and @TotalMaterial parameter fields listed as selectable items in the Insert Summary dialog box. But in my report I don't. So, IF I could see/access the @TotalHours and @TotalMaterial parameter fields in the Insert Summary dialog box then I'd probably be set. Attempt #1: I have a Detail section in which I've placed the @TotalHours and @TotalMaterial parameter fields but these fields don't display in the Insert>Summary dialog box. Attempt #2: I've removed the references to @TotalHours and @TotalMaterial from the Detail section. Either way, I can't SUMmarize... ;-(

What am I doing incorrectly (or not doing?)

<When I created the subreport I just brought over about 4-5 actual fields from a single table.. the table that has the data I'm working on. I also created a Record Selection criteria to be for Type="Standard" which restricts the selection to the correct records that contain my data to total. I also restricted the selection to be by correct internal KeyID via a Left Outer Join.>

TIA!

Edited by: total1 on Aug 10, 2011 7:47 PM

Edited by: total1 on Aug 10, 2011 7:47 PM

[abhilash_kumar]

Hi,

That's because a Subreport is another report per se and any summaries or running totals won't be available in the Main Report's 'Insert Summary' dialog box.

You said the summaries show up correctly in the subreport isn't it? Declare the variable that calculates the final total as shared. You can then access this variable in the Main report by creating another formula with the same shared variable.

For eg: In the Subreport, one of my total summaries would look this

Some important caveats before you follow this approach though:

1) The shared variable name should be the same in both the reports

2) On the Main report, the formula that holds the shared variable should be placed in a section below the subreport's section. This is because the subreport should execute first and accumulate the total in the variable before it is displayed in the Main report

- Abhilash

[Former Member]

1) Let me clarify things a little. my comments on the totalling was specific to the subreport and not the Main report. All my comments were relating to my unsuccessful attempts in my subreport.

That is, the Insert>Summary comments were related specifically to the my subreport.

2) The summaries in another report's subreport show up correctly but not in mine. That's where I was attempting to find out how to see the Parameter field in the Insert Summary dialog box. Maybe you're saying to share a variable name and that does the trick?

3) I'm lost after your comments of "For eg: In the Subreport, one of my total summaries would look this". You then go into another paragraph. Am I missing something between the sentences/paragraphs?

4) I haven't done a shared variable name yet.. Help me here. Got to be easy!!

A) Isn't there a way to do this via Insert>Summary as well as shared variable name?

B) I didn't follow you on the Insert>Summary in your responses for the subreport.

TIA

[abhilash_kumar]

Hi,

Sorry for all the confusion. I thought a subreport was being used as an aid to calculate the final summaries which inturn need to be brought back to the Main report.

Anyway, getting back to the subreport summaries, you said you're trying/expecting to see 'Parameters' in the Insert Summary box? You'll never see report parameters in the insert summary box; maybe you're just getting confused between parameters and formula fields.

On the report that shows the @totalhours formula in the insert summary box, the @totalhours wouldn't be a 'Print time' formula. You're formula on the other hand could be a Print time formula.

If the @totalhours is in the details section, what are you summarizing it on? I'm sure you'd like to Sum it on some db field or some user-defined summary or some other formula and place it in the footer section.

Could you paste the totalhours formula in the post?

-Abhilash

[Former Member]

Ok...I know what I want to say but I'm not sure I'm saying what I want to say!

My bad on using the term parameters rather than formula. ;-(

Making progess, thanks to you for your help!

Subreport: (I have only one)

Here's the @TotalLabor from the Formula Editor: if trim({Table1.TYPE})="Labor" then {Table1.EXTENSION} else 0.

Same for @TotalMaterial from the Formula Editor: if trim({Table1.TYPE})="Material" then {Table1.EXTENSION} else 0

Both of these are in the Footer section as I'm still attempting to solve the issue of getting a total of my SET of records for Labor and Material (as well as others once I solve the totalling issues).

Relating to your comments on the Insert Summary dialog box, I can't figure out how to add a Formula field like @TotalLabor etc so as to make it available to the Insert Summary dialog box. How do I?

I have another report, which I didn't write, that utilizes the Insert Summary dialog box in its subreport footer (only one footer) with a {Sum of TotalLabor}(Text Box) and another that has {Sum of TotalMaterial}(number). I don't understand these! I can see the respective fields (@TotalLabor) in this subreport's Edit Summary dialog box but I can see the @TotalHours in my Insert Summary dialog box!

These formulas for the working report are: if {Table1.TYPE} = "Labor" //and {Table1.PRINT} = "T"

then

{Table1.EXTENSION}

else

0

and

if {Table1.TYPE} = "Material" //and {Table1.PRINT} = "T"

then

{Table1.EXTENSION}

else

0

So, they are the same. I just can't get my subtotals to work on a SINGLE row..maybe because I can't see or get them into the Insert Summary dialog box like the subreport that is working on a single row also.

Maybe another comment: My totals appear to work in a line-by-line (row-by-row) on the report printout but I don't get the subtotals on the SAME line.

Here's what I see on an output:

Labor 800.00

Material 200.00

(two lines)

I'd like to see

Labor= 800.00 Material= 200.00

(single line and I've added the text also) When I try the running totals on the same field (Table1.Extension) I get the SAME value for all my entries even though I've setup a separate running total field and use a conditional check for "Labor", "Material" etc.

HTH

[abhilash_kumar]

Alright, so are you trying to add the values per detail section depending on the TYPE? I mean consider this layout:

Detail 1 Labor 200

Detail 2 Labor 500

Detail 3 Material 800

Detail 4 Labor 300

Detail 5 Material 600

Report Footer Total Labor : 1000 Material Total : 1400

So, on the Report Footer you get the final Labor and Material totals accumulated from the Details sections.

If this is what you would like to do, the formula would be:

@Total:

Whileprintingrecords;

Numbevar lb;

Numbervar mt

If {Table1.TYPE} = "Labor" then

Lb := lb + {Table1.EXTENSION}

Else if {Table1.TYPE} = "Material" then

mt := mt + {Table1.EXTENSION}

else 0;

Create a separate formula and place it in the report footer. Call it Labor_total:

Whileprintingrecords;

Numbervar lb;

Similarly create another formula for the mt variable and place it on the footer.

-Abhilash

[Former Member]

Uniquely and I don't know why at this time the Formula fields showed up in the Insert Summary dialog box so I have been able to solve the issues relative to this question. I think it has something to do with refreshing/reloading the report....