CR8, Crosstab, Row Header: Display one value, sort by another but do not display

(continuation of

Hello all

I have a crosstab with row headers in YYYYMM format (chr(6)), and via the formula displaying them as Aug 05, Sep 05, etc.

The only problem is, it is sorting the rows in alphabetical order, i.e. Apr 05, Apr 06, Aug 05, Aug 06, etc.

How to display the month in Apr 05 format, but keep sort order of columns in 200504 format (so it goes Apr 05, May 05, Jun 05, etc)?

LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?

I think you need to change your row field to the original field so that they sort properly.
Once you do that in the crosstab expert, highlight the new row field and click the group options button below the row box.
In the group options, go to the options tab, then select customize group name field, and choose the formula that reformats the value as the group name field.
This will allow things to be sorted by the original field, but the formula will be displayed.
Make sense?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
I was able to follow the instructions (other than in CR8 in Group Options, there is not a separate Options tab, but the controls you mentioned all existed on a Common tab), but was not able to get the formula value to display correctly when entered in the 'Group Name Formula Editor' as {formula_name};
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Disregard my first comment, I had a later formula in there.  Your comment as posted worked great.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.