Sql Server Return Grouped Data and Crystal Reports

Question

I have a SQL server query that I want to return all the type of orders in the system. Simple enough using:

SELECT OrderType.OrderTypeID
FROM Order
INNER JOIN OrderType ON OrderType.OrderTypeID = Order.OrderType
GROUP BY OrderType.OrderTypeID

Unfortunately with Crystal Reports, I want the ability to pass through a parameter such as RegionID (as multiple) and display only the Order Types in that region. To achieve this I must add Order.RegionID to the select statement (and therefore the Group By statement) resulting in duplicate Order Types.

Is there a way to use DISTINCT or grouping in crystal reports to get around this problem? Greatly appreciate any assistance.

Answer 1

Your menu options may vary, but you should be able to do something along the lines of:

• Find the "Database" menu option, and check the "Select Distinct Data" option

Answer 2

Use your existing query (with a DISTINCT added) to return all order types throughout the system, and a new query (similar to the first query, but with an additional selection on Region) to return all order types within a region.