none
how to subtract adjacent columns in an ssrs matrix

    Question

  • I have an ssrs matrix which looks like the one below :

                   Month(Columns)
       Product(Rows)  Sales(Data)

    The output looks something like this :

                           June  July  August  Sept  Oct  
              ABC          34     34     23     22    67
              DEF          33     21     32     22    14

    I want an output that looks like this :

                           June  July  June-July  Aug  July-Aug  Sept  Aug-Sept  Oct  Sept-Oct
                  ABC        34   34      0        23     11      22      1       67     45
                  DEF        33   21      12       32     11      22      10      14     8

    I tried doing something like this :

                    Month(Columns) Change
       Product(Rows)  Sales(Data)   Expression

    The expression looks something like this :

    =Sum(IIF(Fields!MONTH.Value=Fields!MONTH.Value,Fields!Products.Value,Nothing))-
    Sum(IIF(Fields!MONTH.Value=Fields!MONTH.Value - 1,Fields!Products.Value,Nothing))

    But it doesnt work . I want to see the output as shown above . Please let me know.

    Wednesday, November 27, 2013 7:37 PM

Answers

  • Hi Onlineboss,

    We can use the custom code to acieve your requirement. I have tested it on my local environment, the steps below are for your reference.

    1. Copy the custom code below and paste it to your report.(Add Code to a Report (SSRS))
      Public Shared Value1 as Integer=0
      Public Shared Value2 as Integer=0
      Public Shared Function GetValue(Item as Integer) as Integer
           value1= value2
           value2=Item
           return Item
      End Function
      Public Shared Function GetTotal()
           return value1-value2
      End Function
    2. Right-click Month column>Insert Column>Inside Group-Right, and use the expression below to set the new inserted column.
      =IIF(Fields!Month.Value="June",true,false)
    3. Use the expression below on the two columns.
      Left:=Code.GetValue(Sum(Fields!Ammount.Value))
      Right:=Code.GetTotal()

    The report looks like below.

    Regards,


    Charlie Liao
    TechNet Community Support


    Thursday, November 28, 2013 12:12 PM

All replies

  • Why dont you try doing this in the stored procedure which returns the result. It is much easier.
    Wednesday, November 27, 2013 8:03 PM
  • Hi Onlineboss,

    We can use the custom code to acieve your requirement. I have tested it on my local environment, the steps below are for your reference.

    1. Copy the custom code below and paste it to your report.(Add Code to a Report (SSRS))
      Public Shared Value1 as Integer=0
      Public Shared Value2 as Integer=0
      Public Shared Function GetValue(Item as Integer) as Integer
           value1= value2
           value2=Item
           return Item
      End Function
      Public Shared Function GetTotal()
           return value1-value2
      End Function
    2. Right-click Month column>Insert Column>Inside Group-Right, and use the expression below to set the new inserted column.
      =IIF(Fields!Month.Value="June",true,false)
    3. Use the expression below on the two columns.
      Left:=Code.GetValue(Sum(Fields!Ammount.Value))
      Right:=Code.GetTotal()

    The report looks like below.

    Regards,


    Charlie Liao
    TechNet Community Support


    Thursday, November 28, 2013 12:12 PM