Crystal Reports 9 - Formula Field for Dates 1

[JD5241]

I'm developing a report in Crystal Reports 9; I have two columns in a SQL table, eMonth and eYear, neither of which has a date/time designation, they are just numbers that represent months and years (eMonth: 1 through 12, and eYear: 2001, 2002, 2003, etc.).
I am not altogether familiar with formulas, and I am trying to come up with one that will allow me to put them together as a date (e.g., 1/2003), and be able to sort a group on that date/formula. Sorting a group on either the month or the year singularly does not return the results that I'm looking for.
What I have tried (unsuccessfully) so far:

StringVar eMonth:="";
StringVar eYear:="";
(if isnull({EOM.eMonth}) then eMonth:= ""
else eMonth:= {EOM.eMonth} + Month ()"/");
(if isnull({EOM.eYear}) then eYear:= ""
else eYear:= {EOM.eYear} + "");
TRIM (eMonth + eYear);

and...

Left({EOM.eMonth}, 2) + "/" + right({EOM.eYear}, 7)

I've had to learn this application on the fly without the benefit of anything but the manual and some of the knowledge base on their website, so I'm not sure how to proceed with this particular piece. Any help would be greatly appreciated! Thanks!

 

[scottostanek]

I use this kind of thing all the time and my best experience is to make a single number out of it for the sort:

//do all your mull testing first then
{EOM.eYear} * 100 + {EOM.eMonth}

Jan of 2004 is 200401
May of 2001 is 200105
Dec of 1999 is 199912

Sorting, no problem!
Scotto the Unwise

 

[JD5241]

Thank you for your help, I've saved that for future use.
However, I need the date to be viewed like '1/2003', and have the report see it as a date field that I can sort a group on. This is probably something simple, but I'm stumped.

 

[kskid]

Here's what I have done.

Option 1 - If you have to handle null values

//RptDate
Numbervar eMonth;
numberVar eYear;
datevar eDate;

if isnull({EOM.eMonth}) then eMonth:= 1
    else eMonth:= tonumber({EOM.eMonth});
if isnull({EOM.eYear}) then eYear:= 1900
    else eYear:= tonumber({EOM.eYear});
eDate := date(eYear,eMonth,1);

Option 2 - If {EOM.EMonth} and {EMO.EYear} is used in your record selection where the null values are screened out because of some parameter such as

not(
isnull({EOM.eYear)) and isnull({EOM.eMonth)
)

OR if parameters, ?PYear and ?PMonth, are used

tonumber({EOM.eYear}) = {?PYear} and
tonumber({EOM.eMonth}) = {?PMonth}

Then the date formula can be the following

//RptDate
numbervar eMonth := tonumber({EOM.eMonth);
numbervar eYear := tonumber({EOM.eYear});
datevar eDate := date(eYear,eMonth,1);

I can then group and sort on @RptDate by month in the format you want.

Cheers,

-LW

 

[JD5241]

Thanks for your help.
I think maybe I didn't give enough information... I need the the date to appear on the report 'month/year', i.e., '1/2004'. Having it show up as '1/1/2004' or as a single number won't work for what I am doing, it's a monthly summary report, so individual days of the month aren't needed (or even listed in the table I'm pulling from), and could possibly be misleading.
I'm pulling from all the rows in a table that have the same month and year, and putting the sum of a numerical field in the same rows on the report (the "Charges" field shown below). So there could be several rows with the same month and year, but with different charge amounts, that would be summarized and grouped by the month and year.
So it would look something like this:
Month Charges
1/2004 $2500.00
2/2004 $3500.00
Total $6000.00

Sorry to be such a pain, and thank you for all your help!

 

[lbass]

You don't really need variables. You could use:

date({table.eyear},{table.emonth},01)

You can then right click on this formula->format field->date->customize->day: None

This will give you the display 1/2004.

-LB

 

[kskid]

Well DUH!!!!

That was too easy. I know I had a brain freeze not to pick that one up.

Another star