Display message if no row count in Group

Question

I have report with group header. But I'm not sure how to display message

when no row count (no data under Group Header).

OR

if no row count then hide the text border around the column so we can see like a blank row.

Anyone have any way can help?

Note: It does work without Group Header

Answer 1

Do you have a separate table for Countries? I don't see how you could have Japan from your query without a count for it.

I think it would be better to get all of your countries and them JOIN your results to it so the query always has data for all countries.

SELECT 'Germany' AS Country
INTO #Countries 
UNION
SELECT 'Italy' AS Country
UNION
SELECT 'Japan' AS Country

SELECT 1 CustomerID, 'Germany' Country
INTO #Customers 
UNION
SELECT 2 CustomerID, 'Germany' Country
UNION
SELECT 3 CustomerID, 'Italy' Country
UNION
SELECT 4 CustomerID, 'Italy' Country
UNION
SELECT 5 CustomerID, 'Italy' Country
UNION
SELECT 6 CustomerID, 'Italy' Country



SELECT COUNT(CustomerID) COUNTRY_COUNT, C1.Country 
FROM #Countries AS C1
LEFT JOIN #Customers C2 ON C2.Country = C1.Country
GROUP BY C1.Country

This will give a count of 0 for Japan.