How do I add a footer row displaying row count to a matrix in SQL Reporting Services?

Question

I have a matrix on my report that is displaying a data set that contains one row for each person in a company:

Company     Position   Name
-------     --------   -----
Acme Inc.   CEO        Bob
Acme Inc.   COO        Alice
Beta Corp.  CEO        Frank
Beta Corp.  CTO        Rob
Beta Corp.  COO        Bill
(etc)

The matrix has a column grouping for Position, and a row grouping for Company. The final report looks like this:

  Company        CEO      CTO     COO
-----------------------------------------
| Acme Inc.   |  Bob              Alice |
| Beta Corp   |  Frank    Rob     Bill  |
| Foo, Inc.   |           Paul          |
| Bar Corp    |  Mary                   |

I want to add a footer row at the bottom of the matrix that counts how many CEOs, CTOs, etc there are.

-------------------------------------
People in role | 3         2        2

How do I do this in SQL Reporting Services 2005? Unfortunately, Matrix controls in SSRS 2005 don't seem so support footer rows like Table controls do. I think the solution will involve some trickery involving row groups, but I don't know enough about the Matrix control to figure it out.

Solution 1

In the Design view, right click on the "Company" cell and select Add Total-->After.

A new row will be added as a footer with "Total" in the left most cell. To the right of totalm in the Title Column, enter the following expression:

=COUNT(Fields!Name.Value)

Preview the report and the Totals will be displayed exactly as you're looking for.

Update

The above answer is for SSRS 2008. Since you're using SSRS 2005, take a look at Technique #5 in the following link: http://www.simple-talk.com/sql/reporting-services/advanced-matrix-reporting-techniques/

Solution 2

Simplest Solution I could find out was

=SUM(iif(Fields!Name.Value<>"",1,0))

where Fields!Name is data field in Matrix.

---

Please Mark this as an answer you find this helpful

Solution 3

Can you try to use InScope function?

For example,

=IIF(InScope("nameOfColumnGrouping"), Sum(yourField.Value), do something)

You may have to specific the scope on Sum function also.

For example,

=IIF(InScope("nameOfColumnGrouping"), Sum(yourField.Value,"datasetName/groupName"), do something)

Hope this help!