Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

LT2

MIS
May 24, 2006
232
US
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?
 
Prepare the cross-tab in Access using TRANSPOSE and use it as a regular table in crystal reports

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.
 
I would just prefer to put in a formula for the column display in Crystal if possible.
 
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
 
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


 
The formula for {@month} should be:

month({table.date})

-LB
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top