| Author |
Message |
Barbara
Newbie
Joined: 31 Dec 2013
Location: United States
Online Status: Offline
Posts: 5
|
   Topic: Group Sorting Expert Top N results for two columns
Posted: 12 Mar 2014 at 5:41am |
I have a report listing clients by numbers (client.clinum), and names (client.cliname). When I choose Top N sort on the client.clinum field and select top n = 25 (sum of dollars field) with the option to show Others, it works on that column (field), however the field client.cliname shows 26 client names the last of which is next to the Others name.
1 ABC Company $100
2 DEF Company $200
3 GHI Company $300
4 JKL Company $400
25 TUV Company $500
Others XYZ Company $15,000
XYZ Company should not be showing.
Top N is sorting on the dollars field. Is there anything I can do?
Thanks for your help!
Edited by Barbara - 12 Mar 2014 at 6:16am
|
 IP Logged |
|
|
lolly54
Groupie
Joined: 25 Sep 2011
Online Status: Offline
Posts: 58
|
Posted: 12 Mar 2014 at 5:59am |
|
When you select the Top N field, it should have this other check box asking you if you would like to include Others, with the name: [Others]
If you only want to show the Top 25, un-tick the include Others selection.
It should work! :)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Mar 2014 at 6:09am |
in the group sort expert you can set the 'N'
as well as was uncheck a box that has 'inlcude others with the name...'
EDIt: sorry lolly, must have been typing when you were posting 
Edited by DBlank - 12 Mar 2014 at 6:10am
|
|
IP Logged |
|
Barbara
Newbie
Joined: 31 Dec 2013
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 12 Mar 2014 at 6:15am |
Thank you for your response.
I have the Others box checked because I do want to see that, however, it does not show "Others" in the client name field but an actual client name where it should not be. I want to see:
Others ____________ (blank) $15,000
I'm currently seeing:
Others XYZ Company $15,000 The amount is referring to the Others reference so the client name is not accurate.
I've tried to select top n on both underlined fields but it only chooses the first one. I can't seem to get that 26th record to not show the client name.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Mar 2014 at 7:55am |
are you using the DB field placed in the header or footer and not the Group Name field?
Edited by DBlank - 12 Mar 2014 at 7:56am
|
|
IP Logged |
|
Barbara
Newbie
Joined: 31 Dec 2013
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 12 Mar 2014 at 8:11am |
|
Not sure what you are asking. I have 3 groups, timekeeper (group #1), client number (sorted by top n) (group#2), and client name (group 3). The top n sort is based on a summary field of timekeeper hours located in the group 2 header. All db fields are in a group header or footer, not a page or report header or footer. How would I use the group name field to do the sort vs. the db field?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Mar 2014 at 9:02am |
it is more of an issue of display than grouping.
WHen you group it insert the group as a primary sort and DB field can then be used to sort rows inside each group.
YOu can sort the group using the group sort expert and a summarized field for the group (like the top n you are using).
When you insert a group it creates a group name field and places it in the group header. You can also see these in the Field Explorer under the "Group Name Fields" node.
The group sort expert uses the Group Names fields to diaplsy 'Other' (on the 26th grouping in your case). If you are using the DB field to display the
company it will not use 'Other'. Look in your group headers and see the "group #2 name". That field will show Other on 26th instance of that grouping.
|
|
IP Logged |
|
Barbara
Newbie
Joined: 31 Dec 2013
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 13 Mar 2014 at 4:10am |
Thank you for clarifying. That is good to know and makes sense. I was using the group name field for the client numbers (and Others was showing as result 26), and was using the db field for the client name. I've changed to the client name group name field but unfortunately it is still showing a 26th record with a name when it should be blank or say "Others". Crystal seems to ignore the secondary sort request. Thinking it may be something simple I am missing...
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Mar 2014 at 6:09am |
I assume you are gouping on the ID and then the name because it is possible to have two names with different ID's.
Either add a concatenated formula field as name + id and group on that instead of each seperately and then use that group header for the display
or
move your sum to grouip level 3 the group 3 name to dispaly
or
right click on the field you want to display the names on
select format field
select common tab
select the display string formula
use this formula
if groupnumber=26 then 'Others' else table.clientnamefield
Edited by DBlank - 13 Mar 2014 at 6:10am
|
|
IP Logged |
|
Barbara
Newbie
Joined: 31 Dec 2013
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 13 Mar 2014 at 6:47am |
|
Thank you so much for your patience and expertise. Finally got this bugger to work. :)
|
|
IP Logged |
|
|
|
