Crystal Reports Sorting on Minimum field

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Sorting on Minimum field

Author	Message
LarryM Newbie Joined: 14 Jun 2008 Location: United States Online Status: Offline Posts: 8	Topic: Sorting on Minimum field Posted: 09 Apr 2009 at 6:39pm
	I am trying to put a minimum field value in ascending order when in a group. I just wondered if someone could help me with this. Thanks
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 10 Apr 2009 at 7:49am
	Not exactly sure what you mean by the "minimum field value"... I am guessing you just want the least value to the highest value and are not actually using a 'minimum' code in a formula. Sorting is done first at the group level. From there you can sort the detail rows based on any field or combination of fields. Just click on the "record Sort Expert" button. You will then see your all of your data fields. Double click on the one you want to sort by and it will move into the Sort Fields window under your group field that defaults into the window. Choose Ascending to make your minimum values start your sort. If you have a combination of values, say a date field and then an amount and you want the date to stay together but then sort min to max on the values within those dates, add the date field as ascending then the amount field as ascending and it will sort first by date then the amount values within the dates. Hope this helps. If not, please explain further the exact nature of what you are needing.
	IP Logged

LarryM Newbie Joined: 14 Jun 2008 Location: United States Online Status: Offline Posts: 8	Posted: 11 Apr 2009 at 7:41am
	This is what I am trying to do. I have 15 contracts and each contract have work in process. There are 35 different sources in work in process. I group the contract together then I take the minimum work in process because you can have over 200 job in work in process per contract so at means that I could have over 200 minimum work in process. Some of the work in process are just starting some are almost finished. So I wanted to sort within the contract the work in process in acending order so I would know that I have 15 job starting the Eval process and list all 15 jobs that are at that phase. and do this for each phase per contract. example ABC Contract Job No. WIP Phase Part ID Custom Name 321525 Eval 321321 Jim Smith 321498 Paint 456441 Jon Jones 317822 Eval 112877 Casey Jones 369811 Balance 871243 Howard Cassey This is the way I would like to see it down below. ABC Contract Job No. WIP Phase Part ID Custom Name 369811 Balance 871243 Howard Cassey 317822 Eval 112877 Casey Jones 321525 Eval 321321 Jim Smith 321498 Paint 456441 Jon Jones
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Apr 2009 at 10:35am
	By grouping on the contract that is defaulted to ascending and will keep all of your contract data togther. You could add a second group on the "WIP phase" to keep all of those together if you want but it is not necessary, just an option. Based on your sample I would add a primary sort on WIP Phase as ascending, a secondary sort on the Job No as ascending and that should make your example come out the way you want it. What I am not sure about is if you need to suppress any items as you did not mention this. Again, if this does not do what you need it to, feel free to post examples of what is not working (how it looks based on the above sorting) and what you want it to look like in the end. Hope this takes care of it though Edited by DBlank - 11 Apr 2009 at 10:37am
	IP Logged

LarryM Newbie Joined: 14 Jun 2008 Location: United States Online Status: Offline Posts: 8	Posted: 11 Apr 2009 at 3:13pm
	On the first grouping I have the Contract The second grouping I have the Job Number but the problem I am having is that I am only want one detail one per Job number, so I take the field as minimum of WIP Phase and the put the Wip Phase in ascending order. When I do that the report goes crazy. It tries to do all of the detail lines also which I do not want. I just want the min line from each job number and then put then phase in order, so at that time the job number will be cout of order because of their phases are not the same. example ABC Contract Job No. WIP Phase Part ID Custom Name 321525 Eval 321321 Jim Smith 200 Eval 90-123891 04/1/2009 04/15/2009 4/21/2009 300 Materials 90-123891 04/1/2009 04/15/2009 4/21/2009 400 PaintPrep 90-123891 04/1/2009 04/15/2009 4/21/2009 450 Paint 90-123891 04/1/2009 04/15/2009 4/21/2009 500 Crate 90-123891 04/1/2009 04/15/2009 4/21/2009 550 Balance 90-123891 04/1/2009 04/15/2009 4/21/2009 610 Shipping 90-123891 04/1/2009 04/15/2009 4/21/2009 321498 Paint 456441 Jon Jones 450 Paint 90-123921 3/21/2009 4/12/2009 4/15/2009 500 Crate 90-123921 3/21/2009 4/12/2009 4/15/2009 550 Balance 90-123921 3/21/2009 4/12/2009 4/15/2009 610 Shipping 90-123921 3/21/2009 4/12/2009 4/15/2009 317822 Eval 112877 Casey Jones 200 Eval 90-123892 04/11/2009 04/15/2009 4/21/2009 300 Materials 90-123892 04/11/2009 04/15/2009 4/21/2009 400 PaintPrep 90-123892 04/11/2009 04/15/2009 4/21/2009 450 Paint 90-123892 04/11/2009 04/15/2009 4/21/2009 500 Crate 90-123892 04/11/2009 04/15/2009 4/21/2009 550 Balance 90-123892 04/11/2009 04/15/2009 4/21/2009 610 Shipping 90-123892 04/11/2009 04/15/2009 4/21/2009 369811 Balance 871243 Howard Cassey 550 Balance 90-123712 04/9/2009 04/15/2009 4/21/2009 610 Shipping 90-123712 04/9/2009 04/15/2009 4/21/2009 This is the way I would like to see it down below. ABC Contract Job No. WIP Phase Part ID Custom Name 369811 Balance 871243 Howard Cassey 317822 Eval 112877 Casey Jones 321525 Eval 321321 Jim Smith 321498 Paint 456441 Jon Jones Edited by LarryM - 11 Apr 2009 at 3:13pm
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Apr 2009 at 4:53pm
	The best solution may not be possible. I would handle this in a SQL view or stored procedure to get your minimum values then use that to join back into the report to exclude the extra data. This would allow you to sort it the way you want it. You are in kind if a bind because you need a minimum value of a group which will be your primary sort but you you want the primary sort to be by WIP phase. Can you create a view to remove the extra data? If not I'll give this some more thought. Anyone else have a solution?
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum