Sometimes we fall into a situation where we need to calculate columns difference in Matrix. Something likes image1
Let’s go through with following steps to achieve this output in Matrix. I believe that you already have created required column and row group in your matrix. The following steps are additional work to achieve the column difference in the matrix.
 |
| Image1 |
Step 1. Create a calculated column in the dataset named as ColumnDiff and assign this expression under Field Source
="Diff : "+Format(Cdate(Fields!OrderDate.Value),"MM-yyyy")+" - "+DateAdd(DateInterval.Month,-1,Cdate(Fields!OrderDate.Value)).ToString("MM-yyyy")
 |
| Image2 |
Refer image2 regarding this step.
Step 2. Now select the matrix and create a new Parent Group under your Column Groups
 |
| Image3 |
Refer image3 regarding this step.
Choose [ColumnDiff] field as a Group by expression and click ok button.
 |
| Image4 |
Refer image4 regarding this step.
Now click on newly created column group and give an approprite name as grp_ColumnDiff. Because we will use this group name in a expression in next step.
 |
| Image5 |
Refer image5 regarding this step.
Step 3. Now use following expression in your child group row & [ColumnDiff] column cell.
=SUM(Fields!TotalAmount.Value)-Previous(SUM(Fields!TotalAmount.Value),"grp_ColumnDiff")
Refer image6 regarding this step.
 |
| Image6 |
Let’s discuss the above expression.
SUM(Fields!TotalAmount.Value) will give the total value for the current column group and Previous(SUM(Fields!TotalAmount.Value),"grp_ColumnDiff") will give the total value of the previous column group corrosponding to grp_ColumnDiff column group
Step 4. Now set the following column visibilty expression for the last column that says [ColumnDiff]
=CBOOL(Cdate(Fields!OrderDate.Value).Month mod 2)
Refer image7 regarding this step.
 |
| Image7 |
This expression will return True for ODD month number and False for even month number.
You need to only change the report connection to appropriate connection string to execute this report at your own enviornment. This sample is designed in SSRS2008R2 version. So lower version of SSRS might give some design error.
