How do I summarizes DETAILS Section in Group Footer 2

[fabianor]

I'm using Crystal 8.5

I need to summarizes the Detail section of my report in my Group Footer for all the Batches Tenders that closes.

Summary data for batch(es) that closed on: @DateClosed

I have a DEF File as Follows and I have given report breakdown below.

I have a little knowledge if Crystal Reports


Filename = REGANALY.RPT
Description = Register Analysis Report
HelpContextID = 0

DefaultSelectionFormula = {@DateClosed} = Today
DefaultSelectionDescription = Date Closed = Today

GroupCount = 2
StartGroup = 1

TableCount = 5

VisibleDescription = Show Individual Batches
VisibleSectionName = DETAIL
VisibleSectionName = GH2
VisibleSectionName = GH1
VisibleSectionName = GF2

; Description Field Name Type Level Crit. G.T. #
; --------------- ----------------------------- ---- ----- ------ ------
FIELD = Date Closed, {@DateClosed}, 2, 1, -1, 0
FIELD = Date Opened, {@DateOpen}, 2, 0, -1, 0

FIELD = Register Number, {Register.Number}, 1, 0, -1, 0
FIELD = Total Tendered, {Batch.TotalTendered}, 1, 0, -1, 0
FIELD = Total Change, {Batch.TotalChange}, 1, 0, -1, 0
FIELD = Discounts, {Batch.Discounts}, 1, 0, -1, 0
FIELD = Cost of Goods, {Batch.CostOfGoods}, 1, 0, -1, 0
FIELD = Commission, {Batch.Commission}, 1, 0, -1, 0
FIELD = Customer Count, {Batch.CustomerCount}, 1, 0, -1, 0
FIELD = No Sales Count, {Batch.NoSalesCount}, 1, 0, -1, 0
FIELD = Aborted Trans. Count, {Batch.AbortedTransCount}, 1, 0, -1, 0
FIELD = Opening Total, {Batch.OpeningTotal}, 1, 0, -1, 0
FIELD = Sales, {Batch.Sales}, 1, 0, -1, 0
FIELD = Returns, {Batch.Returns}, 1, 0, -1, 0
FIELD = Tax, {Batch.Tax}, 1, 0, -1, 0
FIELD = Shipping, {Batch.Shipping}, 1, 0, -1, 0
FIELD = Debit Surcharge, {Batch.DebitSurcharge}, 1, 0, -1, 0
FIELD = Cash Back Fee, {Batch.CashBackSurcharge}, 1, 0, -1, 0
FIELD = Paid On Layaway, {Batch.LayawayPaid}, 1, 0, -1, 0
FIELD = Paid To Account, {Batch.PaidToAccount}, 1, 0, -1, 0
FIELD = Deposit Made, {Batch.CustomerDepositMade}, 1, 0, -1, 0
FIELD = Total 1, {@Total1}, 1, 0, -1, 0
FIELD = Paid Out, {Batch.PaidOut}, 1, 0, -1, 0
FIELD = Dropped, {Batch.Dropped}, 1, 0, -1, 0
FIELD = Layaway Closed, {Batch.LayawayClosed}, 1, 0, -1, 0
FIELD = Paid On Account, {Batch.PaidOnAccount}, 1, 0, -1, 0
FIELD = Deposit Redeemed, {Batch.CustomerDepositRedeemed},1, 0, -1, 0
FIELD = Closing Total, {Batch.ClosingTotal}, 1, 0, -1, 0
FIELD = Total 2, {@Total2}, 1, 0, -1, 0
FIELD = Over / Short, {@OverShort}, 1, 0, -1, 0

This is my Crystal Report Breakdown

Report Header
___________________________________________________________
Page Header A

Consolidated Register Analysis Report

Sort Order: @Sort Order
Selection Criteria: @Selection Criteria
___________________________________________________________
Page Header B
___________________________________________________________
Group Header # 1 @DateClosed () - A

List of batches that closed on: @DateClosed

@Initialize
WhilePrintingRecords;

CurrencyVar TotalTendered :=0;
CurrencyVar TotalChange := 0;
CurrencyVar Discounts := 0;
CurrencyVar CostOfGoods := 0;
CurrencyVar Commission := 0;
NumberVar CustomerCount := 0;
NumberVar NoSales := 0;
NumberVar AbortedTrans := 0;
CurrencyVar OpeningTotal := 0;
CurrencyVar Sales := 0;
CurrencyVar Returns := 0;
CurrencyVar Tax := 0;
CurrencyVar Shipping := 0;
CurrencyVar DebitSurcharge := 0;
CurrencyVar CashBackSurcharge := 0;
CurrencyVar PaidOnLayaway := 0;
CurrencyVar PaidToAccount := 0;
CurrencyVar DepositMade := 0;
CurrencyVar PaidOut := 0;
CurrencyVar Dropped := 0;
CurrencyVar LayawayClosed := 0;
CurrencyVar PaidOnAccount := 0;
CurrencyVar DepositRedeemed := 0;
CurrencyVar ClosingTotal := 0;
CurrencyVar Total1 := 0;
CurrencyVar Total2 := 0;
CurrencyVar OverShort := 0;
StringVar Batches := "";
___________________________________________________________
Group Header # 2 Batch.BatchNumber () – A

Batch Number: Batch.Batch Number
Register Opened: Batch.OpeningTime
Register: Register.Number
Register Closed: Batch.ClosingTime
Total Tendered: Batch.TotalTendered
Opening Total: Batch.OpeningTotal
Paid Out: Batch.PaidOut
Total Change: Batch.Total Change
Sales: Batch. Sales
Dropped: Batch. Dropped
Discounts:Batch. Discounts
Returns: Batch. Returns
Layaway Closed: Batch.LayawayClosed
Cost of Goods: Batch. CostofGoods
Tax: Batch.Tax
Paid On Account: Batch. Paid On Account
Commission: Batch. Commission
Shipping: Batch. Shipping
Deposit Redeemed: Batch.CustomerDepositRedeemed
Customer Count: Batch.Custome Count
Debit Surcharge Batch. Debit Surcharge
Closing Total: Batch. Closing Total:
No Sales: Batch.NoSales
Cash Back Fee Batch. Cash Back Fee
Total: @Total2
Aborted Trans.: Batch. AbortedTrans
Paid On Layaway:Batch. Paid On Layaway
Paid To Account: Batch. Paid To Account
Over / Short: @OverShort
Deposit Made: Batch. Deposit Made
Total: @Total1

@Iterate

WhilePrintingRecords;

CurrencyVar TotalTendered;
CurrencyVar TotalChange;
CurrencyVar Discounts;
CurrencyVar CostOfGoods;
CurrencyVar Commission;
NumberVar CustomerCount;
NumberVar NoSales;
NumberVar AbortedTrans;
CurrencyVar OpeningTotal;
CurrencyVar Sales;
CurrencyVar Returns;
CurrencyVar Tax;
CurrencyVar Shipping;
CurrencyVar DebitSurcharge;
CurrencyVar CashBackSurcharge;
CurrencyVar PaidOnLayaway;
CurrencyVar PaidToAccount;
CurrencyVar DepositMade;
CurrencyVar PaidOut;
CurrencyVar Dropped;
CurrencyVar LayawayClosed;
CurrencyVar PaidOnAccount;
CurrencyVar DepositRedeemed;
CurrencyVar ClosingTotal;
CurrencyVar Total1;
CurrencyVar Total2;
CurrencyVar OverShort;
StringVar Batches;


TotalTendered := TotalTendered + {Batch.TotalTendered};
TotalChange := TotalChange + {Batch.TotalChange};
Discounts := Discounts + {Batch.Discounts};
CostOfGoods := CostOfGoods + {Batch. CostOfGoods};
Commission := Commission + {Batch. Commission};
CustomerCount := CustomerCount + {Batch.CustomerCount};
NoSales := NoSales + {Batch.NoSalesCount};
AbortedTrans := AbortedTrans + {Batch.AbortedTransCount};
OpeningTotal := OpeningTotal + {Batch.OpeningTotal};
Sales := Sales + {Batch.Sales};
Returns := Returns + {Batch.Returns};
Tax := Tax + {Batch.Tax};
Shipping := Shipping + {Batch.Shipping};
DebitSurcharge := DebitSurcharge + {Batch.DebitSurcharge};
CashBackSurcharge := CashBackSurcharge + {Batch.CashBackSurcharge};
PaidOnLayaway := PaidOnLayaway + {Batch.LayawayPaid};
PaidToAccount := PaidToAccount + {Batch.PaidToAccount};
DepositMade := DepositMade + {Batch.CustomerDepositMade};
PaidOut := PaidOut + {Batch.PaidOut};
Dropped := Dropped + {Batch.Dropped};
LayawayClosed := LayawayClosed + {Batch.LayawayClosed};
PaidOnAccount := PaidOnAccount + {Batch.PaidOnAccount};
DepositRedeemed := DepositRedeemed + {Batch.CustomerDepositRedeemed};
ClosingTotal := ClosingTotal + {Batch.ClosingTotal};

Total1 := OpeningTotal + Sales + Returns + Tax + Shipping + DebitSurcharge + CashBackSurcharge + PaidOnLayaway + PaidToAccount + DepositMade;
Total2 := PaidOut + Dropped + LayawayClosed + PaidOnAccount + DepositRedeemed + ClosingTotal;

OverShort := Total2 - Total1;

IF Length(Batches) = 0 THEN
Batches := ToText({Batch.BatchNumber}, 0)
ELSE
Batches := Batches + ", " + ToText({Batch.BatchNumber}, 0)



Tender Type Count Open Shift Close Over / Short
___________________________________________________________
Details

Tender.Description
TenderTotals.Count
TenderTotals.Open
TenderTotals.Shift
TenderTotals.Close
@TenderOverShort @TenderMessage
___________________________________________________________
Group Footer # 2 Batch.BatchNumber () – A

___________________________________________________________
Group Footer # 1 @DateClosed () – A

Summary data for batch(es) that closed on: @DateClosed
Batch Number(s): @ShowBatches


Total Tendered: @showTotalTendered
Opening Total: @showOpeningTotal
Paid Out: @showPaid Out:
Total Change: @showTotal Change
Sales: @showSales
Dropped: @showDropped:
Discounts: @showDiscounts
Returns: @showReturns
Layaway Closed: @showLayawayClosed
Cost of Goods: @showCostofGoods
Tax: @showTax
Paid On AccountshowPaidOnAccount
Commission: @showCommission
Shipping: @showShipping
Deposit RedeemedshowDepositRedeemed
Customer Count: @showCustomerCount
Debit Surcharge @showDebitSurcharge
Closing Total: @showClosingTotal
No Sales: @showNoSales
Cash Back Fee @showCash Back Fee
Total: @showTotal2
Aborted Trans.: @showAbortedTrans
Paid On LayawayshowPaid On Layaway
Paid To AccountshowPaidToAccount
Over / Short: @showOverShort
Deposit Made: @showDepositMade
Total: @showTotal1

WhilePrintingRecords;
CurrencyVar TotalTendered;
TotalTendered

WhilePrintingRecords;
CurrencyVar TotalChange;
TotalChange

WhilePrintingRecords;
CurrencyVar Discounts;
Discounts

WhilePrintingRecords;
CurrencyVar CostOfGoods;
CostOfGoods

WhilePrintingRecords;
CurrencyVar Commission;
Commission

WhilePrintingRecords;
NumberVar CustomerCount;
CustomerCount

WhilePrintingRecords;
NumberVar NoSales;
NoSales

WhilePrintingRecords;
NumberVar AbortedTrans;
AbortedTrans

WhilePrintingRecords;
CurrencyVar OpeningTotal;
OpeningTotal


WhilePrintingRecords;
CurrencyVar Sales;
Sales


WhilePrintingRecords;
CurrencyVar Returns;
Returns

WhilePrintingRecords;
CurrencyVar Tax;
Tax

WhilePrintingRecords;
CurrencyVar Shipping;
Shipping

WhilePrintingRecords;
CurrencyVar DebitSurcharge;
DebitSurcharge

WhilePrintingRecords;
CurrencyVar CashBackSurcharge;
CashBackSurcharge

WhilePrintingRecords;
CurrencyVar PaidOnLayaway;
PaidOnLayaway

WhilePrintingRecords;
CurrencyVar PaidOnLayaway;
PaidOnLayaway

WhilePrintingRecords;
CurrencyVar DepositMade;
DepositMade

WhilePrintingRecords;
CurrencyVar Total1;
Total1

WhilePrintingRecords;
CurrencyVar PaidOut;
PaidOut

WhilePrintingRecords;
CurrencyVar Dropped;
Dropped

WhilePrintingRecords;
CurrencyVar LayawayClosed;
LayawayClosed

WhilePrintingRecords;
CurrencyVar PaidOnAccount;
PaidOnAccount

WhilePrintingRecords;
CurrencyVar DepositRedeemed;
DepositRedeemed

WhilePrintingRecords;
CurrencyVar ClosingTotal;
ClosingTotal

WhilePrintingRecords;
CurrencyVar Total2;
Total2


WhilePrintingRecords;
CurrencyVar OverShort;
OverShort

 

[Madawc]

If you want a total of batches closing for a particular date, try a crosstab.

If you want a quick list of the batches, a subreport in the report footer could do it. Wasteful of machine time, but easy to do.

If it's something else, please give a small sample of what you want.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

First, you should try right clicking on a detail field->insert summary->choose the group level you wish. If the results are inflated, then you could insert running totals instead of using variables. While you can use variables (and in some cases must), CR provides simpler methods for you to use.

-LB

 

[fabianor]

Did not get results that I was hoping to get.

Example of the detail section of report below.

Basically I need to get the details of all the Tender Types Count Open Close Shift Close OverShort for all the batches that closed {@DateClosed} summarized in my footer With all the other summarized items.

Batch1
Tender Type Count Open Shift Close Over / Short
CASH 1 $50.13 $250.00 $292.88 -$7.25 Short
GIFT CARD 0 $0.00 $0.00 $0.00 $0.00 Even
MASTERCARD 0 $0.00 $0.00 $0.00 $0.00 Even
DEBIT 1 $0.00 $103.49 $107.49 $4.00 Over
VISA 0 $0.00 $0.00 $0.00 $0.00 Even
AMEX 0 $0.00 $0.00 $0.00 $0.00 Even
JCB 0 $0.00 $0.00 $0.00 $0.00 Even
PAYPAL US0 $0.00 $0.00 $0.00 $0.00 Even
CND CHQ/0 $0.00 $0.00 $0.00 $0.00 Even
US CASH 0 $0.00 $0.00 $0.00 $0.00 Even
PAYPAL 0 $0.00 $0.00 $0.00 $0.00 Even
US CHQ/ 0 $0.00 $0.00 $0.00 $0.00 Even

Batch2
Tender Type Count Open Shift Close Over / Short
CASH 0 $0.00 $0.00 $0.00 $0.00 Even
GIFT CARD0 $0.00 $0.00 $0.00 $0.00 Even
MASTERCARD2 $0.00 $604.58 $604.58 $0.00 Even
DEBIT 0 $0.00 $0.00 $0.00 $0.00 Even
VISA 5 $0.00 $1,987.56 1,987.56 $0.00 Even
AMEX 0 $0.00 $0.00 $0.00 $0.00 Even
JCB 0 $0.00 $0.00 $0.00 $0.00 Even
PAYPAL US0 $0.00 $0.00 $0.00 $0.00 Even
CND CHQ/0 $0.00 $0.00 $0.00 $0.00 Even
US CASH 0 $0.00 $0.00 $0.00 $0.00 Even
PAYPAL 0 $0.00 $0.00 $0.00 $0.00 Even
US CHQ/0 $0.00 $0.00 $0.00 $0.00 Even

Batch3
Tender Type Count Open Shift Close Over / Short
CASH 16 $101.97 $341.75 $429.48 -$14.24 Short
GIFT CARD1 $0.00 $50.00 $0.00 -$50.00 Short
MASTERCARD4 $0.00 $251.91 280.65 $28.74 Over
DEBIT 8 $0.00 $511.54 $477.78 -$33.76 Short
VISA 9 $0.00 $1,188.92$1,160.18 -$28.74 Short
AMEX 0 $0.00 $0.00 $0.00 $0.00 Even
JCB 0 $0.00 $0.00 $0.00 $0.00 Even
PAYPAL US0 $0.00 $0.00 $0.00 $0.00 Even
CND CHQ/ 1 $0.00 $509.39 $509.39 $0.00 Even
US CASH 1 $0.00 $56.69 $56.40 -$0.29 Short
PAYPAL 0 $0.00 $0.00 $0.00 $0.00 Even
US CHQ/ 0 $0.00 $0.00 $0.00 $0.00 Even

LOOKING TO GET THIS RESULT

Batches 1,2,3
Tender Type
CASH 17 $152.10 $591.75 $722.36 -$21.49 Short
GIFT CARD 1 $0.00 $50.00 $- -$50.00 Short
MASTERCARD6 $0.00 $856.49 $885.23 $28.74 Over
DEBIT 9 $0.00 $615.03 $585.27 -$29.76 Short
VISA 14 $0.00 $3,176.48 $3,147.74 -$28.74 Short
AMEX 0 $0.00 $0.00 $0.00 $0.00 Even
JCB 0 $0.00 $0.00 $0.00 $0.00 Even
PAYPAL US0 $0.00 $0.00 $0.00 $0.00 Even
CND CHQ/1 $0.00 $509.39 $509.39 $0.00 Even
US CASH 1 $0.00 $56.69 $56.40 -$0.29 Short
PAYPAL 0 $0.00 $0.00 $0.00 $0.00 Even
US CHQ/ 0 $0.00 $0.00 $0.00 $0.00 Even

 

[lbass]

You didn't explain why the results weren't what you wanted. Were the summary results incorrect at the batch group level when you inserted summaries on the detail fields? If so, then you would need to insert running totals where you evaluate on change of some field (to eliminate repeating records), reset on change of group (batch for the batch group footer, and date closed for that group footer).

You've provided a lot of info about your report, but what would really help is to see a small sample of detail level data and an explanation of the results you got when you tried inserting summaries and/or running totals. The running totals must be placed in group footers, by the way.

-LB

 

[fabianor]

The summary results at the batch group level when I inserted summaries on the detail fields basically adds the totals as one Grand total for (Count, Open, Shift, Close and Over / Short) and does not give me the combine total for the individual Tender Types.

I Basicly want to get the consolidated totals for example if 3 batches all closed on the same date.

I want to get the total count of MasterCard Slips, the opening totals for all MasterCards, the a total amount of MasterCard that was received during a shift, the total of the MasterCard at the end of day for the close and whether it was over / short.

I m getting the results I need for the individual portin of the report but I want the Tender type totals to add together for the overall close for the day.

I have given the details of the DETAIL Section and an example of 3 batches at a close and the summary that I want to try to get as my results in the Group footer.

Guess it is hard to explain without show an actual copy of the report with data in it. Is there an area on this site to post files for one to view it?

___________________________________________________________
DETAIL Section
___________________________________________________________
Description Tender.Description
Count TenderTotals.Count
Open TenderTotals.Open
Shift TenderTotals.Shift
Close TenderTotals.Close

@TenderOverShort {TenderTotals.Close} - ({TenderTotals.Open} + {TenderTotals.Shift})

@TenderMessage

IF {@TenderOverShort} < 0 THEN
"Short"
ELSE IF {@TenderOverShort} > 0 THEN
"Over"
ELSE
"Even"

Example of Return

Batch 1

TenderType Count Open Shift Close Over/Short
CASH 1 $50.13 $250.00 $292.88 -$7.25 Short
M/C 0 $0.00 $0.00 $0.00 $0.00 Even
DEBIT 1 $0.00 $103.49 $107.49 $4.00 Over

Batch 2

TenderType Count Open Shift Close Over/Short
CASH 0 $0.00 $0.00 $0.00 $0.00 Even
MASTERCARD 2 $0.00 $604.58 $604.58 $0.00 Even
DEBIT 0 $0.00 $0.00 $0.00 $0.00 Even

Batch 3

TenderType Count Open Shift Close Over/Short
CASH 16 $101.97 $341.75 $429.48 -$14.24 Short
M/C 4 $0.00 $251.91 280.65 $28.74 Over
DEBIT 8 $0.00 $511.54 $477.78 -$33.76 Short

RESULTS I’m LOOKING TO GET (CONSOLIDATED TOTALS)

Batches 1+2+3

TenderType Count Open Shift Close ver/Short
CASH 17 $152.10 $591.75 $722.36 -$21.49 Short
M/C 6 $0.00 $856.49 $885.23 $28.74 Over
DEBIT 9 $0.00 $615.03 $585.27 -$29.76 Short

 

[lbass]

I see what you are trying to do, but you did not answer my question. ARE the batch totals correct at the batch group level when you insert summaries? That was also why I wanted to see sample data at the detail level--to see if you have repeating values.

Anyway, if the summaries at the batch level are correct, you could insert a crosstab in the date closed group footer where you add tender type as the row and the summaries as the summary fields. You could go to the customize style tab and check "horizontal display of summaries" and "show summary labels". The only issue would be your over/short formula, which I think should be the following at the batch level:

if sum({@TenderOverShort},{table.batch}) < 0 THEN
"Short"
ELSE sum({@TenderOverShort},{table.batch}) > 0 THEN
"Over"
ELSE
"Even"

If you are willing to eliminate the message, you could use the crosstab and then conditionally color the rows based on whether it meets your criteria, and then add a text box with a key indicating that "red" means "short", etc.

Otherwise, you could use running totals, which are more work. Go to the field explorer->running total->new->for {#OpenCash} you would select {tender.totalsopen}, sum, evaluate based on a formula:

{tender.tendertype} = "Cash"

Reset on change of group ({@dateclosed}). Place this in your group footer for {@dateclosed}. You would need 12 separate running totals, one for each combination of tendertype and summary. Then create 3 formulas for the message, changing the running totals to the ones for specific tendertypes, e.g.,:

if {#closecash}-({#opencash}+ {#shiftcash}) < 0 then "Short" else
if {#closecash}-({#opencash}+ {#shiftcash}) > 0 then
"Over" else "Even"

Place these in the group footer, too, along with text boxes to identify the summaries, tender types.

-LB

 

[fabianor]

I inserted some crosstabs in the date closed group footer and got the results that I needed.

Thanks for your help.

I have a few other things that I would like to do with this report. Should I start an new thread?

Also is there not a file posting area on this site where one can actual look at the data one is using?.

 

[lbass]

If the topic is different than the thread title, then yes, you should start a new thread.

No, there isn't a file posting area. Generally, posters provide simplified, but representative, examples of their data in the thread.

-LB

 

[emwalter]

I have what I thought would be a simple question, but I have been banging my head against this for a couple of days now. I have a problem being able to do a summary (any kind of summary) when I have a formula with anything greater than a local variable. Another words if a shared or a global variable is in a formula. I cannot summarize it, it won't click on the object and insert (no insert menu comes up when right clicking on the object) and you cannot insert from the main menu and select (or fool it to select) the field with a global or shared variable. The version I am currently using is 8.5, I hate it, but it is what my company bought who knows how long ago and they are slow to upgrade. This is how far behind the time they are: They are still using Access 97! I have been using CR 9 and 10 for quite a while, and I never noticed that restriction. Has anyone else encountered that problem?

Emwalter

 

[lbass]

If you are using a shared variable or if you are adding "whileprintingrecords" to a local or global variable, you won't be able to insert a summary, because these force the variables to be evaluated at a later pass.

If you need to use a shared variable or "whileprintingrecords", you can summarize the variable by accumulating it in another variable, as in:

whileprintingrecords;
shared numbervar x;
numbervar y := y + x;

Then display the y variable in a separate formula in the footer:

whileprintingrecords;
numbervar y;

-LB

 

[emwalter]

That is what I have been doing, I just wanted confirmation that I was not going crazy. For some reason, I don't remember having that problem in other versions, but I have been doing it the longer way for a long time. I just encountered a situation where using the summaries would make it easier.

Thanks,
Emwalter