[b]Running totals in Sub-report{/b}

[mfroggie]

I have a sub-report with running totals using a formula to Evaluate based on doctype and never resetting. It resets though in the subreport. If I save the subreport as an individual report and test it the running total works. If I insert the subreport into my main report again it resets on each group again. I don't understand why and I want to have a variable to bring the grand total of each Doctype to the main report. Thanks

Mfroggie

 

[synapsevampire]

You haven't stated where this subreport is within the main report.

Each time the subreport fires, the Running Totals within are reset.

If you want a subreport to return a value for use within the main report, place it in the report header and it will fire just once, and return one value for use throughout the main report.

Try describing your environment:

Crystal version
Database/connectivity used
Example data
Expected output

When using subreports, describe where they are:

rep header
Page header
GH header
Details
GF footer
rep footer
Page footer

-k

 

[mfroggie]

Thank you and sorry about that. The subreport is in group header 3.

CR 7.0
ODBC to Pervasive Database
Trying to create a report that lists transactions by doctype grouping by job, then phase and then doctype so there are 3 groups with a running total for each doctype so that the total of each can be placed on the main report summarizing each. Such as total for all material, total for all labor, total for all 3rd party charges per job.

Mfroggie

 

[lbass]

You say that you want the grand totals of doctype, and yet your last post says you want the totals per job. If you want the subreport to show the grand totals across all jobs, remove the links between the subreport and the main report. If you want the subreport to show the totals per job, only link the subreport by job. In either case, you could still place the report in the doctype group header.

If you also only want to show the running total for the specific doctype, you need to use a shared variable. In the main report, create a formula:

whileprintingrecords;
shared stringvar doctype := {table.doctype}

Insert another Group Header #3 section, and place the shared variable formula in GH#3a, and then suppress GH#3a. Drag the original GH#3 fields and the subreport into GH#3_b.

Within the subreport, use field suppression so that only the doctype total corresponding to the doctype in the main report is displayed. To do this, you would highlight the running total->format->field->common->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar doctype;
doctype <> "labor" //or whatever is the specific doctype the running total is evaluating

Repeat for each running total, changing the value in the suppression formula. You could lay the running totals on top of each other so that they would appear in the same position.

-LB

 

[mfroggie]

The report might print more than 1 job at a time unless they select to print just 1 job. It would keep the jobs grouped together, then phases under each job, then doctype.
For expample

Main Report GH1 Job 1
Main Report GH2 Phase E
<Sub Report
GH2 Material (DocType)
D Item Number Qty Unit Cost Extended Cost
GF2 Total Material (Sum Extended Cost)
GH2Time Cards (DocType)
D Employee Number Qty Hours Labor U/C Extended Cost
GF2 Total Labor Cost (Sum Extended Cost)

GF1 (R/Total of Doctype) Total Phase (Sum Extended Cost)
<End of SubReport
This subreport is placed in the GH 3 of the Main report. If I do this subreport by itself the running total doesn't reset for each phase. When I place it in the main report it now resets at each phase level. Even when I place it in the Report Footer of the SubReport.

I want the running total to give me a total of Doctype per job so if the person running the report wants to print more than one job it won't keep running the total thru all the jobs. But my main problem is the reseting when I place the subreport into the main report. I hope this explains it better.

Mfroggie

 

[lbass]

My suggestion should work. Please give it a try. And if it doesn't, for some reason, please explain the results you get when you try it.

-LB

 

[mfroggie]

This did just what I got before. I had the shared variable on the main report but my problem is the reseting at every phase on the subreport. Even though I have told it not to reset and evaluate by DocType when I insert the subreport it starts reseting but before I inserted it the running total was working fine. So why would it start reseting just because my report has been inserted into a main report. The total carried over to the main report is the total of the last phase on the job not a running total of this doctype thru out all phases. Many thanks

Mfroggie

 

[lbass]

You should only have the subreport linked on Job, not Phase, and the subreport should be placed in the DocType group header of the main report.

Within the subreport, the running totals should be displayed in the report footer, with all other sections suppressed.

I have set up what I think is a parallel report, and this works here.

Maybe you should describe how you have the subreport designed.

-LB

 

[mfroggie]

Subreport has
RH
GH1 Phase (nothing in this)
GH2 Table.DocType - A
Details (Listing of transactions according to DocType)
GF2 SubTotal of DocType
GF1 Total of Phase
RF Total of Job

Main Report
Parameters
GH1 Table.Contract (Job)
GH2 Phase Code Phase Description
GH3 Subreport
Details Hidden
GF3 Empty
GF2 Used for when Parameter is chosen not to print down
to DocType (Otherwise it is suppressed)
GF1a Also used with a Parameter but suppressed otherwise
GF1b Formula for "Total Contract: contract no
RFa
RFb Total number of jobs printed

Mfroggie

 

[lbass]

So, are your running totals in the report footer of the subreport? And how do you have the subreport linked to the main report?

-LB

 

[mfroggie]

I have the running total in the GF1 (Phase total) suppressed so it won't show up thru out and placed in the RF of the subreport to see the total of the running total, just to check it. I have the subreport linked by job, phase and doctype because:

when I link only by job or only by phase I get a report that duplicates continually and it is a couple hundred pages.

when I link by job and phase but no doctype if there is more than one doctype in a phase it duplicates them in the phase and phase total. The only way I could get rid of this was to add the link of the doctype. With all 3 links I get the correct results just not the correct running total. But keep in mind if I go back to the subreport before I insert it I am getting the running total correct.

Thanks again

Mfroggie

 

[lbass]

Perhaps I've misunderstood. I thought the purpose of the subreport was just to display the running total of DocType at the Job level in every DocType GR in the main report. This means the report would look something like this:
Amt DocTypeJobTotal(from SubRT)
Job 1
Phase
Materials 125 300
Labor 200 500
Phase
Materials 175 300
Labor 300 500

Job 2
Phase
Materials 700 800
Labor 50 200
Phase
Materials 100 800
Labor 150 200

This requires ONLY a link on Job. All sections of the subreport should be suppressed except the report footer. The subreport should not be adding any extra pages to your main report, since you are only using the subreport to add one value in an existing group header section.

Are you using the subreport to share data contributing to results in the main report? Maybe you will need two subreports--one to provide the job level rt that I've been making suggestions about, and one to provide the shared values that you need using the multiple links.

-LB

 

[mfroggie]

The user of the report wants to see a list of all transactions charged to the job for each type of transaction ie: timecard, material usage, subcontractor, equipment, etc. Then at the bottom of the report a total of all timecards (labor cost), all material cost, etc. The detail in the subreport has qty of each, unit cost and extended cost so he can see this then total of type of cost and total of phase. If I didn't have to show totals for each cost type I would have it done because I have everything else the end user is asking for. It is just getting the running total to stop reseting. I hope this makes sense. Thanks

Mfroggie

 

[tektipdjango]

I think your main report is not correctly designed regarding your information goal.

It would be better to make something like

GH1 JOB
GH2 DocType
GH3 Phase
with the subreport linked by JOB,Phase,Doctype
GF3 get the DocTypesubTotal of subreport via a shared variable
GF2 get the DoctypeTotal via totalization of this shared variable
GF1 get GrandTotal of the different Doctypes

Am I right?

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[lbass]

Okay, if the issue is that you want to be able to display the total for each DocType in the report footer of the main report, then keep all three links between the subreport and the main report. In the subreport, create shared variable formulas like:

//{@sharedMats}:
whilerpintingrecords;
shared numbervar materials := {#MaterialsAmt};

//{@sharedLabor}:
whilerpintingrecords;
shared numbervar labor := {#laborAmt};

...where these are the subreport report footer running totals. Place these on the subreport canvas instead of the running totals. Repeat for each doctype.

In the main report, create these formulas:

//{@reset} to be placed in GH3a (with the subreport in GH3b):
whileprintingrecords;
shared numbervar materials := 0;
shared numbervar labor := 0; //add in other doctypes

//GH3a can then be suppressed.

//{@grtotaccum} to be placed in GF3:
whileprintingrecords;
shared numbervar materials;
shared numbervar labor;
numbervar grtotmaterials := grtotmaterials + materials;
numbervar grtotlabor := grtotlabor + labor; //add in other doctypes

//{@displgrtotmats} to be placed in the report footer:
whileprintingrecords;
numbervar grtotmaterials;

//{@displgrtotlabor} to be placed in the report footer;
whileprintingrecords;
numbervar grtotlabor;

-LB

 

[mfroggie]

...where these are the subreport report footer running totals. Place these on the subreport canvas instead of the running totals. Repeat for each doctype.

I will try this but it says the where these are the subreport footer running totals. My problem is that the footer running totals are only the last phase total for the particular doctype because once the subreport is inserted into the main report the running total starts reseting at each phase.

Thanks

Mfroggie