locked
SSRS Matrix Difference between Columns in same column group RRS feed

  • Question

  • KPI Act Bud MTR Percentage Varaince=(budget-mtr)/mtr
      YTD YTD YTD
      2012-13 2012-13 2012-13
    BU1 1 2 4 -0.5
    BU2 2 3 2 0.5
    BU3 3 4 1 3


    Hi,

    I want to calculate the percentage variance in ssrs & the table built is in  matrix format.I have calculated the above in excel & want to represent the same in ssrs.So how do i proceed the same.

    Also the Act,Bud,Mtr belongs to same group(column group).


















    Monday, June 17, 2013 10:21 AM

All replies

  • Just right-click a cell in the current column group and choose add column > inside group > left or right. Insert the appropriate formula. Repeat for however many columns are needed for the group.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, June 17, 2013 2:33 PM
  • Hi Tim,

    I want to get the last column calculation.As in my case MTR & Bud belongs to same group in dimension table,hence i cannot get the difference in ssrs considering the child groups i.e. YTD and year.

    Thanks & Regards

    Archana.

    Tuesday, June 18, 2013 6:00 AM
  • Hi Archana,

    You can use the expression below to calculate the percentage.
    =(Fields!Bud.Value-Fields!MTR.Value)/Fields!MTR.Value

    If I have anything misunderstood, please point it out.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, June 18, 2013 6:12 AM
  • Hi Charlie,

    Thanks for your prompt reply.

    Act,Bud,MTR is in same dimension table & is represented in ssrs as column group.

    [If i am unable to explain,pls see this link & the column named "DAY OF WEEK" which then gives the output as the name of  days of the week.](http://stackoverflow.com/questions/5181415/calculating-the-differences-of-grouped-rows-on-a-tablix)

    Hence the above expression wont work in my case.

    Thanks & Regards

    Archana.

    Tuesday, June 18, 2013 7:29 AM
  • Hi Archana,

    In this case, here has a similar issue, you can refer to the link below to see Charlie's post.
    http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/6b385f12-c431-4cde-99dc-eca3bc5df2bf

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, June 18, 2013 7:56 AM
  • Hi Charlie,

    Thanks for your  reply.The link is somewhat similar to my case.As in my case(refer the table above),the table contains

    Row group as 1)BU Column group 1) Amount_name,2)FTM_YTD_NAME,3)Year_Name

    In Amount_name Act,Bud,MTR is coming In YTD_NAME = FTM & YTD is coming.(so filtered YTD) & applied a filter to the matrix based on a single KPI.

    As mentioned in the link,http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/6b385f12-c431-4cde-99dc-eca3bc5df2bf

    Public Function GetAmount(Amount as Integer, Type as String)  If Type = "Amt" Then    Num1=Amount Else    Num2=Amount End If

    What does this Amount&Amt in the above expression refers to?

    (As the concerned person explained his case as "Column group 1) Type 2) Amount & In type Sales and Collection is coming")

    Apologies, if I am unclear to explain you,but require the solution desperately.

     Thanks & Regards

    Archana
    Tuesday, June 18, 2013 9:50 AM