Link to home
Create AccountLog in
Avatar of gibneyt
gibneyt

asked on

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
Avatar of Mike McCracken
Mike McCracken

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
Avatar of gibneyt

ASKER

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.
Are the numbers in the second report raw in that they haven't been apportioned as yet?

mlmcc
Avatar of gibneyt

ASKER

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
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
Avatar of gibneyt

ASKER

No, not a chance in my lifetime anyways.

Tim
Avatar of gibneyt

ASKER

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
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
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of gibneyt

ASKER

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
If you post the CR8.5 report, I may be able to find it faster than you can.

mlmcc
Avatar of gibneyt

ASKER

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
Avatar of gibneyt

ASKER

mlmcc,

Thanks so much.  Here it is.

Tim
NashuaTotalLaborDollars--1-.rpt
It works just fine.

Set the display formulas so they can grow.

mlmcc
Avatar of gibneyt

ASKER

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
Avatar of gibneyt

ASKER

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
I opened the report and set the objects to grow and saw data in all of them.

What error are you getting?

mlmcc

User generated image
Avatar of gibneyt

ASKER

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
Are you trying to drill into the list?

mlmcc
Avatar of gibneyt

ASKER

I did try the drill in and when I click one of the cost centers I get the same error again.

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

mlmcc
Avatar of gibneyt

ASKER

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
What is the error?

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

mlmcc
Avatar of gibneyt

ASKER

Sure,  here it is:

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

Tim
Avatar of gibneyt

ASKER

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.
Avatar of gibneyt

ASKER

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
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
Avatar of gibneyt

ASKER

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
You can compare the totals in the summary to the totals in the subreport group footers.

mlmcc