Sort by Colum Value
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10944
Printed Date: 07 May 2024 at 1:27am
Topic: Sort by Colum Value
Posted By: verniqueb
Subject: Sort by Colum Value
Date Posted: 24 Aug 2010 at 8:39am
|
Is it possible to sort crosstab report by colum with highest value?
For example my report is displayed by region, State, by product type. I would like to sort on the state with highest balance value.
So each time the report is ran and New York has the hight balance value I would like New York Colum first within it's region.
If Texas balance is highest the next time the report is ran I would like to see that colum first.
Any ideas how to do this?
Thanks in advance. ------------- VB |
Replies:
Posted By: Emir_W
Date Posted: 25 Aug 2010 at 12:21am
|
when you create a crosstab for columns, there is 'Group Options...' button under 'Columns:' box. try to use this function. (maybe you'll need to create formula to set as ColumnName). hope it help. ------------- Emir W |
Posted By: verniqueb
Date Posted: 25 Aug 2010 at 12:38am
|
I apologize but my crystal skill are not advance...i don't see the option you are referring to. How to i get my cross-tab for colums? Can you give me an example of how the formula should be written? ------------- VB |
Posted By: djwessels
Date Posted: 25 Aug 2010 at 8:43am
|
I have not found any way that the Group Options can be set to sort on an individual column or row. It will sort on overall totals, but not on values in one particular row or column.
I am new to Crystal Reports, using CR 2008. Has anyone had any success with doing this?
Thanks! ------------- Many thanks, DJW 
|
Posted By: verniqueb
Date Posted: 25 Aug 2010 at 12:00pm
|
I created a new calculated field called header and added this field to my cross-tab columns. The calculation on this field is:
Sum ({Balance}, {Location}). The location with the lowest amount floats to the first colum of the report. If another location has the lowest balance on the next run that location moves to the first colum.
Now I need to figure out how to show the location name instead of the calculation. ------------- VB |
Posted By: Emir_W
Date Posted: 25 Aug 2010 at 10:11pm
|
here is what i've done. crosstab condition: - Row : item desc - Column : location desc - Detail : qty on hand till here, it gives me standard/normal crosstab (where the QtyOnHand is not sorted). here is to sort the QtyOnHnd: 1. right click crosstab 2. select 'Cross-Tab Expert...' 3. click on field in Columns section ({tbl.QtyOnHnd}) 4. click 'Group Options...' button 5. sort by {tbl.QtyOnHnd} and set as 'Descending' 6. click on 'Options' tab 7. check for 'Customize Group Name Field' 8. select for {tbl.LocDesc} in order to print the Location name hope it help. ------------- Emir W |
Posted By: djwessels
Date Posted: 26 Aug 2010 at 3:52am
|
Thanks to both of you - I'll try your suggestions and see what I come up with.
At the moment, I've been pulled onto another project... of course.
I'll get back with you on how it goes.
Thanks again!
------------- Many thanks, DJW
|