running total of one group is adding/subtracting with other group

[former_member252592]

HI

I create a crystal report of employee loan/advance select parameters are Employee name rage and also group it with employee name im using the following loop in formula to get balance from debit-credit its working fine when i range for single employee but if i range more than 1 employee it adds or subtract running total of balance with next group. i want whatever the range i select it show individual balance of each employee i.e. do not mix it up with other employee balance

Whileprintingrecords;

Shared Numbervar balance;

Shared Numbervar display;

balance:={Command.Debit}-{Command.Credit};

display:=display+balance;

display

kindly see the following screen shot

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT

T0.[TransId] as 'JV No.', T0.[Account], T0.[Debit],T0.[Credit],

sum(T0.[Debit]-T0.[Credit]) as 'Balance',

T0.[U_Emp] as 'Employee Name', isnull(T0.[ProfitCode],'N/S') as 'Dept Code', T1.[RefDate],

isnull(T1.Memo,'N/A') as 'Remarks', T0.Ref1 as 'Description', T2.Ocrname

FROM

JDT1 T0  INNER JOIN OJDT T1 ON T0.TransId = T1.TransId

INNER JOIN OOCR T2 ON T0.ProfitCode = T2.OcrCode

Left outer join OHEM T3 on T3.firstName = T0.U_Emp

inner join OVPM T2 on T0. TransId = T2.TransId

WHERE

T0.[Account] = 'A1105002' and T3.firstname between '{?firstnsame@Select firstname from OHEM order by  firstname}' and '{?a.firstnsame@Select a.firstname from OHEM a order by a.firstname}'

order by T1.[RefDate], T3.firstname

Group by  T0.[TransId] as 'JV No.', T0.[Account], T0.[Debit],T0.[Credit],T0.[U_Emp]

Thanks & Regards,

Nagarajan

[former_member252592]

thanx nagrajan K for reply i tried your query that didn't work i made some changes in it then use but still same result follow query i made changes

SELECT

T0.[TransId] as 'JV No.', T0.[Account], T0.[Debit],T0.[Credit],

sum(T0.[Debit]-T0.[Credit]) as 'Balance',

T3.firstName as 'Employee Name', isnull(T0.[ProfitCode],'N/S') as 'Dept Code', T1.[RefDate],

isnull(T1.Memo,'N/A') as 'Remarks', T0.Ref1 as 'Description', T2.Ocrname

FROM

JDT1 T0  INNER JOIN OJDT T1 ON T0.TransId = T1.TransId

INNER JOIN OOCR T2 ON T0.ProfitCode = T2.OcrCode

Left outer join OHEM T3 on T3.firstName = T0.U_Emp

WHERE

T0.[Account] = 'A1105002' and T3.firstname between '{?firstnsame@Select firstname from OHEM order by  firstname}' and '{?a.firstnsame@Select a.firstname from OHEM a order by a.firstname}'

Group by 

T0.[TransId] , T0.[Account], T0.[Debit],

T0.[Credit],T0.[U_Emp], T0.[ProfitCode], T1.[RefDate],T1.Memo, T0.Ref1,T2.Ocrname,T3.firstName

order by

T1.[RefDate], T3.firstname

im using the following formula for balance in the following pic u can see its add/sub the bal from other group but i want each group its own balance, group is made on the employee name basis.

Whileprintingrecords;

Shared Numbervar balance;

Shared Numbervar display;

balance:={Command.Debit}-{Command.Credit};

display:=display+balance;

display

[former_member184146]

Hi Jamil ,

can attach the rpt file here ???

[former_member252592]

sorry for delay reply i was on lunch

scn is not permitting rpt format i will send u the query

SELECT

T0.[TransId] as 'JV No.', T0.[Account], T0.[Debit],T0.[Credit],

T0.[Debit]-T0.[Credit] as 'Balance',

T0.[U_Emp] as 'Employee Name', isnull(T0.[ProfitCode],'N/S') as 'Dept Code', T1.[RefDate],

isnull(T1.Memo,'N/A') as 'Remarks', T0.Ref1 as 'Description', T2.Ocrname

FROM

JDT1 T0  INNER JOIN OJDT T1 ON T0.TransId = T1.TransId

INNER JOIN OOCR T2 ON T0.ProfitCode = T2.OcrCode

Left outer join OHEM T3 on T3.firstName = T0.U_Emp

--inner join OVPM T2 on T0. TransId = T2.TransId

WHERE

T0.[Account] = 'A1105002' and T3.firstname between '{?firstnsame@Select firstname from OHEM order by  firstname}' and '{?a.firstnsame@Select a.firstname from OHEM a order by a.firstname}'

order by T1.[RefDate], T3.firstname