When you’d like to display some columns and rows in a matrix, even though there is no actual data for them, the solution is to add them to the dataset before its processed into the matrix. An easy way to do this is to alter the dataset for the matrix. Use a temporary table and insert the default values into it, then insert the actual data into it et voila:
becomes:
As you can see, the second matrix contains a lot more rows 
! Of course it displays NULL in most cases, but you can easily change NULL into anything else using this expression in the data field:
=IIF(Fields!MatrixData.value IS NOTHING, 0, Fields!MatrixData.value)
The SQL query I used to create a matrix with some default values:
DECLARE @TempTable TABLE
(
EyeColor NVARCHAR(50),
City NVARCHAR(50),
COUNTNames INT
)
INSERT INTO @TempTable
SELECT EyeColor, City, COUNT(Name) FROM Person GROUP BY EyeColor, City
INSERT INTO @TempTable
VALUES ('Brown', 'Amsterdam', NULL)
INSERT INTO @TempTable
VALUES ('Blue', 'New York', NULL)
INSERT INTO @TempTable
VALUES ('Green', 'London', NULL)
INSERT INTO @TempTable
VALUES ('Grey', 'Miami', NULL)
SELECT EyeColor, City, SUM(COUNTNames) AS COUNTNames FROM @TempTable GROUP BY EyeColor, City
As you can see, I used a temporary table. In my case a variable table was suitable, but a table in the TempDB using a CREATE TABLE #TempTable statement is also a solution. The table attributes need to correspond with the attributes in the actual dataset.
Fill the table with the actual data and then with the default values you’d like to see in your columns and rows.
Many of you have probably seen and/or used the Document Outline window in Microsoft Word, this nice feature is also available in Visual Studio 2005. It gives you a handy overview of all the items in your document. Just click View | Other Windows | Document Outline or Ctrl-Alt-T.
I’ve come across this window designing a report in the Business Intelligence Development Studio (BIDS). I don’t know how much I will use this feature, but it could help me much by giving an overview of all the items in my report. I can easily select any item and change it properties, cut it, copy it or rename it!
Jorg, a friend and colleague wrote an interesting blog about using the InScope() function in a matrix to take control on the behavior of your subtotals. I’d like to extend his example to illustrate how you can use this function to change the style of the (total)cells in your matrix.
As Jorg already explained you get the following default groups when you create a matrix in a SSRS 2005 report:
A row group named: matrix1_RowGroup1
A column group named: matrix1_ColumnGroup1
When you use the following expression for the Background color property of the data field in your matrix, you can make the Background color of the group total differ from the color of the data field(s):
=IIF(InScope("matrix1_ColumnGroup1"),
IIF(InScope("matrix1_RowGroup1"),
"In Cell",
"In Subtotal of RowGroup1"),
IIF(InScope("matrix1_RowGroup1"),
"In Subtotal of ColumnGroup1",
"In Subtotal of entire matrix"))
You can replace "In Cell", "In Subtotal of RowGroup1", "In Subtotal of ColumnGroup1" and/or "In
Subtotal of entire matrix" with any color you prefer. For example:
=IIF(InScope("matrix1_ColumnGroup1"),
IIF(InScope("matrix1_RowGroup1"),
"White",
"Blue"),
IIF(InScope("matrix1_RowGroup1"),
"#ff8000",
"Red"))
This will give the following result:
If you'd like to change the font settings for the totals, replace the expression for the Color property to:
=IIF(InScope("matrix1_ColumnGroup1"),
IIF(InScope("matrix1_RowGroup1"),
"Black",
"White"),
IIF(InScope("matrix1_RowGroup1"),
"White",
"White"))
This will give the following result:
