none
vb.net crystal report running total of balances based on greater date of repeating Individual ID records RRS feed

  • Question

  • 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 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


    Saturday, August 25, 2018 10:45 AM

All replies

  • You best post to the Crystal Reports forum for help, which is a 3rd party tool. MSDN doesn't support 3rd party tools.

    http://www.crystalreportsbook.com/forum/default.asp

    Saturday, August 25, 2018 12:18 PM