Date for cross Tab

[smokie07]

I have created a cross Tab with CR 8.5 from an access DB. The columns are for each QTR and the rows count a number of requests for each Department. On the columns I get an actual Date. I want "1st Qtr yyyy", and so on. I've tried many things none work. Does anyone have any suggestions?

Thanks!

 

[lbass]

You should be able to change the name by selecting the crosstab->format crosstab and selecting the column field. Then click on Group Options and check customize group name field--BUT this doesn't work, I discovered, as I guess you did. The datatype remains the same even if you create a string name formula, so the custom name doesn't display correctly.

So, instead create a formula {@qtr} to use instead of the date group you used to define your column.

if month({Orders.Ship Date}) in 7 to 9 then "1st Qtr " + totext(year({Orders.Ship Date}),0,"&quot else
if month({Orders.Ship Date}) in 10 to 12 then "2nd Qtr " + totext(year({Orders.Ship Date}),0,"&quot else
if month({Orders.Ship Date}) in 1 to 3 then "3rd Qtr " + totext(year({Orders.Ship Date}),0,"&quot else
if month({Orders.Ship Date}) in 4 to 6 then "4th Qtr " + totext(year({Orders.Ship Date}),0,"&quot

Adjust this to the calendar year, if that's how your fiscal or report year works. There is a calendar quarter function you could try also.

Once you have entered {@qtr} as your column name, you will find that it displays the column order based on the string values, so you will need to go to format crosstab->select the column name->group options and choose specifed order. The drop down box will contain the column heading values to use for ordering--just add them in the order you wish.

-LB

 

[smokie07]

It did not work, I get "2nd QTR" on all my Column headings

 

[lbass]

I tested this, and it worked, so please let us know what you did step by step and also provide the formula that you used.

-LB

 

[smokie07]

Ok, (I'm Using Ver 9.2 not 8.5)(I have 3 different PC's with 3 different Versions)

CT: Selecting Date_Recieved for Column(For each Qtr)
Selecting Department For Rows(Count of)

Looks Like this:
10/2002 1/2003 4/2003 Total
Atlanta 5 6 7 18
Baltimore 8 9 10 27

I then went to Format field, Common Tab,Went to display String and entered your formula. I get 2nd QTR instead of the dates

Hope you can help

 

[smokie07]

Got it, I did what you said to the letter(Almost).

Thanks