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
TIA,
Tim
NashuaTotalLaborDollars.rpt
Crystal ReportsDB Reporting ToolsProgramming
Last Comment
Mike McCracken
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.
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
mlmcc
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
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
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
ASKER
No, not a chance in my lifetime anyways.
Tim
Tim
ASKER
So based on current numbers saved with the report here are the calculations for cost center 4017:
(3341.54+12817.10)/(13846.
16404.43*.5571=9138.91
3341.54+12817.10+9138.91=2
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+I
ALT*LR=CC4017A
CC4017DL+CC4017IL+CC4017A=
Tim
(3341.54+12817.10)/(13846.
16404.43*.5571=9138.91
3341.54+12817.10+9138.91=2
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+I
ALT*LR=CC4017A
CC4017DL+CC4017IL+CC4017A=
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
I am trying to get a copy of CR8 or CR8.5 so I can make the changes
mlmcc
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
mlmcc
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
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
ASKER
It works just fine.
Set the display formulas so they can grow.
mlmcc
Set the display formulas so they can grow.
mlmcc
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
I do see the tree of cost centers in the left window.
Tim
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
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
![report]()
What error are you getting?
mlmcc
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
Tim
Are you trying to drill into the list?
mlmcc
mlmcc
ASKER
I did try the drill in and when I click one of the cost centers I get the same error again.
Tim
Tim
You shouldn't drill. Just look at page 4 of the report
mlmcc
mlmcc
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
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
SInce I don't run the report I don't see the error
mlmcc
ASKER
Sure, here it is:
<Crystal Reports
! A subscript must be between 1 and the size of the array.
OK>
Tim
<Crystal Reports
! A subscript must be between 1 and the size of the array.
OK>
Tim
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.
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.
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
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
Try sorting them there.
Other wise you will have to sort them in the formula that builds the output strings.
mlmcc
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
Tim
You can compare the totals in the summary to the totals in the subreport group footers.
mlmcc
mlmcc
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