Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conditional Grouping 1

Status
Not open for further replies.

icrf

Programmer
Dec 4, 2001
1,300
US
CR.NET / VB.NET / SQL Server

I have a joined table of product information supplied by a stored procedure, relevant fields being OrderID and ProductID. I have a group for each OrderID so the header info for each order gets displayed on each page with all the items with that OrderID displayed in the details section. This is the packing slip that goes with each order when shipped to the customer.

The bulk of the orders only have one item in them, so the production department needs to have these orders sorted by ProductID so when they package, they can go through X orders in a row that all have the same product.

When I group by OrderID maintaining the original sorted order (product count per order then ProductID), if the items returned are not sequential (ProductID's of 2 and 6 on one order and 4 and 7 on another), all each order's items don't get put in the same group. If I set the group to sort ascending, then the single item orders are no longer grouped (well, sorted) by ProductID. I tried to group in specified order, but the only field I can use as a conditional is the one I'm grouping by, which is not the group I want to sort by.

Am I making any sense? This is very frustrating because is seems so simple in my head but I can't find a way to do it.

________________________________________
Andrew
 
If your goal is to get the orders with only one item first, and then organize them so that they are ordered by product, then you might try removing your sort fields, and then inserting the following summaries at the item group level by right clicking on the relevant field->insert summary:
minimum of {table.ProductID}, count of {table.ProductID}, sum of {table.quantity}. Then go to report->topN/sort group and choose these same fields for the topN: minimum of {table.ProductID}, count of {table.ProductID}, sum of {table.quantity--each in ascending order. This should cluster product groups more usefully, I think, although you might want to play with which summary to prioritize in the topN.

-LB
 
I don't quite follow, but I'm still pretty new to CR and don't have it in front of me right now. I'll try once I get into work.

The issue is still ordering by Count(OrderID) then ProductID causes the products for a single OrderID (if it has more than one) to be out of order. All orders with 2 products will be clustered, but the two products for a single order may not be, and when they're not, the grouping on OrderID doesn't get both of them.

Again, I haven't tried your suggestion yet, but I don't see how it addresses that.

Thank you very much for your time, it is appreciated.

________________________________________
Andrew
 
Well, it looks like it works. I sort from the stored procedure on OrderID to make sure all items for an order are clustered. Then in the report, I have summary fields for Count(OrderID) and Min(ProductID) and I sort ascending on each key in that order. Initial testing looks good, so we'll see.

I'm not entirely sure how it works, but it does. Definitely worth a star. Thanks for your help.

________________________________________
Andrew
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top