on 11-28-2011 10:28 PM
There are 2 fields in my report. One is the month number and the other one is a year.
fiscal year starts in June. So we are in fiscal month 6 and fiscal year 2012 now.
I have these 2 fields in the crystal report and I need to create a formula ,
that will display the Oct12 And then I need to supress the year - 12 . I might have fiscal year 2011 also in my dataset so it needs to be sorted in ascending order) , Right now it just displays as '5 2012'
. It needs to display as Oct
So I created a formula to convert number number to name: =Month(ToDate(FormatNumber([month_no];"00") ;"MM"))
BUt that gives an error in the version of Crystal report that I am using.
Hi,
If there is the possibility of showing multiple years then the first thing we need to do is determine the year.
You should create a group based on a formula like:
StringVar myYear;
myYear := ToNumber (Split ({table.FIELD}, " ") [2]);
If Length (myYear) = 4 and NumericText (myYear) Then
ToNumber (myYear);
Else 0;
I am assuming your values will always be separated by a space. What this formula does is builds an array using the space as a seperator. I'm using the second part of the array as the year and checking if it's 4 characters long and they are all numeric values. If they are then it converts the string to a number otherwise it returns a 0.
Group on this and you will get your years separated. Now create a second formula to parse out the month and format correctly, like:
StringVar myMonth;
myMonth := ToNumber (Split ({table.FIELD}, " ") [2]);
If NumericText (myMonth) Then
(If ToNumber (myMonth) In [1 to 12] Then
["June", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May"] [ToNumber (myMonth)];
Else "";)
Else "";
I created an array ordering the months in your fiscal year. The correct month should be spelled based on the numeric month from your field.
Hope this helps,
Brian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I will always have only 2 fiscal years previous fiscal year and current fiscal year. So I just created a formula that will display in yyyymm format, but if the date is 201112(year&month) , month is a 2 digit number, but if it is 20113 , then it displays month as a single digit number. Is there a way to display it as 201103 instead of 20113 and then supress the first 2 digits of the year?
Hi,
Your first formula you can change to:
StringVar myYear;
StringVar myMonth;
myYear := Split ({table.FIELD}, " ") [2];
myMonth := Split ({table.FIELD}, " ") [1];
If Length (myYear) = 4 and NumericText (myYear) Then
myYear;
Else myYear := "0";
myMonth := Split ({table.FIELD}, " ") [1];
If NumericText (myMonth) Then
(If ToNumber (myMonth) In [1 to 12] Then
myMonth
Else myMonth := "0";)
Else myMonth := "0";
((ToNumber (myYear) * 100) + ToNumber (myMonth);
Now this will return a number with the format you want, 201103.
Hi,
Yes, that should not be a problem, use the
StringVar myYear;
StringVar myMonth;
myYear := Split ({table.FIELD}, " ") [2];
myMonth := Split ({table.FIELD}, " ") [1];
If Length (myYear) = 4 and NumericText (myYear) Then
myYear;
Else myYear := "0";
myMonth := Split ({table.FIELD}, " ") [1];
If NumericText (myMonth) Then
(If ToNumber (myMonth) In [1 to 12] Then
myMonth
Else myMonth := "0";)
Else myMonth := "0";
((ToNumber (myYear) * 100) + ToNumber (myMonth);
to create the group,
then add say a max date field to the group, modify the field to make it display without the year.
So the below part is in one formula:
StringVar myYear;
StringVar myMonth;
myYear := Split ({table.FIELD}, " ") [2];
myMonth := Split ({table.FIELD}, " ") [1];
If Length (myYear) = 4 and NumericText (myYear) Then
myYear;
Else myYear := "0";
myMonth := Split ({table.FIELD}, " ") [1];
If NumericText (myMonth) Then
(If ToNumber (myMonth) In 1 to 12 Then
myMonth
Else myMonth := "0";)
Else myMonth := "0";
((ToNumber (myYear) * 100) + ToNumber (myMonth);
And then not sure where the group comes in::::
Can you pl. elaborate on ::
to create the group,
then add say a max date field to the group, modify the field to make it display without the year.
This is the formula for the new field. Pl. see below for error.
StringVar myYear;
StringVar myMonth;
myYear := Split ({query.fiscal_mth_nbr1}, " ") [2];
myMonth := Split ({query.fiscal_mth_nbr1}, " ") [1];
If Length (myYear) = 4 and NumericText (myYear) Then
myYear
Else myYear := "0";
myMonth := Split ({query.fiscal_mth_nbr1}, " ") [1];
If NumericText (myMonth) Then
(If ToNumber (myMonth) In 1 to 12 Then
myMonth
Else myMonth := "0";)
Else myMonth := "0";
(ToNumber (myYear) * 100) + ToNumber (myMonth);
ERROR:
a subscript must be between 1 and size of array
Split ({query.mth_nbr1}, " ") [2]
User | Count |
---|---|
89 | |
10 | |
9 | |
9 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.