How to sort Corss-Tab by a specific column via Crystal Reports 11?

[Former Member]

Hi,

I have created a Cross-Tab in Crystal Reports 11.

I would like to sort this report by a specific column (the last column) within the Cross-Tab, e.g.:

2002 --- 2003 --- 2004 --- ...... --- 2010 --- 2011

Product1 ............................................. 2,120

Product2 ............................................. 1,306

Product3 ............................................. 3,535

......

......

......

I want to sort the data according to the values in the cloumn 2011. How should I do?

Thanks in advance.

Edited by: Holdup on Feb 3, 2012 5:59 PM

[abhilash_kumar · Accepted solution]

Hi,

You cannot sort a cross-tab based on one single column. It can be only be sorted on a summary value as a whole.

-Abhilash

[JWiseman · Accepted solution]

hi holdup,

if you're trying to sort the rows ascending or descending based on 2011 summaries only, you can do this using the following steps:

1) create a new formula with syntax like

if year({yourdatefield}) = year(currentdate) - 1
then {yourvaluefield}

2) go to the Cross Tab Expert and add the new formula as a Summarized Field

3) preview the Cross Tab and then right click on your Row header field > choose Row Options > Row Group Sort Expert

4) now do a Top N (ascending or descending) on the new formula

jamie

forgot one thing...you can now suppress the new cell in the cross-tab and try to make the size as small as possible.

Edited by: Jamie Wiseman on Feb 3, 2012 10:41 AM

[abhilash_kumar · Accepted solution]

Oh Yes Jamie's right, you can add 'another' summary and base the sort on the new one!

-Abhilash

[Former Member · Accepted solution]

Hi Jamie,

I try to use your method. But it can't work.

Because I use SQL Server 2005 database for reports, I set the data type of Year as integer.

So, I can't use the function year(). How can I do?

The other question is:

For step 2), you mentioned "go to the Cross Tab Expert and add the new formula as a Summarized Field".

I already put a field in "Summarized Fields", I should keep the old one as well as the new formula, right?

Thanks.

Edited by: Holdup on Feb 9, 2012 6:27 PM

[Former Member · Accepted solution]

Need help ...

Edited by: Holdup on Feb 9, 2012 10:44 PM

[abhilash_kumar · Accepted solution]

Hi,

If the year is defined as Integer then you just need to use this:

*if = year(currentdate) - 1

then *

And, yes need to keep the field along with this formula field in the Summarized field.

-Abhilash

[Former Member · Accepted solution]

Good to know.

Thank you.