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 67
Jim HornMicrosoft SQL Server Data DudeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeAuthor 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 Data DudeAuthor Commented:
Disregard my first comment, I had a later formula in there.  Your comment as posted worked great.  Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.