M
Man TopCus
Guest
Hi All
Am designing a loan lending system. I have managed to create most reports but am stuck at one report (Report to filter only records between two dates)- Filtering code is Ok and working well, the issue is the the last running total for field "Balance". My Crystal report fetches data from a query and the fields are as follows respectively:
Example In the first grouping (FERRY 1A) Balance Column SubTotal where ID 4444444 is repeating on 24-August is picked and 22-August dropped then added to the only ID 666666 giving total of (49970.00+70400.00)=120370.00 but my running total configuration produces wrong subTotal of 122400.00 whereby 52,000.00 of ID 444444 for DOP 22-Aug is used instead of 24- Aug.
My configuration in running total Editor are as shown:
Running total name: subBalance
Field to summarize: Qrytable.Balance
Type of summary: Sum
Evaluate: On change of Field- Qrytable.NatID
Reset: Use a Formula- if {QryRegLoanPaymentsZonewise.NatID}<>previous({QryRegLoanPaymentsZonewise.NatID}) and {QryRegLoanPaymentsZonewise.DOP} >previous({QryRegLoanPaymentsZonewise.DOP}) then sum({QryRegLoanPaymentsZonewise.PaybackTotAmount})
NOTE: 1)For Repeating Balances of a particular NatID, I just want to pick its latest(greater date) to use for totals. 2) I want correct subtotals for each and every group in the details section.
any help shall be apreciated.
ManTopcus
ClientZone DOP NatID Surname PayBMode PaybackAmount CummulatedPay PaidAmount LateFine TotalPaid Balance
GROUP 1: FERRY 1A
FERRY 1A
FERRY 2A
MWANANZIA
Continue reading...
Am designing a loan lending system. I have managed to create most reports but am stuck at one report (Report to filter only records between two dates)- Filtering code is Ok and working well, the issue is the the last running total for field "Balance". My Crystal report fetches data from a query and the fields are as follows respectively:
Example In the first grouping (FERRY 1A) Balance Column SubTotal where ID 4444444 is repeating on 24-August is picked and 22-August dropped then added to the only ID 666666 giving total of (49970.00+70400.00)=120370.00 but my running total configuration produces wrong subTotal of 122400.00 whereby 52,000.00 of ID 444444 for DOP 22-Aug is used instead of 24- Aug.
My configuration in running total Editor are as shown:
Running total name: subBalance
Field to summarize: Qrytable.Balance
Type of summary: Sum
Evaluate: On change of Field- Qrytable.NatID
Reset: Use a Formula- if {QryRegLoanPaymentsZonewise.NatID}<>previous({QryRegLoanPaymentsZonewise.NatID}) and {QryRegLoanPaymentsZonewise.DOP} >previous({QryRegLoanPaymentsZonewise.DOP}) then sum({QryRegLoanPaymentsZonewise.PaybackTotAmount})
NOTE: 1)For Repeating Balances of a particular NatID, I just want to pick its latest(greater date) to use for totals. 2) I want correct subtotals for each and every group in the details section.
any help shall be apreciated.
ManTopcus
ClientZone DOP NatID Surname PayBMode PaybackAmount CummulatedPay PaidAmount LateFine TotalPaid Balance
GROUP 1: FERRY 1A
FERRY 1A
22-Aug-2018 12:00:00 AM
444444 OCHOLA DAILY 78,000.00
0.00
26,000.00
0.00
26,000.00
52,000.00
FERRY 1A 24-Aug-2018 12:00:00 AM
444444 OCHOLA DAILY 78,000.00
26,000.00
28,000.00
0.00
28,000.00
49,974.00
FERRY 1A 24-Aug-2018 12:00:00 AM
666666 JOHN MONTHLY 79,200.00
0.00
8,800.00
0.00
8,800.00
70,400.00
FERRY 1A Totals of this group 157,200.00
62,800.00
0.00
62,800.00
122,400.00
GROUP 2: FERRY 2A FERRY 2A
22-Aug-2018 12:00:00 AM
123456789 KENNEDY MONTHLY 11,550.00
0.00
5,000.00
192.50
5,192.50
6,550.00
FERRY 2A 24-Aug-2018 12:00:00 AM
123456789 KENNEDY MONTHLY 11,550.00
5,000.00
5,000.00
192.50
5,192.50
1,550.00
FERRY 2A, Totals of this group 11,550.00
10,000.00
385.00
10,385.00
128,950.00
GROUP 3: MWANANZIA MWANANZIA
22-Aug-2018 12:00:00 AM
23538307 OCHOLA DAILY 195,000.00
0.00
3,000.00
0.00
3,000.00
192,000.00
MWANANZIA 23-Aug-2018 12:00:00 AM
23538307 OCHOLA DAILY 195,000.00
3,000.00
5,000.00
0.00
5,000.00
189,997.00
MWANANZIA 24-Aug-2018 12:00:00 AM
23538307 OCHOLA DAILY 195,000.00
8,000.00
3,000.00
0.00
3,000.00
191,992.00
MWANANZIA 24-Aug-2018 12:00:00 AM
222222 MWITA MONTHLY 11,000.00
0.00
1,900.00
0.00
1,900.00
9,100.00
MWANANZIA 24-Aug-2018 12:00:00 AM
222222 MWITA MONTHLY 11,000.00
1,900.00
2,000.00
0.00
2,000.00
8,999.00
MWANANZIA Totals of this Group 206,000.00
14,900.00
0.00
14,900.00
330,050.00
Grand Total: 374,750.00
87,700.00
385.00
88,085.00
330,050.00
Continue reading...