Skip to Content

Opening balance is repeating insted of Closing Balance in the Report

Dear Experts

i had created a crystal Report, which query is attached with the mail.

when i run this query, all the entry's are appearing correctly, but closing balance is not appearing as Opening balance

For Eg: - I had migrated OB as on 01/04/2017 - 50000

starting from 02/04/2017 i had made number of transaction

then again when i run the same report including the transaction obliviously the OB

should be the Closing balance , but in my report the same OB as on 01/04/2017 - 50000

is appering

Pls any one could help me to sort it out

Regards

select A.TaxDate,d1.CardName,b.ContraAct 
,case when isnull(c.acctname,'')='' then d.CardName else c.acctname end 
  + ' - ' + isnull(	B.LineMemo,'') + ' - ' +
isnull(CASE WHEN A.TRANSTYPE ='-2'
then 'OB'
 WHEN A.TRANSTYPE ='-3'
then 'BC NO.'
 WHEN A.TRANSTYPE ='13'
then 'INV NO.'
 WHEN A.TRANSTYPE ='14'
then 'CN NO.'
 WHEN A.TRANSTYPE ='15'
then 'DN NO.'
 WHEN A.TRANSTYPE ='16'
then 'RE NO.'
 WHEN A.TRANSTYPE ='18'
then 'INV No.' 
 WHEN A.TRANSTYPE ='19'
then 'PC NO.'
 WHEN A.TRANSTYPE ='20'
then 'PD'
 WHEN A.TRANSTYPE ='21'
then 'PR NO.'
WHEN A.TRANSTYPE ='24'
then E.TrsfrRef
 WHEN A.TRANSTYPE ='30'
then 'JE NO.'
WHEN A.TRANSTYPE ='46'
then F.TrsfrRef
  WHEN A.TRANSTYPE ='57'
then 'Chq.No.' + convert(nvarchar(100),g.CheckNum)
  WHEN A.TRANSTYPE ='58'
then 'ST NO.'
 WHEN A.TRANSTYPE ='59'
then 'SI NO.'
 WHEN A.TRANSTYPE ='60'
then 'SO NO.'
 WHEN A.TRANSTYPE ='67'
then 'IM NO.'
 WHEN A.TRANSTYPE ='68'
then 'SI NO.'
 WHEN A.TRANSTYPE ='69'
then 'IF'
 WHEN A.TRANSTYPE ='162'
then 'MR'
 WHEN A.TRANSTYPE ='203'
then 'DT NO.'
 else '' end,'')  + '' 	
  + isnull(A.Ref2,'') 	AS 'DESCRIPTION'
  ,sum(b.Debit) 'Debit',Sum(b.Credit) 'Credit' 
from ojdt a
inner join jdt1 b on a.transid=b.transid
left join oact c on c.acctcode=b.ContraAct
left join ocrd d on d.CardCode=b.ContraAct
left join ocrd d1 on d1.cardcode=b.ShortName
LEFT JOIN (select a.ObjType,a.DocEntry,a.TrsfrRef from orct a	) e on e.objtype=a.transtype and e.docentry=a.BaseRef
LEFT JOIN (select a.ObjType,a.DocEntry,a.TrsfrRef from ovpm a	) f on f.objtype=a.transtype and f.docentry=a.BaseRef
LEFT JOIN (select a.ObjType,a.TRANSNUM,a.CHECKNUM from ocho a   ) g on g.ObjType=a.TransType and g.TransNum=a.TransId
where b.ContraAct not in ('3110004','3110005','3110006') and
a.TaxDate between @FD and @TD
and b.ShortName=@AC
group by  A.TaxDate,d1.CardName,b.ContraAct 
,case when isnull(c.acctname,'')='' then d.CardName else c.acctname end 
  + ' - ' + isnull(	B.LineMemo,'') + ' - ' +
isnull(CASE WHEN A.TRANSTYPE ='-2'
then 'OB'
 WHEN A.TRANSTYPE ='-3'
then 'BC NO.'
 WHEN A.TRANSTYPE ='13'
then 'INV NO.'
 WHEN A.TRANSTYPE ='14'
then 'CN NO.'
 WHEN A.TRANSTYPE ='15'
then 'DN NO.'
 WHEN A.TRANSTYPE ='16'
then 'RE NO.'
 WHEN A.TRANSTYPE ='18'
then 'INV No.' 
 WHEN A.TRANSTYPE ='19'
then 'PC NO.'
 WHEN A.TRANSTYPE ='20'
then 'PD'
 WHEN A.TRANSTYPE ='21'
then 'PR NO.'
WHEN A.TRANSTYPE ='24'
then E.TrsfrRef
 WHEN A.TRANSTYPE ='30'
then 'JE NO.'
WHEN A.TRANSTYPE ='46'
then F.TrsfrRef
  WHEN A.TRANSTYPE ='57'
then 'Chq.No.' + convert(nvarchar(100),g.CheckNum)
  WHEN A.TRANSTYPE ='58'
then 'ST NO.'
 WHEN A.TRANSTYPE ='59'
then 'SI NO.'
 WHEN A.TRANSTYPE ='60'
then 'SO NO.'
 WHEN A.TRANSTYPE ='67'
then 'IM NO.'
 WHEN A.TRANSTYPE ='68'
then 'SI NO.'
 WHEN A.TRANSTYPE ='69'
then 'IF'
 WHEN A.TRANSTYPE ='162'
then 'MR'
 WHEN A.TRANSTYPE ='203'
then 'DT NO.'
 else '' end,'')  + '' 	
  + isnull(A.Ref2,'') 	
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 29 at 05:20 AM

    Hi,

    Period End Closing creates two different entries. One at the end of the Year which is Closing Entry and then that is moved on the first day of next year as Opening Entry(balance).

    Check by incorporating dates in the query and see if you can get the desired result.

    regards,

    Jitin

    Add comment
    10|10000 characters needed characters exceeded