locked
Find the difference between two columns in an ssrs matrix ? MSCRM RRS feed

  • Question

  • Hi All,

    I am working in reporting part of our project (On-line MSCRM 2013). I am new in reporting services.

    I am trying to create report using fetch xml based. Below is the snap what we have required the result.

    Kindly help me, how to get the difference in both column. (Its a matrix table where year is grouped).

    

    Thanks,

    Mohammad Sharique

    Thursday, December 18, 2014 10:48 AM

Answers

  • Use this Expression:

    =Sum(IIF(Fields!Year1.Value = "2013",cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    - Sum(IIF(Fields!Year1.Value = "2014",cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    Where Fields!Year1.value is year field.


    Regards, Saad


    Friday, December 19, 2014 12:37 PM

All replies

  • Hello,

    You can fetch a new column, cast it to negative and then SUM up with the positive value.

    Cornel


    Cornel Croitoriu - Senior Software Developer

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Biz-Forward.comCroitoriu.NET

    Thursday, December 18, 2014 11:21 AM
  • Right click 1, copy name from properties, assume name is yourTextBoxName1

    Right click 2, copy name from properties, assume name is yourTextBoxName2

    Add expression in difference field.

    =ReportItems!yourTextBoxName1.Value - ReportItems!yourTextBoxName2.Value

    Hopefully this work for u.



    Thursday, December 18, 2014 12:20 PM
  • Thanx Bro for your reply and inputs. But here i need to get difference between

    Plan Revenue 2013 - Plan Revenue 2014 = Difference

    Actual Revenue 2013 - Actual Revenue 2014 = Difference

    So please let me know for this. If i use your above given query how it will that plan revenue filed 2013 - plan revenue filed 2014, Kindly suggest and direct me.

    Below is the Design time report

    Run Time report.

    Hope you got the requirement. Please reply me.

    Thanks



    Friday, December 19, 2014 5:54 AM
  • What is the value Of Expr in you design for Plan Revenue?

    Regards, Saad

    Friday, December 19, 2014 6:29 AM
  • In year column Expression is =Sum(Fields!new_planrevenueValue.Value/1000)

    We have divided value by 1000 because value comes big thats why, and in header of that value we are showing these are in thousand. 

    And in Difference column there is not expression now i set. Please help me !

    Thanks


    Friday, December 19, 2014 12:18 PM
  • In year column Expression is =Sum(Fields!new_planrevenueValue.Value/1000)

    We have divided value by 1000 because value comes big thats why, and in header of that value we are showing these are in thousand. 

    And in Difference column there is not expression now i set. Please help me !

    Thanks

    Friday, December 19, 2014 12:21 PM
  • Use this Expression:

    =Sum(IIF(Fields!Year1.Value = "2013",cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    - Sum(IIF(Fields!Year1.Value = "2014",cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    Where Fields!Year1.value is year field.


    Regards, Saad


    Friday, December 19, 2014 12:37 PM
  • Dear Saad,

    Alhumdulillah its working for me, but one query how we will handle the hard coded value "2013" and "2014". Report can be for different year so please let me know. And once again for your help i appreciate it. Thanks a lot.

    Monday, December 22, 2014 5:50 AM
  • You can create two Year parameters and use those paramaters for comparing values. In this expression you just need to use those parameters instead of Year Value.

    Regards, Saad

    Monday, December 22, 2014 5:57 AM
  • Ok. i was thinking for same, ill let you know.

    Thanks :)

    Monday, December 22, 2014 6:05 AM
  • Bro Saad, Instead of creating "Year parameters" can we not get the years from the dataset field because here the value always will come only for two year from the database table. Like 2013 and 2014 OR 2014 and 2015.

    Means we can't place the dynamic values in expression instead of hard coded value.  :)

    Monday, December 22, 2014 6:33 AM
  • There are many ways of doing it. On the dataset perform sort it by year in descending order and get the top most row. then use that expression in your expression and -1 of that year in expression. Or

    You can also get current year from today() function and get last year also. you can use that also in your expression


    Regards, Saad


    • Edited by Mohd Saad Monday, December 22, 2014 7:30 AM
    Monday, December 22, 2014 7:29 AM
  • Dear Saad,

    To getting years dynamically i tried below expression

    using  Last(fields! new_year.value) function i am getting "2014" And

    using First(fields! new_year.value) function getting "2013"

    but when using in expression its giving errors. Used as below expr

    =Sum(IIF(Fields!new_year.Value = Last(fields! new_year.value),cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    - Sum(IIF(Fields!new_year.Value = First(fields! new_year.value),cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))

    but expression giving error as below snap.

    Kindly let me know the resolution.


    Monday, December 22, 2014 9:48 AM
  • Use this:

    =Sum(IIF(Fields!Year1.Value = (year(today)-1),cdec(Fields!amount.Value), cdec(0)))
    - Sum(IIF(Fields!Year1.Value = year(today),cdec(Fields!amount.Value), cdec(0)))


    Regards, Saad

    Monday, December 22, 2014 10:01 AM
  • Dear Saad,

    Thanks for your quick reply, year come from database table, here we are not getting report only for current and previous year. It can be another year like 2013 & 2014.

    Monday, December 22, 2014 10:10 AM
  • then use two different year parameters if you want to compare different years.

    Regards, Saad

    Monday, December 22, 2014 10:16 AM
  • Bro Saad,

    From database table records will come only for two year it can be only for 2013 to 2014 or it can only for 2012-2013 like this.

    table will have records only for 2013 to 2014 or 2012-2013 that's y i don't want to use parametrized report for year. Please help me on this.

    Monday, December 22, 2014 11:56 AM
  • create a hidden parameter and populate it's value from a new dataset with query like this
    select top 1 year(new_date) as Year
    from yourtablename
    sort by year(new_date) descending
    you will get the year and last year from this parameter.

    Regards, Saad


    • Edited by Mohd Saad Monday, December 22, 2014 1:35 PM
    Monday, December 22, 2014 1:34 PM
  • Bro Saad,

    I used your above option for parametrized and done the report,Currently we are getting value in Difference columns now i want to show that value in percentage. How can we show the percentage based on that value. Means i want to show the Difference in Percentage. 

    Kindly help me i tried but getting some issue. Below i am mentioning the code and snap with result.

    Below expression using to showing Plan Revenue in Percentage for year.

    =
    (((
    (
    Sum(IIF(Fields!new_year.Value =Parameters!StartYear.Value,cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    - Sum(IIF(Fields!new_year.Value =Parameters!EndYear.Value,cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    )
    /IIF(Sum(IIF(Fields!new_year.Value = Parameters!StartYear.Value,cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))>0,
    (Sum(IIF(Fields!new_year.Value = Parameters!StartYear.Value,cdec(Fields!new_planrevenueValue.Value/1000), cdec(0))))
    ,1)
    )*100))

    Result issue is as below in snap with highlighted in red colour.

    Kindly help me on this issue also :)

    Thank a lot Bro Saad.


    Monday, December 22, 2014 3:08 PM
  • How you want to calculate the percentage. I mean what will be the denominator in percentage calculation. you want percentage increase or decrease from last year? or something else. Your current formula suggests you want percentage increase in terms of last year.

    It's better if you show the percentage change like this

    =
    (((
    (
    Sum(IIF(Fields!new_year.Value =Parameters!StartYear.Value,cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    - Sum(IIF(Fields!new_year.Value =Parameters!EndYear.Value,cdec(Fields!new_planrevenueValue.Value/1000), cdec(0)))
    )
    /IIF(Sum(cdec(Fields!new_planrevenueValue.Value/1000))>0,
    (Sum(cdec(Fields!new_planrevenueValue.Value/1000)))
    ,1)
    )*100))

    Regards, Saad


    • Edited by Mohd Saad Tuesday, December 23, 2014 6:39 AM
    Tuesday, December 23, 2014 6:35 AM
  • Bro Saad,

    Below is the snap with result i used above query. Can you please check the result is right ? Please :)

    and if result right can you please explain once here

    Please check only for Plan Revenue Column



    Tuesday, December 23, 2014 8:35 AM