cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports - Customer Statements with Ageing

nshah123
Explorer
0 Kudos

Hi,

I have created a custom accounts receivable statement in Crystal reports 2011.

However, I am currently facing some challenges:

1. How can I put a specific date so that the statement will show data upto that date e.g. today is 22/05/2014, but I want to run a statement as 30/04/2014 - how do i achieve this?

2. How can I insert formulas into crystal report that will allow me to calculate the ageing on the above as at date?

Any help would be highly appreciated.

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member197621
Active Contributor
0 Kudos

Hi,

Try this below function, will return you the last date of the month.


SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0))

former_member197621
Active Contributor
0 Kudos

Hi,

Below is the Crystal report syntax, let me know your feedback..


DateSerial(Year({?Date}), Month({?Date}) + 1, 1 - 1)

nshah123
Explorer
0 Kudos

Hi Prasanna

Thanks for the help

However, I am having trouble putting the syntax.

Where should I be putting it?

Former Member
0 Kudos

Hello

Try This

Create procedure [dbo].[customerledger]

@CCodeFrom varchar (10),

@CCodeTo varchar (10),

@Fromdate datetime,

@Todate datetime

as

begin

declare @status int

declare  @NewDate daTETIME

SET @NewDate=DATEADD(DD,0,@Fromdate)

set @status=1

--SELECT @NewDate

create table #temoSummary1 (ShortName  varchar(100),CardCode varchar(100),CardName varchar(100),U_PCODE varchar(100),ContraAct varchar(100),RefDate datetime,Linememo varchar(100) ,BaseRef varchar(100),FCCurrency varchar(100),TSHDebit numeric(18,2), TSHCredit numeric(18,2),FCDebit numeric(18,2),FCCredit numeric(18,2))

insert into #temoSummary1 

select JDT1.ShortName, ocrd.CardCode, OCRD.CardName,'' as U_PCODE,'' as ContraAct,

'' as RefDate,'' as LineMemo,'Opening' as BaseRef,'' as FCCurrency,(case when ( ISNULL(  FCDebit,0))= 0 then  (isnull(Debit,0)) end) as TSHDebit, (CASE  when (isnull(FCCredit,0)) = 0 then (isnull(Credit,0))  end ) as TSHCredit,(CASE when (isnull(FCDebit,0)) <> 0 then  (isnull(FCDebit,0)) end) as FCDebit ,(CASE when (isnull(FCCredit,0)) <> 0 then  (isnull(FCCredit,0)) end) as FCCredit

FROM         JDT1 INNER JOIN

                      OCRD ON JDT1.ShortName = OCRD.CardCode

                     

               where   OCRD.CardType='C' and ocrd.CardCode like '%C%' and  CONVERT(int ,right(JDT1.ShortName,5))>=CONVERT(int,  right(@CCodeFrom,5))                    

                      and  CONVERT(int, right(JDT1.ShortName,5))<= convert(int,  right(@CCodeTo,5)) and RefDate< @Fromdate

        

      --select * from  #temoSummary1

        

       if(@status=1)

       begin 

       

           

          update [#temoSummary1]  set  TSHDebit=0 where TSHDebit is null

                  update [#temoSummary1]  set  TSHCredit=0 where TSHCredit is null

                           update [#temoSummary1]  set  FCDebit=0 where FCDebit is null

                           update [#temoSummary1]  set  FCCredit=0 where FCCredit is null

           --select * from  #temoSummary1

        

       select  ShortName COLLATE Latin1_General_CI_AI as ShortName , CardCode COLLATE Latin1_General_CI_AI  as CardCode, CardName COLLATE Latin1_General_CI_AI as CardName,'' as U_PCODE ,'' as ContraAct ,@NewDate as  RefDate ,'' as Linememo  ,'Opening' as  BaseRef ,'' as FCCurrency ,sum(TSHDebit) as TSHDebit ,sum(TSHCredit) as  TSHCredit ,sum(FCDebit) as  FCDebit ,sum(FCCredit) as FCCredit

        

                 

   

FROM         [#temoSummary1]

group by ShortName  , CardCode, CardName

union all

select JDT1.ShortName, ocrd.CardCode, OCRD.CardName, ocrd.U_PCODE,ContraAct,

RefDate,LineMemo,BaseRef,FCCurrency,(case when FCDebit=0 then  Debit end) as TSHDebit, (CASE  when FCCredit=0 then Credit  end ) as TSHCredit,(CASE when FCDebit <> 0 then  FCDebit end) as FCDebit ,(CASE when FCCredit <> 0 then  FCCredit end) as FCCredit

FROM         JDT1 INNER JOIN

                      OCRD ON JDT1.ShortName = OCRD.CardCode

                     

               where   OCRD.CardType='C' and ocrd.CardCode like '%C%' and  CONVERT(int ,right(JDT1.ShortName,5))>=CONVERT(int,  right(@CCodeFrom,5))

                     

                      and  CONVERT(int, right(JDT1.ShortName,5))<= convert(int,  right(@CCodeTo,5)) and RefDate>=@Fromdate and RefDate<=@Todate

       order by RefDate

end

end

   -- exec  [customerledger] 'C001489' , 'C001489' ,'2013-04-15 00:00:00.000','2013-07-31 00:00:00.000'

Thanks & Reagrds

Pankaj Jha

former_member197621
Active Contributor
0 Kudos

Hi,

Create one date parameter like "Date"and don't link it to any field.

Now try below example in the Select Expert ,

Refer below image,


{OPOR.DocDate} <= DateSerial(Year({?Date}), Month({?Date}) + 1, 1 - 1)

nshah123
Explorer
0 Kudos

Got totally lost with that one...

In the query I have used

(DATEDIFF(dd,refdate,current_timestamp)


what should i replace current_timestamp with so that it ages according to the "As at date" which I input when running the report

Former Member
0 Kudos


Hi

If you need parameter that affects your query in the CR you should create the parameter within the

Add command you will have create bottom

if you want to relate the parameter to the query just press twice on the parameter

you can see simple example in the screen shot

shachar

nshah123
Explorer
0 Kudos

Hi Shachar

Thanks for your reply.

I tried putting in the parameters as you suggested, but it keeps giving me an error.

Former Member
0 Kudos

Hi Nishit,


Please try below Query.


----------------------

select T1.cardcode 'Bp Code',T1.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount',

T0.BALDUEDEB as 'Balance Due',

case T0.transtype

when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type',

Ref1,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), refdate, 103) 'Posting Date',

CONVERT(VARCHAR(10), duedate, 103) 'Due Date',

CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date' ,

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "0-30 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 30

and (datediff(dd,refdate,current_timestamp))+1< 61)

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "31 to 60 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 60

and (datediff(dd,refdate,current_timestamp))+1< 91)

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "61 to 90 days",

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90

then

case

when syscred= 0 then sysdeb

when sysdeb= 0 then -syscred

end

end "90 + days"

from dbo.JDT1 T0

INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'

where T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED and t1.CardCode='[%0]'

ORDER BY T1.CARDCODE, T0.taxdate


--------------------------


Hope this help



--

--

Regards::::

Atul Chakraborty

nshah123
Explorer
0 Kudos

Atul

Thanks once again... but it seems my question is not clear.

I have already created the query and I have got the data that I need using the query as a command in Crystal Reports (screen shot attached)

Now what I require in Crystal reports is:

1. How do i get the report to show up to a specific date - e.g. screen shows data upto 16/05/2014 but I want it to show data ONLY upto 30/04/2014

2. How do I create formulas that formulate the ageing upto the specific date i.e once i set the date as 30/04/2014, how can i create and insert formulas that will add the data shown in the report on a 0-30, 30-60, 60-90, >90 days.

I saw the results in the query you provided but I am not able to achieve this in crystal reports

Former Member
0 Kudos

You can modify your query to return data only from up to the last day of the previous month. There are various ways of doing so in SQL; some examples are located here.

For your second issue, you could use conditional totals, or depending on how your report is set up, running totals.

nshah123
Explorer
0 Kudos

Atul,

Thanks for the query... I have managed to use it in the sql query / crystal reports.

However, i still need help with getting the report as at a specific date, and the ageing then calculating on those basis.

Former Member
0 Kudos

Hi Nishit,

Please try below query with Data Parameter. Only you need to Declare From Date and To Date Parameter.

------------------

select T1.cardcode 'Bp Code',T1.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount',

T0.BALDUEDEB as 'Balance Due',

case T0.transtype

when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type',

Ref1,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), refdate, 103) 'Posting Date',

CONVERT(VARCHAR(10), duedate, 103) 'Due Date',

CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date' ,

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "0-30 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 30 and (datediff(dd,refdate,current_timestamp))+1< 61)

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "31 to 60 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 60 and (datediff(dd,refdate,current_timestamp))+1< 91)

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "61 to 90 days",

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90

then

case

when syscred= 0 then sysdeb

when sysdeb= 0 then -syscred

end

end "90 + days"

from dbo.JDT1 T0

INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'

where T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED and T0.RefDate >= '2014-01-01' and T0.RefDate <= '2014-06-02'

ORDER BY T1.CARDCODE, T0.taxdate

-----------------------

Hope this help

--

--

Regards::::

Atul Chakraborty

nshah123
Explorer
0 Kudos

Hi Atul

Thanks for that.

I have managed to use the @RefDate for the report to show data upto that specific date.

However, when I cross checked my results with what is produced in SAP B-One reports and my report, I noticed that some balances are wrong or completely ommitted.

My SQL query that I am using in  Crystal Reports is:

Select

T1.cardcode 'Bp Code',

T1.cardname 'Name',

taxdate 'Doc Date' ,

case T0.transtype

when '13' then 'INV'

when '14' then 'CRN'

when '24' then 'RCPT'

else 'Other'

end 'Type',

Ref2,

fccurrency 'BP Currency',

(T0.FCDEBIT - T0.FCCREDIT) as 'FCY Amount',

sysdeb 'Debit ',syscred 'Credit',

(T0.BALDUEDEB - T0.BALDUECRED) as 'Balance Due',

T1.[AddID],

T2.[Street],

T2.[City],

T2.[Country],

case when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31 then

case

when BALDUECRED <> 0 then -BALDUECRED

else BALDUEDEB

end

end "0-30 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 30

and (datediff(dd,refdate,current_timestamp))+1< 61)

then

case

when BALDUECRED <> 0 then -BALDUECRED

else BALDUEDEB

end

end "31 to 60 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 60

and (datediff(dd,refdate,current_timestamp))+1< 91)

then case when BALDUECRED <> 0 then -BALDUECRED else BALDUEDEB  end end "61 to 90 days",

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90

then

case

when BALDUECRED= 0 then BALDUEDEB

when BALDUEDEB= 0 then -BALDUECRED

end

end "90 + days"

from dbo.JDT1 T0

INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c' INNER JOIN CRD1 T2 ON T1.CardCode = T2.CardCode WHERE T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED ORDER BY T1.CARDCODE, T0.taxdate

record selection formula for @RefDate is: {Command.Doc Date} <= {?@RefDate}

Please help me understand why certain transactions or customer accounts are completely ommitted.

nshah123
Explorer
0 Kudos

I figured out why certain accounts were ommitted...

Still need help with the ageing - even though the refdate is a user inputted value, the query is still calculating ageing and balances as at current timestamp and not the refdate.

Any idea how i can correct this?

nshah123
Explorer
0 Kudos

Hi Atul

I am now using the query below (based on the one you had provided)

DECLARE @D1 DATETIME

SET @D1=  '2012-04-01 00:00:00.000'

Select

T1.cardcode 'Bp Code',

T1.cardname 'Name',

T0.refdate 'Doc Date' ,

case T0.transtype

when '13' then 'INV'

when '14' then 'CRN'

when '24' then 'RCPT'

else 'Other'

end 'Type',

Ref2,

T0.fccurrency 'BP Currency',

(T0.FCDEBIT - T0.FCCREDIT) as 'FCY Amount',

sysdeb 'Debit ',syscred 'Credit',

(T0.BALDUEDEB - T0.BALDUECRED) as 'Balance Due',

T1.[AddID],

T2.[Street],

T2.[City],

T2.[Country],

case

when (DATEDIFF(dd,refdate,{?Age}))+1 < 31 then

case

when BALDUECRED <> 0 then -BALDUECRED

else BALDUEDEB

end

end "0-30 days",

case when ((datediff(dd,refdate,{?Age}))+1 > 30

and (datediff(dd,refdate,{?Age}))+1< 61)

then

case

when BALDUECRED <> 0 then -BALDUECRED

else BALDUEDEB

end

end "31 to 60 days",

case when ((datediff(dd,refdate,{?Age}))+1 > 60

and (datediff(dd,refdate,{?Age}))+1< 91)

then case when BALDUECRED <> 0 then -BALDUECRED else BALDUEDEB  end end "61 to 90 days",

CASE

when (DATEDIFF(dd,refdate,{?Age}))+1 > 90

then

case

when BALDUECRED= 0 then BALDUEDEB

when BALDUEDEB= 0 then -BALDUECRED

end

end "90 + days"

from dbo.JDT1 T0

INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'

INNER JOIN CRD1 T2 ON T1.CardCode = T2.CardCode

WHERE  T0.BALDUEDEB != T0.BALDUECRED  and T0.Refdate >= @D1 and T0.Refdate <= {?Age}

ORDER BY T1.CARDCODE, T0.refdate

However, I have the following challenges now:

Say for instance, my ageing date is 30/04/2014

1. There are invoices with a posting/doc/due date before the ageing date, but have been allocated by a receipt received after this date - these transactions should show on the ageing because the transactions were not reconciled before the ageing date

2. There are certain accounts which are not showing up at all where there are transactions entered before the ageing date, with no subsequent receipts

How can I resolve these issues?

PS: I actually realised this because I compared the results from the query with the results from SAP built in ageing reports.

Former Member
0 Kudos

Hi Nishit,

Please check below link.

Hope this help

--

--

Regards::::

Atul Chakraborty

nshah123
Explorer
0 Kudos

Hi Atul

Thank you for your response.

However, I have already done the query  as Add Command in crystal reports for the Customer Statements.


What I need help with is:

1. setting the ageing date through paramaters

2. calculating the formula fields for current, 30 days, 60 days etc etc