Crystal Reports subreport total aggregation

I have a Crystal Reports 8.5 report with 3 total reports (2 are subreports).  The main report is the Direct Labor Dollars report.  The two supreports are Indirect Labor Dollars in report footer a and Apportioned(Overhead) Labor Dollars in report footer b.  Each report returns a specific type of labor dollars for Specific cost centers.  I need to add the specific Cost Center Direct and Indirect Labor Totals and then add the Direct and Indirect Labor Totals and then divide each Cost Center Total into the new aggregated (Direct and Indirect Labor) total to get the ratio used by each cost center.  Those cost center ratios will then be used to divide up the apportioned labor.  How do I add the separate report's specific Cost Center Dollars and Total Dollars?  I've attached the report I have so far.

TIA,

Tim
NashuaTotalLaborDollars.rpt
gibneytAsked:
Who is Participating?
 
mlmccCommented:
Here is an updated version.  I couldn't find a CR 8.5 version to do it in.  I named all the formulas I added starting with an x

It prints a summary on the last page

mlmcc
NashuaTotalLaborDollars-v2008.rpt
0
 
mlmccCommented:
Let me make sure I understand what you are trying to do.

You have direct labor in the main report and indirect labor in the first subreport.
You want (CostCenterA(Direct) + CostCenterA(Indirect)) / (TotalDirect + TotalIndirect)

You then need to use that to determine how much of the costs in the second subreport to apportion to each cost center?

mlmcc
0
 
gibneytAuthor Commented:
Yes, that sounds right.

There may not always be matching cost centers in the Direct and Indirect reports so then the aggregate cost center total will be just the one, whichever exists.

The (aggregate cost center total/aggregate total) ratio needs to be displayed on the report too with the apportion calculation.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mlmccCommented:
Are the numbers in the second report raw in that they haven't been apportioned as yet?

mlmcc
0
 
gibneytAuthor Commented:
That is correct.  I feel certain that the Indirect and Apportioned Indirect are two distinct sets of numbers that do not overlap.  There are separate different calculations for each.  I could be missing something though.  Do you think otherwise?

Tim
0
 
mlmccCommented:
Without knowing more about your data, I would agree.

The problem is you will have to maintain a set of arrays to gather the direct and indirect labor costs then use them in the 2nd subreport to do the calculation.

Is there any chance you would get more than 1000 cost centers?

mlmcc
0
 
gibneytAuthor Commented:
No, not a chance in my lifetime anyways.

Tim
0
 
gibneytAuthor Commented:
So based on current numbers saved with the report here are the calculations for cost center 4017:
(3341.54+12817.10)/(13846.21+15161.22)=.5571
16404.43*.5571=9138.91
3341.54+12817.10+9138.91=25297.55

CC = Cost Center
DL = Direct Labor
IL = Indirect Labor
DLT = Direct Labor Total
ILT = Indirect Labor Total
ALT = Apportioned Labor Total
LR = Labor Ratio
A = Apportion
TL = Total Labor

(CC4017DL+CC4017IL)/(DLT+ILT)=LR
ALT*LR=CC4017A
CC4017DL+CC4017IL+CC4017A=CC4017TL

Tim
0
 
mlmccCommented:
I'll see what I can do to build the appropriate formulas.
I am trying to get a copy of CR8 or CR8.5 so I can make the changes

mlmcc
0
 
gibneytAuthor Commented:
W.O.W.!  Thanks for the crash course!  Works perfectly in 11.5!

In trying to get it to work in 8.5, after copying all the x... formulas from all report's Formula Editors and adding the appropriate fields to the report and running it I get an error:

<Crystal Reports
! A subscript must be between 1 and the size of the array.
OK>

and the Formula Editor dumps into xCalcDirectLabor.  Pressing Check comes up with no errors though.  And now I am stuck in the same error over and over again.  At this point I'll have to End Process on CR.

Either I missed a formula (very possible) or there is an incompatibility between 2008 and 8.5 (maybe a little less possible).

Any suggestions?  I'll try to build the 8.5 report again.

Tim
0
 
mlmccCommented:
If you post the CR8.5 report, I may be able to find it faster than you can.

mlmcc
0
 
gibneytAuthor Commented:
mlmcc,

I missed the single formulas being placed on the report for the two supreports!  And now there is only one error the same as above but the report does not run.

Tim
0
 
gibneytAuthor Commented:
mlmcc,

Thanks so much.  Here it is.

Tim
NashuaTotalLaborDollars--1-.rpt
0
 
mlmccCommented:
It works just fine.

Set the display formulas so they can grow.

mlmcc
0
 
gibneytAuthor Commented:
I set all the formulas in Report Footer e to Can Grow but i still get the same error just once.

I do see the tree of cost centers in the left window.

Tim
0
 
gibneytAuthor Commented:
I found out the the error occurs once if you are not saving on close.  If you attempt to save on close a loop is created with the error.

If you save mid stream and then close CR exits cleanly.

Tim
0
 
mlmccCommented:
I opened the report and set the objects to grow and saw data in all of them.

What error are you getting?

mlmcc

report
0
 
gibneytAuthor Commented:
After the error I get a white empty report that is 10.5 inches long with forward and back buttons grayed.  I see the list of cost centers in the left hand window.

Tim
0
 
mlmccCommented:
Are you trying to drill into the list?

mlmcc
0
 
gibneytAuthor Commented:
I did try the drill in and when I click one of the cost centers I get the same error again.

Tim
0
 
mlmccCommented:
You shouldn't drill.  Just look at page 4 of the report

mlmcc
0
 
gibneytAuthor Commented:
I can't get to page 4.

I fire off the report and it asks for start date and end date.  Upon OK the report opens and the error comes up. OK the error and blank report, grayed forwad/back buttons, and the cost center tree in the left window.

Tim
0
 
mlmccCommented:
What is the error?

SInce I don't run the report I don't see the error

mlmcc
0
 
gibneytAuthor Commented:
Sure,  here it is:

<Crystal Reports
! A subscript must be between 1 and the size of the array.
OK>

Tim
0
 
gibneytAuthor Commented:
mlmcc,

Thanks so very much for the detailed help.  Like I said, "Thanks for the crash course in CR!"  The technology you conveyed inside that report speaks volumes.  I will use ALL of it in many reports to come!

Tim.
0
 
gibneytAuthor Commented:
mlmcc,

I just noticed that the summary on the last page does not sort the Cost Centers.  Would that be done in the code somewhere or should I be able to do that?
NashuaTotalLaborDollars-v2008.rpt
0
 
mlmccCommented:
They should be in the same order as the report.

Try sorting them there.

Other wise you will have to sort them in the formula that builds the output strings.

mlmcc
0
 
gibneytAuthor Commented:
How about totals on the fields in the summary, as a sanity check and easy verification?  I can't use Running Total Field, none of the formulas shoe up to total on.

Tim
0
 
mlmccCommented:
You can compare the totals in the summary to the totals in the subreport group footers.

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.