Report Design: Sorting Group Data

Print Page | Close Window

Sorting Group Data

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5096
Printed Date: 06 May 2024 at 5:42pm

Topic: Sorting Group Data

Posted By: hdjim69
Subject: Sorting Group Data
Date Posted: 29 Dec 2008 at 5:30am

Hello, I’m following the book CR Encyclopedia Pro XI Reports and just got done creating the Group By Intervals.rpt. I have the groups Less then $10,000, $10,000 to $25,000 & Greater Then $25,000 setup and the report is working. Now I want to go and SORT the detail records from highest to lowest amounts within each group. However when I go into Report Sort Expert to select Customer.Last Year Sales it is grayed out! Is this because it’s used in the Group By? If so, how do I sort by this field within the detail section?

TIA

Replies:

Posted By: lockwelle
Date Posted: 29 Dec 2008 at 6:15am

I would create another group, inside of the interval, and set the sort order there.

Hope this helps

Posted By: hdjim69
Date Posted: 29 Dec 2008 at 6:26am

>I would create another group, inside of the interval, and set the sort order there.

You mean a group within a group? I don't really want to group the data within the group, I just want to sort the detail records but the field I need to perform the sort is grayed out.

the question is, if you use a field to group, can you also perform a detail sort on that field?

Posted By: lockwelle
Date Posted: 29 Dec 2008 at 6:34am

I don't know about the sorting, since when I need to sort, I create a group. My groups can be complex, and in many cases are handled by formulas for dynamic grouping.

When you think about it, grouping is just a way to order things, as it places them in an order that crystal can look at to break on.

I guess the complete instructions would be to create a group within the group based on the field to sort on. Then suppress the header and footer, and the report will appear to have sorted the data as desired. You don't have to see or use the header/footer info. You really ONLY care about how it looks.

While I realize that the answer may not be the one that you are initially seeking, it is a solution to the issue at hand. I would thought that you could, but if it is grayed out...maybe not.

Posted By: hdjim69
Date Posted: 29 Dec 2008 at 9:31am

>When you think about it, grouping is just a way to order things, as it places them in an order that crystal can look at to break on.

not sure that is the case or the rows would be already ordered. They are grouped by dollar amount so a record fits within it's correct range but within that range of rows there is no order. Your solution may work but it seems like a hack. there should be a way to order the records within a group based on the same field you used to group the records.

Posted By: DBlank
Date Posted: 29 Dec 2008 at 11:02am

You can also create a forluma field = to the field you want to sort on and then sort on the formula field rather than the original field

Posted By: hdjim69
Date Posted: 29 Dec 2008 at 11:22am

I looked for a Sort function but there is none that I can see. please explain how to do what you suggested.

Thx

Posted By: DBlank
Date Posted: 29 Dec 2008 at 11:27am

Create a formula field called something like "Last_Year_Sales _Sort" as

"Customer.Last Year Sales"

Go to the Record Sort Expert (the AtoZ button next to the select Expert).

Under Report Fields their should now be a field called "X-1 Last_Year_Sales _Sort". Click on it and then click on the over arrow to move it to the sort fields box. Under the Sort fields box you can choose Ascending or Descending.

Posted By: hdjim69
Date Posted: 29 Dec 2008 at 12:11pm

when i drag the sql field down into the main section of the formula expert i keep getting an error - the results must be a boolean. I guess i need to read on, I'm not at the point in the book where we create formulas yet...:)

Posted By: hdjim69
Date Posted: 29 Dec 2008 at 12:27pm

actually I figured out how to do it. Not sure why it's this way but it works. i went back and delted the group in the Group Expert. Then I went back to Record Sort Expert and selected the report field Customer.Last Year's Sales and it was not grayed out now since I deleted the group that was using it. So I added this to the Sort Fields FIRST this time. Then I went back and created the groups in the Group Expert - Change Group Options and it's now sorting within each group on Customer.Last Year's Sales FIELD. So it seems like you need to add the sort fields before you use them in a group by or that field will be grayed out.

Posted By: DBlank
Date Posted: 29 Dec 2008 at 12:30pm

Just in case for the future you can do what i was suggesting...

Don't use a SQL Expression field use a Formula Field (the one with the "x-1" icon).

Right click on it and select New.

When the Name box pops up you can put whatever but for now just use Last_Year_Sales_Sort.

Hit OK.

In the formula workshop find your Customers table and double click on the Last Year Sales field to bring it into the formula.

Click on save and close. All you did here was create a field that is exactly the same as the Last Year Sales field from your table. Nothing more.

You do not even have to put it into your report to sort by it but you can if you want to see what the field is doing.

from this point follow the last post ...

Go to the Record Sort Expert (the AtoZ button next to the select Expert).

Posted By: hdjim69
Date Posted: 30 Dec 2008 at 4:38am

what's the starting point? that is, what screen do I start off by clicking the formula button to create this formula?

TIA

Posted By: DBlank
Date Posted: 30 Dec 2008 at 7:16am

Inside your report design, in your Field Explorer box you have your Database Fields, then Formula Fields then SQL Expression Fields, etc.

Right Click on the Formula Fields and select New to create a Formula Field.

Print Page | Close Window