extract opening balance of loan

[AzizKamal]

I need to extract opening balance of loan for employees. I am using CR version, which is included in VS 2008. I have the following tables:

Table1 Loan:
Fields: empcode,code,ldate,loanamt
Sample Rows:
10012 204 100000 31/7/10
10012 206 90000 1/1/09
10012 207 10000 31/8/10

Table2 Period:
Fields: code,startdate,enddate
Sample Rows:
79 1/7/07 31/7/07
80 1/8/07 31/8/07
81 1/9/07 31/9/07
91 1/7/08 31/7/08
93 1/6/09 30/6/09
114 1/7/10 31/7/10
115 1/8/10 31/8/10

Table3 SalaryMasterPay:
Fields: empcode,periodcode,entryid
Sample Rows:
10012 115 22175
10012 91 20654
10012 81 20653
10012 93 20655
10012 114 16756
10012 79 20651
10012 80 20652

Table4 SalaryTransdd:
Fields: masterid,deductioncode,loanid,amount
Sample Rows:
22175 6 204 2028
20654 6 204 3690
20653 6 204 3736
20655 6 204 3644
16756 6 204 3599
20651 6 206 3830
20652 6 206 3782

Now, let’s say user selects 2010 as current year. Then opening balance for empcode 10012 up to Dec 2009 will be as follows:

Total Loans up to Dec 2009:
Sum of Loan Amount from Loan Table where year(loan.ldate) is less than 2010.
Result: 90000

Total Deductions up to Dec 2009:
Sum of Amount from SalaryTransdd Table where year(period.enddate) for masterid is less than 2010. Entryid is unique in SalaryMasterPay table and is a foreign key in SalaryTransdd Table. Primary key for SalaryMasterPay table is empcode+periodcode and for SalaryTransdd Table is Masterid + DeductionCode. DeductionCode for loan is 6

Result:
MasterIDs: 20654,20653,20655,20651,20652
Amount: 3690+3736+3644+3830+3782=18682

Hence opening balance for empcode 10012 is 90000-18682=71318

To achieve this in crystal reports:

1. I added loan,period,salarymasterpay and salarytransdd tables
2. In the link tab:
a. LOAN.CODE --> SALARYTRANSDD.LOANID (Join Type=Left Outer Join)
b. SALARYMASTERPAY.ENTRYID --> SALARYTRANSDD.MASTERID (Join Type=Inner Join)
c. PERIOD.CODE --> SALARYMASTERPAY.PERIODCODE (Join Type=Inner Join)
3. I created formula fields:

year:
2010

oplnamt:
if year({LOAN.LDATE})<{@year} then
{LOAN.LOANAMT}
else
0

Now if I place oplnamt in Details Section, I get three rows; 0,90000 and 0 whose sum at empcode group level will give me correct loan amount. But I am stuck as how to subtract loan deductions from these 90000.

When I place loan.code in Details Section, I get 3 rows:
oplnamt code
0 204
90000 206
0 207

Then I placed salarytransdd.amount in Details Section, I got deductions related to code 204 and 206 and a row for code 207 with blank amount field.

But this shows 90000 twice as there are two deductions for loanid 206 and then I also need to check for period.enddate to include deductions up to dec 2009 only…

 

[AzizKamal]

I accomplished the task using the following steps:

Group By: loan.empcode
Sort Fields: loan.empcode,loan.code

Formula:

oplnamt:
numbervar loancode;
if year({LOAN.LDATE})<{@year} and
{LOAN.NATURE}="P" and
{LOAN.CODE}<>loancode then
(loancode:={LOAN.CODE};
{LOAN.LOANAMT})
else
(loancode:={LOAN.CODE};
0)

oplnded:
if year({PERIOD.ENDDATE})<{@year} and {SALARYTRANSDD.DEDUCTIONCODE}=6 then
{SALARYTRANSDD.AMOUNT}
else
0

opln:
Sum ({@oplnamt}, {LOAN.EMPCODE})-Sum ({@oplnded}, {LOAN.EMPCODE})

I placed oplnamt and oplnded in details section and inserted summaries in group footer section. And then I placed opln in group footer section which generated 71,318 for sample employee code 10012