Crystal Reports sort by subreport

Question

So I have a series of orders that come in everyday. We add them to a database and then use Crystal Reports make packing sheets of these orders. We have two tables one for order information(name, address, order number, etc) and one for the items. In Crystal Report we use the order for the report and then do a subreport with all the item associated with order number. Now I have been given the requirement that we need to all the orders sorted by the first item's item number. How do I sort a report by a field in a subreport?

I am unsure what you are asking. I presume at the moment the packing sheets are produced ordered by the Order number. Are you saying you want to ignore the order numbers and just base it from the Product/item Code (for example). So then you would get a jumbled order of Order numbers, but an ordered list of Product/Item codes? Or do you want it still in order of Order Number, but then have the data in the sub reports ordered by Product/Item Code? — vice

craig craig · Accepted Answer · 2011-11-22T16:29:23

First of all, you can accomplish this report w/o a subreport. The report would be grouped by order # w/ the order fields in the group header. The Details section would contain the order-items.

In any case (my approach or yours), if you just need to sort the order-items by item number, simply reference that field in the Record Sort Expert.

If you need to sort the orders by each order's order-items[0] (the first item in the list), you'll need to create a SQL Expression that returns the item #. If you add a group on the SQL Expression field, then make that the top-level group (G1), the report will group first by the SQL Expression, then by the Order #. The SQL Expression should resemble:

(
//this will select the numerically-smallest item# for a given order#
SELECT min(item #)
FROM  order-items
//correlate to 'main' query
WHERE order#=order.order#
)

If you need more complex rules for 'first item #' (whatever that means), you need to include it in the SQL Expression.

Crystal Reports sort by subreport

1 Answers