Sorting Groups - Problem with non-distinct values

[Khelben]

I have a problem with sorting and grouping.&nbsp;&nbsp;I have a report with a group defined.&nbsp;&nbsp;Consider that there are two different fields I would like to sort by.&nbsp;&nbsp;One is a unique text string, lets call it &quot;Name.&quot;&nbsp;&nbsp;The other is not unique and is a number, let's call it &quot;Value.&quot;<br><br>Right clicking on the group, and selecting &quot;Change Group...&quot; I'm presented with the choice of which field to sort the group by.&nbsp;&nbsp;When I choose &quot;Name,&quot; the group sorts fine, and all the records appear properly ordered.&nbsp;&nbsp;Like so:<br><br><FONT FACE=monospace><br>Fred&nbsp;&nbsp;&nbsp;&nbsp;5<br>Joan&nbsp;&nbsp;&nbsp;&nbsp;2<br>John&nbsp;&nbsp;&nbsp;&nbsp;5<br>Lisa&nbsp;&nbsp;&nbsp;&nbsp;1<br>Mary&nbsp;&nbsp;&nbsp;&nbsp;2<br></font><br><br>When I choose &quot;Value,&quot; however, I get only some of the records, because it only shows one of each of the numeric values.&nbsp;&nbsp;That is, several different Names have the same Value, but when sorting by Value, it only shows one record for each value, even though the Values aren't neccessarily distinct. For the same data as above, I'd only get:<br><br><FONT FACE=monospace><br>Lisa&nbsp;&nbsp;&nbsp;&nbsp;1<br>Joan&nbsp;&nbsp;&nbsp;&nbsp;2<br>Fred&nbsp;&nbsp;&nbsp;&nbsp;5<br></font><br><br>What I want, however, is something like this:<br><br><FONT FACE=monospace><br>Lisa&nbsp;&nbsp;&nbsp;&nbsp;1<br>Joan&nbsp;&nbsp;&nbsp;&nbsp;2<br>Mary&nbsp;&nbsp;&nbsp;&nbsp;2<br>Fred&nbsp;&nbsp;&nbsp;&nbsp;5<br>John&nbsp;&nbsp;&nbsp;&nbsp;5<br></font><br><br>How can I sort the group by Value (recall, a non-distinct numeric value) but still have it display all the records?&nbsp;&nbsp;I notice that when I sort &quot;in original order&quot; I see all the records, but when using &quot;ascending&quot; or &quot;descending&quot; I get only one record per Value.&nbsp;&nbsp;&quot;In original order&quot; doesn't do me any good, though, since the data isn't sorted to begin with.&nbsp;&nbsp;I understand that it displays the group for each change of value of the specified field, but is there a way to get it to display the group for every record, no matter what?<br><br>Thank you sincerely for any advice you can offer me.&nbsp;&nbsp;

 

[MalcolmW]

Check out Report¦Sort Records - that should do what you want, which is sorting, not grouping, of records. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Khelben]

I'm afraid I may have oversimplified my example.&nbsp;&nbsp;I'm trying to explain what I need without including all of the irrelevant details of my particlular report and I probably haven't provided an accurate representation.<br><br>Let me explain further.&nbsp;&nbsp;The grouping is, in fact, neccessary.&nbsp;&nbsp;There is a hierarchy of groups in my report required to display the data properly and I wish to sort one of them in this particular way.<br><br>My database source has two tables of interest.&nbsp;&nbsp;One we'll call Name which contains distinct Names and their associated Values.&nbsp;&nbsp;The other we'll call Sub-Name and contains distinct Sub-Names and their associated Sub-Values.<br>The relationship between Names and Sub-Names is one-to-many.<br><br>Group1 of my report is defined by the Name.&nbsp;&nbsp;Group2 is defined by the Sub-Name.&nbsp;&nbsp;This is about what it looks like:<br><br><FONT FACE=monospace><br>Name1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Value1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Value2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Value3<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Name1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Value1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Value2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Name1.2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Value1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Value2<br><br>Name2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Value2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Value2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Value3<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Name2.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Value1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Value2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Name2.2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Value1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub-Value2<br></font><br><br>The grouping, as you see, is needed to properly display, below each Name, the Sub-Names that are associated with it.<br><br>Now, what I'd like to be able to do is sort Group1 by one of the Values (as opposed to the Name).&nbsp;&nbsp;This works perfectly if the Value field I choose, say Value1, happens to conatin distinct data.&nbsp;&nbsp;Every Name is displayed, with it's list of Sub-Names, in the order of increasing/decreasing Value1.<br><br>However, when the Value field I choose, say Value2, happens to contain non-distinct data, I have a problem.&nbsp;&nbsp;Let's take Value2 = 5 as a concrete example.&nbsp;&nbsp;Only one Name will be displayed with Value2 = 5, even if several exist (the others never show up).&nbsp;&nbsp;Although this Name will be properly listed after a Name with Value2 = 4 and before a Name with Value2 = 6, it will only display the first Value2 = 5 Name it comes across.<br><br>So, the question remains, how <i>within a group</i> can I sort on a field that contains values which are not distinct and still display all of the records?<br><br>Thanks for your input.&nbsp;&nbsp;Hopefully I have now constructed a more complete picture of the problem and someone may have some more advice for me.&nbsp;&nbsp;Any ideas you have to offer are appreciated.&nbsp;&nbsp;Pleas help...I'm stumped!

 

[DanR]

I was searching old posts, and came aross this post that describes my issue exactly. Will Crystal handle sorting groups as described in the original post.

Thanks for the help.

 

[Khelben]

I still haven't heard a satifactory solution to this problem. Crystal 8 is out now...maybe it can do it, but I wouldn't know.

 

[Ngolem]

try creating a group formula...you can use a parameter to determine how the grouping is to take place

{?ParamSort}
type: string
description: type &quot;N&quot; for Name/Value or &quot;V&quot; for Value/Name
Default: &quot;N&quot;

{@grouping}

If uppercase({?ParamSort}) = &quot;V&quot; then
Totext({Table.Value1} + &quot;/&quot; + {Table.Name1}
else
{Table.Name1} + &quot;/&quot; + Totext({Table.Value1} //the default

Now group on this formula...that should organize things for you

You can create another grouping formula for the Sub values as well...using the same parameter if you want or creating a new one.

Hope this helps

Jim