Search This Blog

Sunday, 25 November 2012

Calculate Column difference in Matrix

Sometimes we fall into a situation where we need to calculate columns difference in Matrix. Something likes image1
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.
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.

16 comments:

  1. Hi aftab can you help me .
    i have to subtract current month VS Previous month in my matrix. Do you have any idea about this.
    Thanks in advance.

    ReplyDelete
  2. Hi,

    Thank you for the article. It was very informative and works a treat.

    The only issue I could find in the article was image 6 and 7. The third column should contain both groups.

    Instead of having

    /----------------------------\
    /--------------\
    Column 1 Column 2

    It should be

    /----------------------------\
    /----------------------------\
    Column 1 Column 2


    But thank you very much.

    I found this in conjunction with the following article to make the report dynamic an excellent way of creating reports. It took me about 3/4 days to get this working exactly as I wanted but in the end it was worth it. But you do need a bit of time to get your own reports working correctly.

    I now have a report with the following:

    Year
    Week Number
    Sales Week 1 - Sales Week 1 Vs Sales Week 1 Last Year - Sales YTD - Sales YTD Vs Sales YTD Last Year - % Up/Down YTD Vs Last Year

    Darren

    ReplyDelete
  3. Article: https://www.simple-talk.com/sql/reporting-services/advanced-matrix-reporting-techniques/

    ReplyDelete
  4. Here its the simple way you can get your Difference of Two Columns in Matrix report :

    Sum(iif(Fields!Date.Value=Parameters!SelectedDate.Value,Data.Value,Nothing))-Sum(iif(Fields!Rds_Eff_Date.Value=Parameters!PreviousDate.Value,Fields!Data.Value,Nothing))

    Mrugesh

    ReplyDelete
  5. Oh my goodness, Mrugesh, my sanity is saved! Thank you thank you thank you: I've been searching and experimenting for over an hour but your post finally gave me the expression needed for the difference between adjacent columns in a tablix group

    ReplyDelete
  6. Although It was not actually what I was looking for but this article is surely a life saver.

    ReplyDelete
  7. Since I had to calculate the difference between 2 years (Current and last year) only, I added a column "ColDiff" in the dataset with IF condition as following formula. =IIF(Fields!ColumnName.Value=year(today()),1,-1). Then in the report simply SUM "ColDiff" outside the group.

    ReplyDelete
  8. Thanks for your sharing. I'm searching this solution for so long.

    ReplyDelete
  9. I'm having an issue with having a parent group of Month, then child of Year, and trying to figure out how I'm to write the expression. I cannot find how to get the difference from within the group, as I need to know the difference between last year month and this years, based on field called YEAR which would have data record of the actual year in the table.

    PRODUCT January January DIFFERENCE
    2016 2017

    ReplyDelete
  10. Thanks this was very helpful. base on this I would like to know how to use milimar logic without repeating the parent group for all dates. instead only to use recent 2 months of date. Any suggestions ?

    ReplyDelete
    Replies
    1. @vickspills can you explain your requirement a bit more in details?

      Delete
    2. in the above example the Diff column is parent group so is appearing after every subgroup dates. I want to have difference only for the most recent and its previous column.I do not want to compare all columns(although the columns will be displayed).lets take above example. we 4 dates columns 01-2006,02-2006,03-2006,04-2006. I want to compare only the last two(03-2006,04-2006) and not all columns. In the above example the comparison is for every column(because we have used diff column as parent group). is there any way to achieve this?

      Delete
    3. You can logically hide unwanted column by using Visibility expression.

      Delete
    4. see this sample report
      https://drive.google.com/file/d/0ByYnpeuIfYV0Q3NnY2RBb0hGOW8/view?usp=sharing

      Delete
    5. Thanks Aftab, the xpression for the visibility works well for me.
      :)

      Delete