Add Monthly Trend Indicator SSRS


  • Hi All,

    I have a matrix style report where the "month" is the column and this expression is the data: round(100*(SUM(Fields!ENROLLED.Value)/SUM(Fields!TOTAL_EMP_COUNT.Value)), 2)

    I am trying to create a Trend indicator for the "% Participating" Row  that would show either a Red Arrow Down when the last month value is less than the previous month, or a Green Arrow Up when the last month is greater than or equal to the previous month.  So in this case, the last month being  10-13 and the previous month being 09-13.   Im trying to create the proper expression for the indicator, here is what I have (that is not working) :

    =round(100*(SUM(Fields!ENROLLED.Value)/SUM(Fields!TOTAL_EMP_COUNT.Value)), 2) - Previous(round(100*(SUM(Fields!ENROLLED.Value)/SUM(Fields!TOTAL_EMP_COUNT.Value)), 2))

      Since the  Sept (09-13) value of 92.81 is greater than the last month, which is Oct (10-13) of 92.18 then  the Red indicator icon should show up.    Can someone please help me with this?

    Thanks in Advance,


    M Collier

    Thursday, November 21, 2013 4:28 PM


All replies

  • Hi Collier, Your report design is vague to me so a quick input from the top of my head. Well instead of using the formula in the expression you could make use of ReportItems Collection

    Hope this helps............Let me know if you need more help.


    Thursday, November 21, 2013 5:24 PM
  • Hi Ione,

    Thanks for the tip, however I was unable to use the ReportItems Collection in this case.  The report is a simple matrix that has the "Month" column assigned to the column,  and the Expression assigned to the "Data"  portiion of the matrix.   So the values for the % of participants are created on the fly by the expression.  I need a kind of case statement or expression that looks at the last month's value and the previous month's value for that expression field to determine of the trend is going up or down. 

    M Collier

    Friday, November 22, 2013 3:36 PM
  • Hi Collier, Ok I now have a better understanding of the report design. You should be using a custom code and this link should be a good starting point.

    Hope this helps....


    Friday, November 22, 2013 9:45 PM
  • I know this is an old post but in case some one else needs it. I got this working in a matrix by changing the expression in the cell slightly to use the textbox name i.e.

    =Code.GetCurrent(reportitems!Textbox48.Value) & "-Previous-" & iif(Code.GetPrevious()=0,"",Code.GetPrevious())

    Monday, October 27, 2014 10:53 AM