Subreport & Main Report Calculations

[77walker]

I am running Crystal version 9 and I have a linked subreport with totals in the main that come from shared variables. Now that I have that calculation and it looks good I have realized that some of the my detail items need to have a portion of the dollar amount reduced by the dollar amount in my main report. Here is a sample of my data:


California Paint 11089

38082 1.00 46110 200.00 Original Amount of Invoice
38082 2.00 46110 200.00 Original Amount of Invoice
38082 3.00 49314 11.90 Int/Late Chg/Balance
38082 5.00 49314 13.08 Int/Late Chg/Balance
38082 1.00 49314 205.13 Int/Late Chg/Balance
38082 2.00 49314 225.38 Int/Late Chg/Balance
Total Amount 855.49 455.49 -400.00

I need to line 1 and subtract it from line 5 and come up with a new total for line 5. The problem is line 1 comes from the main report and line 5 is from the subreport. Is there a way for me to do that type of calculation within Crystal?

If anyone can help me it would be greatly appreciated.

Thank you.

LW

 

[lbass]

You need to identify any groups, label your fields, identify the main report sections the fields are located in, identify the main report section containing the subreport. Don't know what you mean by line 1 and 5 either, whether it means the number in column 2 or whether you are connecting the values of 1 in each of two rows in column 2.

Yes, this can be done, but we don't have enough info.

-LB

 

[77walker]

In the main report I have one group being displayed CustID.
In the detail of the main is Item ID, Line Seq Num, Account, Amount and Aging Amount. I am calcualting the Total Amount from the Amount in main and TotAmt in my Subreport. My Subreport is grouped by CustID and contains ItemID, Item Line#, Account, and BalAmt. I am pulling the same data from different tables because of the way our process changed when we upgraded our system. The BalAmt in my subreport actually contains the total balance on any particular item. The total amount in my Main report contains the total origina dollar amt associated to the item by a revenue account #(without any interest charges). I am trying to get the total dollars by revenue account that will need to be written off. But the subreport is only associating my item by a receivable account and is the total outstanding balance.

California Paint 11089
ItemID Line# Acct Tot Amt Desc of Charges
L#1 38082 1.00 46110 200.00 Original Amount of Invoice
L#2 38082 2.00 46110 200.00 Original Amount of Invoice
L#3 38082 3.00 49314 11.90 Int/Late Chg/Balance
L#4 38082 5.00 49314 13.08 Int/Late Chg/Balance
L#5 38082 1.00 49314 205.13 Int/Late Chg/Balance
L#6 38082 2.00 49314 225.38 Int/Late Chg/Balance
Total Amount 855.49 Aging Amt455.49 Diff-400.00

So I need L#1 to be subtracted out L#5 to give me only the balance of 5.13 which is actually a late charge that has been assessed. L#1 contains the original 200.00 charge. I can connect the two based upon the Line# being the same. But I'm not sure how to achieve this with the subreport being part of the mix. Any help would be appreciated.

Thanks,
LW

 

[lbass]

So where is the sub located and which lines are coming from the sub (3 to 6?)?

-LB

 

[77walker]

The sub is located in GF1 and yes lines 3-6 are from the sub.

 

[lbass]

Just to clarify, do you also need to subtract line2 from line6? In other words, do the amounts in the main report always have to reduce some value in the sub?

-LB

 

[77walker]

Not always usually if the line #'s match.

 

[lbass]

Well, then you need to supply the rules for when you do do it. I can't quite work "usually" into a solution.

-LB

 

[77walker]

I am going to have to add a couple more fields in so I can give that criteria. Let me modify it first.

 

[77walker]

Yes, it only when the line #'s match.

 

[lbass]

Create these formulas in your main report:

//{@reset} for the group header:
whileprintingrecords;
shared numbervar sumres := 0;

//{@amt} to be placed in the detail section:
whileprintingrecords;
shared numbervar array amt;
shared numbervar array id;
numbervar i := i + 1;

if i < 1000 then (
redim preserve amt;
redim preserve id;
id := {table.lineseqno};
amt := {table.amount};
);

//{@displgrptot} to be placed in an inserted Group Footer_b section (below the section containing the sub):
whileprintingrecords;
shared numbervar sumres;
sumres + sum({table.amount},{table.custID});

In the subreport, create this formula and place it in the detail section:

//{@subtot}:
whileprintingrecords;
shared numbervar array amt;
shared numbervar array id;
numbervar i;
numbervar j := ubound(amt);
shared numbervar result;
shared numbervar sumres;

for i := 1 to j do(
if {table.lineseqno} = id then
result := {table.subamt} - amt //not sure the name of the amount field in the sub, so called {table.subamt}
);
sumres := sumres + result; //accumulates for the group total.
result //shows current result.

-LB

 

[77walker]

The Line# columns need to match. The calculation worked great on those that matched, but for all those Line#'s that didn't match everything was zero'd out or negative value. The only calculations I need to work are Am-As and Bm-Bs. Can it be done?

California Paint 11089
ItemID Line# Acct Tot Amt Desc of Charges
Am 38082 1.00 46110 200.00 Original Amount of Invoice
Bm 38082 2.00 46110 200.00 Original Amount of Invoice
38082 3.00 49314 11.90 Int/Late Chg/Balance
38082 5.00 49314 13.08 Int/Late Chg/Balance
As 38082 1.00 49314 205.13 Int/Late Chg/Balance
Bs 38082 2.00 49314 225.38 Int/Late Chg/Balance
Total Amount 855.49 Aging Amt455.49 Diff-400.00

So what I ended up with after applying the formula's was:

California Paint 11089
ItemID Line# Acct Tot Amt Desc of Charges
Am 38082 1.00 46110 200.00 Original Amount of Invoice
Bm 38082 2.00 46110 200.00 Original Amount of Invoice
38082 3.00 49314 -97.00 Int/Late Chg/Balance
38082 5.00 49314 -97.00 Int/Late Chg/Balance
As 38082 1.00 49314 5.13 Int/Late Chg/Balance
Bs 38082 2.00 49314 25.38 Int/Late Chg/Balance
Total Amount -8702.24 Aging Amt455.49 Diff-8246.75

 

[lbass]

It would have helped to know the field name, which I will just call {table.asbs}. Change the subreport formula to:

//{@subtot}:
whileprintingrecords;
shared numbervar array amt;
shared numbervar array id;
numbervar i;
numbervar j := ubound(amt);
shared numbervar result;
shared numbervar sumres;

if {table.asbs} in ["As","Bs"] then(
for i := 1 to j do(
if {table.lineseqno} = id then
result := {table.subamt} - amt;
));
if isnull({table.asbs}) or
trim({table.asbs}) = "" or
not({table.asbs} in ["As","Bs"]) then
result := {table.subamt};
sumres := sumres + result; //accumulates for the group total.
result //shows current result.

-LB

 

[77walker]

The name of the field that I want to match on is {O_AR_WRITEOFF_SUM_ITEM_DST.ITEM_LINE}

 

[77walker]

I'm not sure what {table.asbs} is supposed to represent. My db fields that I am using in the queries are:

My Main Report consists of:
TABLENAME = O_AR_WRITEOFF_SUM_BI_DST
GH1 - CUSTID, NAME1
Detail - INVOICE, LINE_SEQ_NUM, ACCOUNT, AMOUNT, AGING_AMT

My Sub Report consists of:
TABLENAME = O_AR_WRITEOFF_SUM_ITEM_DST
GH1 - CUSTID, NAME1
Detail - ITEM, ITEM_LINE, ACCOUNT, BAL_AMT

If line_seq_num = item_line then TotAmt := BAL_AMT-AMOUNT

I'm sorry if I haven't been clear, I'm just not sure how to do this with the subreport in the mix.

 

[lbass]

Are you unsure where to plug this in or something? If it is what you labeled "Line #" in your sample, then replace {table.lineseqno} with it.

-LB

 

[77walker]

No, {table.lineseqno} is pretty explanatory, it's {table.AsBs} that I am unsure of.

 

[lbass]

Whatever is the name of the field that can have results like As, Bs, Am, Bm. THAT field.

-LB

 

[77walker]

As" "Bs" and "Am" "Bm" are not real values they were used to represent the record coming from line "As"- subreport and line "Am"- main report. They could have as easily been labeled "*" and "**".

 

[lbass]

Wow, that really wasn't clear.

//{@subtot}:
whileprintingrecords;
shared numbervar array amt;
shared numbervar array id;
numbervar i;
numbervar j := ubound(amt);
shared numbervar result;
shared numbervar sumres;

for i := 1 to j do(
if {table.lineseqno} <> id then
result := {table.subamt};
);
for i := 1 to j do(
if {table.lineseqno} = id then
result := {table.subamt} - amt;
);
sumres := sumres + result; //accumulates for the group total.
result //shows current result.

-LB