This is either something so easy that none of the tutorials I've seen bothers to mention it, or, I'm using the wrong search phrase!
What I want is, using the Report Builder, to group my data using header-like rows. Say I have a table looking like this
NORTH AMERICA United States Washington
NORTH AMERICA Canada Ottawa
SOUTH AMERICA Brazil Brasília
SOUTH AMERICA Argentina Buenos Aires
I want to show the countries and their capitals grouped by continent in the report:
NORTH AMERICA
-----------------------------
United States Washington
-----------------------------
Canada Ottawa
-----------------------------
SOUTH AMERICA
-----------------------------
Brazil Brasília
-----------------------------
Argentina Buenos Aires
How do I do that?
No matter what I try, I can only get either this, with the continents as a first column
NORTH AMERICA United States Washington
-----------------------------
Canada Ottawa
----------------------------------------------
SOUTH AMERICA Brazil Brasília
-----------------------------
Argentina Buenos Aires
-----------------------------
or this, where the whole table is extended horizontally:
NORTH AMERICA SOUTH AMERICA
----------------------------------------------------------
United States Washington
----------------------------------------------------------
Canada Ottawa
----------------------------------------------------------
Brazil Brasília
----------------------------------------------------------
Argentina Buenos Aires
So what DO I need to do? Or, what kind of words do I need to search for in order to find the answer?
DECLARE @tbl table (Id int IDENTITY, Continent varchar(100), Country varchar(100), City varchar(100));
INSERT @tbl (
Continent
, Country
, City
)
VALUES ( 'NORTH AMERICA', 'United States', 'Washington' )
, ( 'NORTH AMERICA', 'Canada', 'Ottawa' )
, ( 'SOUTH AMERICA', 'Brazil', 'Brasília' )
, ( 'SOUTH AMERICA', 'Argentina', 'Buenos Aires' );
WITH cont AS (SELECT DISTINCT id, t.Continent ,'' Country,'' City, 1 ord FROM @tbl t)
, tbl AS (SELECT id,t.Continent , t.Country,t.City , 2 ord FROM @tbl t)
SELECT a.ID
, a.ContinentOrCountryCity
, a.ord
FROM (
SELECT Min(id) ID
, cont.Continent ContinentOrCountryCity
, Min(ord) ord
FROM cont
GROUP BY cont.Continent
UNION
SELECT Min(id) id
, tbl.Country+', '+
tbl.City
, Min(tbl.ord) Ord
FROM tbl
GROUP BY tbl.Country+', '+ tbl.City) a
ORDER BY Id, a.ord;
Thanks.
That's... very clever. But does that mean there's no way to do that using the Report Builder tools?
With this SQL, I'm not sure how to, for example, give the generated group headers different properties such as another background colour.
When you say you are using Report Builder - is that SQL Server Data Tools or the downloaded control from SSRS?
If using SQL Server Data Tools you can merge columns (just like in Excel). If you merge the columns at the group level you can then drag the detail columns almost all the way to the left to line those detail columns up under the group column.
You can also use the text box on the detail line directly below the group header and put any of the available detail columns in that text box.
If you are using the Report Builder control - I am not sure about that since I don't use it.
Hope the below steps help you to create the required SSRS report
Step1:
Create a table with Places and capital
Step 2:
Click on Row groups option on the bottom of page
Click on add group
Click on Parent group
A window pops up
Select the columns for continents
Click the check box to Add group header and Add group footer
Click on OK
Note: It adds a column in the table
step 3: Delete the header for all the three columns
step 4: Select continent in the cell on top of places
Step 5: Delete the column continent that was formed earlier due to row groups
this shows the required result
consider this as the table:
continent Places Capital
NORTH AMERICA United States Washington
NORTH AMERICA Canada Ottawa
SOUTH AMERICA Brazil Brasília
SOUTH AMERICA Argentina Buenos Aires