1) Create 2 text boxes in a column
Re: http://www.simple-talk.com/sql/reporting-services/advanced-matrix-reporting-techniques/
2) The first column will contain the standard sum expression : = Sum(Fields!DetaiField.Value)
DetailField is a placeholder for the field that you count on.
3) The second column will contain the following:
= Sum(Fields!DetaiField.Value)/
Sum(Fields!DetaiField.Value,"matrix_RowGroup")
Substitute the name of your row group for "matrix_RowGroup"
What we do here is sum up the count in the default scope divided by the total of items in a row.
4) The last step is to enclose the last expression into FormatPercent function:
FormatPercent(Sum(Fields!DetaiField.Value)/
Sum(Fields!DetaiField.Value,"matrix_RowGroup"))
Show a Variance Instead of a Total in the Subtotal Column
If you have a 2-column matrix, and you want to show a variance instead of the subtotal, you could use the following expression:
=Iif (inScope("matrix_ColumnGroupName"), Sum(Fields!DataField.Value),
Sum (IIF(Fields!ColumnName.Value = "Closed",
Fields!DataField.Value, Fields!DataField.Value * -1)))
First, you define an expression for the column data fields: Sum(Fields!DataField.Value).
The second part of the IIF function defines an expression for the Subtotal column. The value of the second data column becomes negative which allows us to obtain the variance instead of a sum. The expression assumes that the first column is named "Closed".
An example:
Date\State | Closed | Pending | Variance | ||||||||||
| 8/17/2009 | 317 | 22 | 295 | ||||||||||
| 8/18/2009 | 5215 | 816 | 4399 | ||||||||||
| 9/14/2009 | 5526 | 503 | 5023 | ||||||||||
| 9/16/2009 | 49 | 3 | 46 | ||||||||||
Create a Variance Percentage Column
If instead of the variance you want to show a variance percentage, you need to use the following expression in the subtotal column created earlier:
FormatPercent (Sum (IIF(Fields!State.Value = "Closed", Fields!DataField.Value, Fields!DataField.Value * -1))/ Sum(Fields!DataField.Value)))
What you do here is obtain a variance value first, then a simple sum of all data fields in the row, and then divide the first value by the second.
