Print Page | Close Window

crystal report formula : date control

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8036
Printed Date: 08 May 2024 at 12:23pm


Topic: crystal report formula : date control
Posted By: skyfox
Subject: crystal report formula : date control
Date Posted: 14 Oct 2009 at 5:48pm

i need to create a crystal report for user to print the month end data. but now i facing some issue on writing formula code.

i create parameter field allow user select either "Current period", "previous period 1" and "previous period 2".

when user select "current period" in parameter field, the report title will show out the date of this month end, if user select "previous period 1" then report title will show out the date of previous month end.

Example:

Current month is October 2009

Users print this report on 15/10/2009.

If users print trial balance as of current period, report title will show out “31 October 2009”  or show "31/10/2009"

If users print trial balance as of previous period 1, report title will show out “30 September 2009” or show "30/09/2009"

If users print trial balance as of previous period 2, report title will show out “31 August 2009” or show "31/08/2009"

May i know how to write a formula on this issue?

*the coding i write currently as below:

select {APAgedTrialBalanceReport/QueryOptions.ReportAsOf}
    case 'C':
    ToText (PrintDate)      <-this on can show the print date
    case 'P1':
    "XXX1"          <- temporally hard code because don't know how to write code?
    case 'P2':
    "XXX2"          <- temporally hard code because don't know how to write code?

Thank you.


Replies:
Posted By: lockwelle
Date Posted: 15 Oct 2009 at 6:50am
How I approach the last day of the month is to get the first day of the month after you want and subtract a day.
 
so to go back to the previous period,
local numbervar iMonth := month(currentDate);
local numbervar iYear := year(currentDate);
local datetimevar iStart;
 
 
iStart := Date(iYear, iMonth, 1);  //get the first of the month
iStart := DateAdd(d,-1,iStart);  //go back 1 day.
 
 
you might want to play with the variables, but the idea is get the first of the month and subtract 1 day.  so, to get the prior period, get Oct 1, 2009 and subtract a day--9/30/2009.
 
You could probably put it in a custom function, and then call if multiple times.
 
HTH
 

Posted By: skyfox
Date Posted: 15 Oct 2009 at 7:26pm
thank you, by your information i can print out period month end date.
but for currently month, how to change to currently month end?

local numbervar iMonth := month(currentDate);
local numbervar iYear := year(currentDate);
local datetimevar iStart;
local datetimevar iStart2;
iStart := Date(iYear, iMonth, 1);  //get the first of this month
iStart := DateAdd("d",-1,iStart);  //go back 1 day.
local numbervar iMonth2 := month(iStart);
local numbervar iYear2 := year(iStart);
iStart2 := Date(iYear2, iMonth2, 1);  //get the first of previous month
iStart2 := DateAdd("d",-1,iStart2); //go back 1 day.

select {APAgedTrialBalanceReport/QueryOptions.ReportAsOf}
    case 'C':
    ToText (PrintDate)
    case 'P1':
    ToText (iStart)
    case 'P2':
    ToText (iStart2)

Posted By: skyfox
Date Posted: 15 Oct 2009 at 9:08pm
finally the formula done,
local numbervar iMonth := month(currentDate);
local numbervar iYear := year(currentDate);
local datetimevar iStart;
local datetimevar iStart2;
local datetimevar iStart3;
iStart := Date(iYear, iMonth, 1);  //get the first of this month
local numbervar iMonth3 := month(iStart);
local numbervar iYear3 := year(iStart);
iStart := DateAdd("d",-1,iStart);  //go back 1 day.
local numbervar iMonth2 := month(iStart);
local numbervar iYear2 := year(iStart);
iStart2 := Date(iYear2, iMonth2, 1);  //get the first of previous month
iStart2 := DateAdd("d",-1,iStart2); //go back 1 day.
iStart3 := Date(iYear3, iMonth3, 1);  //get the first of this month
iStart3 := DateAdd("m",+1,iStart3); //go forward 1 month.
iStart3 := DateAdd("d",-1,iStart3); //go back 1 day.

select {APAgedTrialBalanceReport/QueryOptions.ReportAsOf}
    case 'C':
    ToText (iStart3)
    case 'P1':
    ToText (iStart)
    case 'P2':
    ToText (iStart2)


Print Page | Close Window