Home > SSRS Expert > SSRS 65# – Add row or column totals to matrix

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.

image

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.

image

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.

 image

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.

image

For the Columns cell, select Product.

image

For the Data cell, select SalesAmount.

image

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

image

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

image

Categories: SSRS Expert
  1. Tiruvazhi
    January 6, 2012 at 7:27 pm

    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

    Like

  2. Tiruvazhi
    February 12, 2012 at 7:57 pm

    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

    Like

  3. Sherry Li
    February 14, 2012 at 2:56 pm

    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

    Like

  4. February 26, 2012 at 3:16 pm

    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

    Like

  5. February 27, 2012 at 5:17 am

    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

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: