Name of Month in Cross-tab to display in certain order

[LT2]

Hello,

Using CRXIr2 w/Access 2003 and have the following cross-tab:

Row @Empl (If {Enter_Issues.EmployeeID}=2 then 'Abc' else '?')
Column @Month(MonthName(DatePart("m"{Enter_Issues.BeginDate}))
Sum Field Enter_Issues.Total

I'd like to display the columns like our Fiscal Year starting with
Oct and ending with Sept.

How can I accomplish this?

 

[RTag]

Prepare the cross-tab in Access using TRANSPOSE and use it as a regular table in crystal reports

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[LT2]

I would just prefer to put in a formula for the column display in Crystal if possible.

 

[lbass]

You can use specified order in group options. Be sure to set up the specified order initially using a full year of data.

Otherwise, you could add fiscal year as an outer group based on a formula like this:

"FY "+totext(year({table.date}+92),"0000")

Then add the months using the month(). In group options, go to customize groupname->use a formula and enter:

monthname({@month},true) //"true" for 3-char monthname

-LB

 

[LT2]

Okay, I created @FY= "FY "+totext(year({table.date}+92),"0000") and placed in the column of the cross-tab and then placed
monthname({@month},true) //"true" for 3-char monthname in the customized groupname formula but I can't save the formula because CRXI is complaining the {@Month} should be a number.
~LT

 

[lbass]

The formula for {@month} should be:

month({table.date})

-LB

 

[crystalkiwibruce]

In the cross tab group by the date field, group option by month, and then display the column field as MMM-yy

Or use display string formatting option to display the month name in the format you want

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[LT2]

LB,

I changed the @month formula and now the display shows just Oct. I have Oct, Nov and Dec data (1stQtr) that should be displaying.

How do we get the rest of the months to follow?
~LT

 

[lbass]

You have to change the display by selecting the column field (in the crosstab expert->group options->options tab->customize group name->use a formula->x+2 and enter it there.

Bruce selected an easier approach--try that.

-LB

 

[LT2]

Unfortunately your suggestions weren't working for me and I wasn't understanding how I was going to achieve the results I was looking for. A friend of mine did give me a solution and I'd like to share it with you in hope that I'm not offending anyone.

@Month
global numbervar FYMonth;
FYMonth:=(month({tabledate});
IF FYMonth<10 THEN FYMonth:=FYMonth+12;
FYMonth;

In the format field of the column
CSS Class Name: the following formula
global stingvar FYMonthName;
global numbervar FYMonth;
FYMonth:=currentfieldvalue;
IF FYMonth>12 THEN FYMonth:=FYMonth-12;
FYMonthName:=monthname(FYMonth,true); //"true" for 3-char monthname
"";

In the format field of the colum
Display String: the following formula
global stringvar FYMonthName;
FYMonthName;

End result I have a cross-tab with @Empl as the row, @Month as column and Sum of Enter_Issues.TotalTimeSpent

This displays the emloyee's time spent by month in our fiscal year beginning with October and ending in September.
~LT