FORMULA HELP

building reports crystal reports
1

In my report there are two parameters named “C_Month” and “C_Year”, for example we pass “JUL” and “2009” seperately
In the report i have to display a label in the following manner “Current Data of: Jul 31, 2009”
now the problem is, users are able to select the future dates from the application side like “DEC” “2009” from the drop down and if they do so my label will display “Current Data of: DEC 31, 2009”
and the report data will not be of DEC 2009 which will mislead them, so i want to write a formula in Crystal Report, if the users selects the future date lable should display the “Current Data of: Jul 31, 2009” and if the users selects back date like “DEC” “2008” the label should show “Current Data of: Dec 31, 2008”, Please let me know how can i implement it

datahelp (BOB member since 2009-07-09)

2

Hello.

Assuming you pass the month as a string and the year as a number, you can do the following:

  1. Convert the two inputs into one date value - {@Convert Input To Day1}:
Date({?Month} & " 1, " & ToText({?Year},"#"))
  1. Get the last day of the month - {@Get Last Day of Input}:
Date(DateAdd("m",1,{@Convert Input To Day1}-1))

3.Determine what the last day of the current month is - {@Last Day of Current}:

Date(DateAdd("m",1,CurrentDate - Day(CurrentDate) + 1) - 1 )
  1. Display most current date - {@Display Most Current}:
local datevar showdate ;

If {@Get Last Day of Input} > CurrentDate Then showdate := {@Last Day of Current} 
                                          Else showdate := {@Get Last Day of Input} ;

"Current Data of: " & showdate ;

Of course, you could handle all that with only long formula, but I did it with four to keep it simple.

lgonzalez (BOB member since 2002-07-17)

3

Thanks a lot for your reply, I am passing both the Month and Year parameter as string so Is it possible to do it in one formula ? and what changes i need to make kindly let me know ?

datahelp (BOB member since 2009-07-09)

4

Hello.

If both are string values, then the only change is with the first formula:

Date({?Month} & " 1, " & {?Year})

lgonzalez (BOB member since 2002-07-17)

5

Can i create a single formula with all these in one ?

datahelp (BOB member since 2009-07-09)

6

Sure, just replace the formulas with local variables:

local datevar dFirstInput  := Date({?Month} & " 1, " & ToText({?Year},"#")) ;
local datevar dLastInput   := Date(DateAdd("m",1,dFirstDay-1)) ;
local datevar dLastCurrent := Date(DateAdd("m",1,CurrentDate - Day(CurrentDate) + 1) - 1 ) ;
local datevar dShowDate ;

If dLastInput  > CurrentDate Then dShowDate := dLastCurrent 
                             Else dShowDate := dLastInput ;

"Current Data of: " & dShowDate ;

I did not check the code in CR (in a hurry), but it should be right.

lgonzalez (BOB member since 2002-07-17)

7

There are few things i am wanted to implement

  1. The date is showing in following format 12/30/2008 as it should show as December 31, 2009 not 12/30/2008

  2. Instead of comparing with the Current date there is a column with the data date in following format “31-MAY-09” the column name is DATA_DATE

datahelp (BOB member since 2009-07-09)

8

Hello.

Please don’t take this the wrong way, but I have given you more than enough for you to be able to finish. You could easily substitute DATA_DATE for CurrentDate - I do not have to do that for you. For the date format, look up ToText in the CR Help. If you can’t figure it out after looking and trying a few things on your own, I will be happy to help again. It’s better to try and fail than for me to write the end solution for you.

Good luck!

lgonzalez (BOB member since 2002-07-17)

9

Thanks for your reply, I am just a starter on crystal report, trying to learn so pls. dont mind if i ask you silly questions, i tried replacing the currentdate with DATA_DATE column but still its showing the currentdate and currently i am using your first solution because the single formula was giving me some error

datahelp (BOB member since 2009-07-09)

10

Luis Please reply i am badly i need of your help

datahelp (BOB member since 2009-07-09)

11

Try to right click and format the field now that you changed it to datadate.

muffntuf :us: (BOB member since 2006-01-04)