Crystal report - Group Towns by Countries

Question

I have two tables

With this query i got the results

select countries.id, countries.name, Towns.id,Towns.TownName
from
Countries
left outer join Towns on countries.id = towns.countryID

Now i want to group results by country to get something like this on my CrystalReport. Is it even possible to make it ?

Answer 1

In the example you have provided, doing a GROUP BY in Sql Server would not be desirable. A Sql GROUP BY is generally used to project single scalar values from a list in each group, e.g.

SELECT Countries.Name, Count(Towns.Id) AS NumberOfTowns, Sum(Towns.Population) AS TotalPop
FROM Countries
   LEFT OUTER JOIN Towns 
   ON Countries.Id = Towns.CountryID
GROUP BY Countries.Name;

Would give results like

Name         NumberOfTowns            TotalPop
Bosnia       2                        123456
England      2                        98765

by applying the Aggregates COUNT and SUM to all rows (Towns) in each country.

This isn't going to be useful for your report, as you need to show a list of all towns per country.

What you want instead is to use your current query as-is, and then to apply a Crystal Group Header on Countries.Name. Then, in Crystal, remove the Countries.Name field from the Details section (since you don't want the country repeated). You'll possibly also want some ordering done in the groups and data - again, I would suggest you do this in Crystal (although an ORDER BY Countries.Name, Towns.TownName would also work).

You'll then have a report which resembles your requirement.