Sort on different field from grouping field

[jotek]

I am trying to sort on a different field than the field in which I am grouping. I would like to group on Items but sort in their order date. So the whole report is in order date sequence with groupings of items within each date. I have changed the grouping to "on original order" and added an "ORDER BY" to my SQL statement. The data comes back in an original order but not in the sorted order. My grouping is a forumla and I also would not like to hard code the "order by" in the sql, since I do not want this particular order in other 'runs' of this report(I have a case statement in the Record Selection Forumla that changes the filtering.
I am using CR 8.5

 

[SmpTraining]

Sounds like you need two groups, the first should be on Order Date (with the group printed for each day) and the second group should be the Item.

Simply supress the display of the first group if you don't want to see the new headings for each day!

Steve Phillips, Crystal Consultant

 

[jotek]

This does not work correctly. The end result is that each item is summed per day. I do not want the report grouped per day, just sorted so that the items do get grouped together and summed once an item changes. So if the same items span consecutive days they are grouped together. ex:
7/15/01 1234 5
7/16/01 1234 1
1234 6
7/20/01 4444 3
4444 3
7/20/01 5555 8
5555 8
7/20/01 1234 7
1234 7

 

[kenhamady]

Try this. Group by Day, then group by Item, then suppress the Group 1 header and footer.

Now how is that different from what you have shown? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[jotek]

Still no luck. I started from scratch so it would be clean. But the end result is still the same. Suppressing the 'Day' (Date) grouping makes it so the report does not show the day grouping but it still groups the data by day so an item on two different days have two seperate grouping totals. As my example shows I want the item to have one total. Doing want you guys have suggested causes the item to group per day, so I get two totals instead of one.

 

[SmpTraining]

If you grouped by Item then Day, would you not get the correct results? Show an example of why this would not work please.

Steve Phillips, Crystal Consultant

 

[jotek]

Grouping by Item then by Day will first order the items in sorted order and then within each item, group them by day. (using the data example above item 1234 would have the 7/20/2001 record grouped with the other 1234 records). So this will not solve the problem. At this point I think I will go to the owner of the current report and see if they are willing to see item totals per day. I know they want to see this report sorted by date but I don't think it would kill them to see a total for an item on two seperate consecutive days. I greatly appericiate the quick reponses and feedback. I truely like this forum. If someone can come up with an answer I still would be interested, I have tried several things myself and need to move on.

 

[kenhamady]

Because 1234 is split and separated you ARE grouping by Date and then by product. Your exception is that you want a product total for consectutive dates.

But, that has some messy logic. For instance in your example what if 4 different products all sold on 3 consecutive dates. How could you combine these products without breaking the day sort? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[jotek]

Your first statement is correct. The Grouping is by Date and then by Product. And yes if the same product is sold on 3 consective days, the report is to show one total for that particular product. If a different product is sold within one of thos days then the end result would be 3 product totals. This could be solved if I could Sort on date and group by product within the date sequence. The reporting tool I am converting this report from allows me to do this, so I was just trying to do the same in CR.

 

[kenhamady]

I think you missed the point of my question. If 2 products are sold on day one, and both of them sell again on day 2 and then then both of them sell again on day 3. How would these 6 records be ordered?

7/15/01 1234 5
7/16/01 1234 5
7/17/01 1234 5

7/15/01 2345 3
7/16/01 2345 3
7/17/01 2345 3 Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[jotek]

Given your data the end result should be as follows:

7/15/01 1234 5
1234 5 <---grouping sum
7/15/01 2345 3
2345 3 <---grouping sum
7/16/01 1234 5
1234 5 <---grouping sum
7/16/01 2345 3
7/17/01 2345 3
2345 6 <---grouping sum

(I removed this entry from the data set to show a little difference: 7/17/01 1234 5)

 

[kenhamady]

R&R report writer allows you to sort without grouping, but the words mean different things. In R&R a Group didn't change the order of the records, just looked for a change. In CR it does.

In CR you can still accomplish this with 2 sorts (date then product). Then create a details B that is suppressed with the following condition:

Product = Next(Product)

On this put a running total that sums the quantity, and resets &quot;on change of product&quot;

This should do what you want. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[jotek]

This final suggestion work perfectly! Thanks
(I did have to add an OnLastRecord in order for the last total in detail B to not be suppressed).

Thanks again for your willingness to see this thru.