## SSRS 65# – Add row or column totals to matrix

A reader asked me how to add grand totals a matrix (A Sample of Matrix in SSRS).

It’s actually pretty easy to accomplish that in both SSRS 2005 and 2008. I am going to show a few screen shots here.

**Challenge**

Suppose you have a very simple table with sales data for products sold from each store.

You want to rearrange the data so it shows the stores vertically, all products horizontally, and the Sales Amount at each intersection. Finally you want to show totals at the bottom and also to the right.

Matrix will be a perfect choice for this purpose with no extra coding on your part.

**Solution – create a matrix and then add totals to row and column**

The steps are very straightforward. I am going to show the screen shots from SSRS 2008 here.

Here is a matrix without any configuration yet.

Point the cursor to the Rows cell, an icon appear on the upper right corner. Click the icon, you will see the list of columns from your dataset. Select Store.

For the Columns cell, select Product.

For the Data cell, select SalesAmount.

Now, right-click the data cell, you will see choices under Tablix. Select Add Totals, and then Row.

Repeat the above step to create a total next the column Product.

Thanks for the answer. But I’ve four groupings in the matrix, It shows the total for the individual groupings as per your suggestion. But i need the grand total of all the groupings for a month row. Say January 2011 i want all the group totals to show the grand total for each row.

Tiruvazhi

LikeLike

Hello Sherry:

I need your help.

I want to Monthly % difference column in a matrix report.

I’ve 12 months of invoice totals on month column group. i need to calculate % between the months for example Jan total 25765 whereas Feb total is 23987 i want to find by creating a formula whether it’s positive or negative increase in percentage totals.

Please help me.

thanks

Tiruvazhi

LikeLike

Hi Tiruvazhi,

Your question about creating sub-totals/percentage using matrix is a very common question. Although it is straightforward enough to use a matrix report, there are some tricks that can cause frustration. I’ll try to answer your question as soon as possible when I get time in the next few days.

Sherry

LikeLike

hi,

when you work on the example for %, please include the % calculation for

Amount01 (rowgroup,columgroup)/ Amount02 (rowgroup,columgroup)*100

I have a report that has one Column group and two row level grouping.

the group column has 3 percentages

ex: column group 3 = column group 1/colum group 2 * 100

and column group 14 = column group 13/column group 2 * 100

and column group 15 = column group 12/column group 2 * 100

regards

Shibu P

LikeLike

Good Day,

I am not able to identify, the cell (row,column) in a matrix report.

If I could identify the cell

Ex.

Cell (rowgroup01,colgroup01,item no: 01) then I could say

Cell (rowgroup01,colgroup01,item no: 03) = Cell (rowgroup01,colgroup01,item no: 01)/ Cell (rowgroup01,colgroup01,item no: 02) * 100

My simplified table is

Project, Year, AccountNum, Amount, AccBit

ABC, 2010, 10xy, 1000, 10 Rev

ABC, 2011, 10xy, 1000, 10 Rev

ABC, 2012, 10xy, 250, 10 Rev

DEF, 2010, 10xy, 1000, 10 Rev

DEF, 2011, 10xy, 1000, 10 Rev

DEF, 2012, 10xy, 250, 10 Rev

ABC, 2010, 20xy, 900, 20 D.Exp

ABC, 2011, 20xy, 800, 20 D.Exp

ABC, 2012, 20xy, 200, 20 D.Exp

DEF 2010, 20xy, 900, 20 D.Exp

DEF, 2011, 20xy, 800, 20 D.Exp

DEF, 2012, 20xy, 200, 20 D.Exp

ABC, 2010, 40xy, 50, 50 I.Exp

ABC, 2011, 40xy, 60, 50 I.Exp

ABC, 2012, 40xy, 10, 50 I.Exp

DEF, 2010, 40xy, 50, 50 I.Exp

DEF, 2011, 40xy, 60, 50 I.Exp

DEF, 2012, 40xy, 10, 50 I.Exp

In my matrix report I get

|10 Rev |20 D.Exp |30 GM |40 GM% |50 I.Exp |60 EBIT |70 EBIT%

ABC

2010 |1000 |900 |100 |10% |50 |50 |5%

2011 |1000 |800 |200 |20% |60 |140 |14%

2012 | 250 |200 |50 |20% |10 |40 |16%

Total ABC |2250 |1900 |350 |50% |120 |230 |35%

DEF

2010 |1000 |900 |100 |10% |50 |50 |5%

2011 |1000 |800 |200 |20% |60 |140 |14%

2012 | 250 |200 |50 |20% |10 |40 |16%

Total DEF |2250 |1900 |350 |50% |120 |230 |35%

The Calculation for

30 GM = 10 Rev – 20 D.Exp

40 GM% = 30 GM / 10 Rev *100

60 EBIT = 30 GM – 50 I.Exp

70 EBIT% = 60 EBIT/ 10 Rev*100

The error is in

1, the calculation of the 40 GM% & 70 EBIT%

Project ABC may use account 1010 & 1020 to record the revenue but project DEF will use 1020 to record their revenue

2, the major error is in the totalling of percentages

In the example above the correct figures are

|40 GM% |70 EBIT%

Total ABC |15.5% |10.2%

Total 40 GM % = Total 30 GM/ Total 10 Rev*100

Total 70 EBIT%= Total 60 EBIT / Total 10 Rev*100

Friends I need help with the calculation of the percentages

Thanks in advance.

Best Regards

Shibu.P

Email: gpshiburaj@hotmail.com

Please consider the environment before printing this e-mail

LikeLike