Skip to content

How to create a dynamic sum in matrix in Reporting Services

Jump to bottom
Anthony edited this page Sep 1, 2017 · 3 revisions

Example 1

=switch(
inscope("RowGroup1") and inscope("ColumnGroup1"), "detail"
,InScope("RowGroup1") and InScope("ColumnGroup2"), "Row1Col2-subtot"
,InScope("RowGroup2")and InScope("ColumnGroup1"), "Row2Col1-subtot"
,InScope("RowGroup2")and InScope("ColumnGroup2"),"Row2Col2-subtot"
,InScope("dataset1")and InScope("ColumnGroup2"),"Col2-subtot"
,InScope("RowGroup2")and InScope("dataset1"),"Row2-subtot"
,InScope("dataset1"),"grandtotal")
)

Example 2

=IIF(Inscope("matrix_Row_Contract")
AND Inscope("matrix_Row_Billable")=FALSE 
AND Inscope("matrix_Row_Project")=FALSE  
AND Inscope("matrix_Row_TMS_Time")=FALSE, 1, "")

Example 3

=IIF(Inscope("matrix_Row_Contract") 
AND Inscope("matrix_Row_Billable") 
AND Inscope("matrix_Row_Project")=FALSE 
AND Inscope("matrix_Row_TMS_Time")=FALSE
,sum(Fields!QUANTITY.Value) / sum(Fields!QUANTITY.Value, "matrix_Column_WeekEnd") 
,IIF(Inscope("matrix_Row_Contract")
AND Inscope("matrix_Row_Billable")=FALSE 
AND Inscope("matrix_Row_Project")=FALSE  
AND Inscope("matrix_Row_TMS_Time")=FALSE, 1, ""))

Clone this wiki locally