Column Grouping in SSRS report Matrix

Question

I am trying to group the columns in SSRS report. I managed to show the first level of column group using Matrix. Now I need Grand total at the end.

Could someone please help me on this.

Need to display total as shown in the below Image

With Matrix grouping I am getting the first part

Result

Now I need to add grand totals for each country Desired Output

Answer 1

You can't do this exactly but you can get close. Don't worry about the length of the answer, lots of images and two partial solutions are in here, both simple.

There are two approaches you can take, the first one resembles your requirements more but is more work, the second on is very simple but means showing the totals above the data.

In this example, I started with some sample data (I added spaces in front of the country names as a cheap and nasty way to sort the columns so they looked like you example)

DECLARE @t TABLE(Country varchar(20), City varchar(20), Company varchar(20), Amount int)

INSERT INTO @t VALUES 
('  UK', 'London', 'Company 1', 2),
('  UK', 'Manchester', 'Company 1', 3),
('  UK', 'London', 'Company 2', 3),
('  UK', 'Manchester', 'Company 2', 2),

(' France', 'Lyon', 'Company 1', 1),
(' France', 'Lyon', 'Company 2', 1),

('Germany', 'Munich', 'Company 1', 0),
('Germany', 'Munich', 'Company 2', 0)

SELECT * FROM @t

So with this as the dataset, I added a matrix to the report. Dragged company to the rows placeholder, City to the columns placeholder and Amount to the data placeholder, simple so far.

Next I right-clicked the "Company" row group in the row group pane under the main designer, and selected "Add total - After".

The design looks like this..

If we look at the report so far we just get a simple matrix with column totals.. , not what you want..

---

Option 1:

---

Next, I right-click the City Column Group and do "Add Group - Parent Group" and check the 'add group footer' option

Now we get totals but they are still only for each city.

However, if we change the expression in the bottom total and set the scope of the expression to the 'Country' column group like this..

=Sum(Fields!Amount.Value, "Country")

We now get this..

Finally we can hide the duplicates by setting the HideDuplicates property to Country (the name of the column group we used in the expression earlier)

Not perfect by maybe acceptable?

---

Option 2:

---

The other option is to simply move the total to the top, directly under the Country name. To do this, right-click the City textbox, then do "Insert Row ==> Inside Group - Below"

In the new row, select "amount" from the drop down in the text box directly below City, finally centre everything to tidy up..

Final design looks like this (with both methods being used)

Which gives us this output