Inspecting more than one previous month's value in SSRS
Hello, I am running SSRS 2008 (not R2). I have a report that displays a list of metric values for the current month and also a sparkline chart for the last 12 months. The cell that displays the current month's value does so by using an IIF expression that only shows the value for the current year and month (both of which are report parameters). This allows me to keep the full 12 months of data available for the sparkline chart. I would like to have the cell with the metric value filled in with a color based on the previous 3 months' values. For example, if the last 3 months had a metric value of 0, I want to color the cell yellow. The problem is that I'm not sure how to analyze previous values. I tried the following expression to analyze the last 2 months (since the Previous() function doesn't seem to allow going back more than 1 row). =IIF(Fields!MetricValue.Value = 0 And Previous(Fields!MetricValue.Value, "MetricsDataSet") = 0, "Yellow", "Lime") This kind of worked. If any 2 consecutive month values were 0, the cell was filled in yellow. However, I want to only use the most recent months' values, and I also want to go back 3 months, not just 2. Can anyone help me? Thanks. JJ
December 8th, 2010 10:22am

Hi, Could you give some examples to illutrate your requirement? I can't understand your logical clearly. thanks, Jerry
Free Windows Admin Tool Kit Click here and download it now
December 9th, 2010 9:22pm

We have 4 different services we offer people. Each of these services has a set of metrics we want to report on. I have a single query that returns the year, month, service name, metric name and metric value for the last 12 months. The report uses this query to display metric names and values for the current year and month, grouped by service name. The reason I have the query include a full 12 months is so that I can add a sparkline chart that shows the previous 12 months, even though the metrics displayed in the report are only for the current month. I want the metric values to be colored red, yellow or green based on some inspection of the past months values (for example, if the past 3 months have a value of zero). I have now found a way to do this, however I will want to perform other checks on previous values and am not sure if this is the best way. I was hoping others would have advice on the best way to do this. I used the following expression for the text box fill to color it red for 6 months of inactivity, yellow for 3 months, and otherwise green. =Switch(SUM(Switch( Fields!TheDate.Value = DateAdd(DateInterval.Month, -5, Parameters!TheDate.Value), Fields!MetricValue.Value, Fields!TheDate.Value = DateAdd(DateInterval.Month, -4, Parameters!TheDate.Value), Fields!MetricValue.Value, Fields!TheDate.Value = DateAdd(DateInterval.Month, -3, Parameters!TheDate.Value), Fields!MetricValue.Value, Fields!TheDate.Value = DateAdd(DateInterval.Month, -2, Parameters!TheDate.Value), Fields!MetricValue.Value, Fields!TheDate.Value = DateAdd(DateInterval.Month, -1, Parameters!TheDate.Value), Fields!MetricValue.Value, Fields!TheDate.Value = Parameters!TheDate.Value, Fields!MetricValue.Value)) = 0, "Red", SUM(Switch( Fields!TheDate.Value = DateAdd(DateInterval.Month, -2, Parameters!TheDate.Value), Fields!MetricValue.Value, Fields!TheDate.Value = DateAdd(DateInterval.Month, -1, Parameters!TheDate.Value), Fields!MetricValue.Value, Fields!TheDate.Value = Parameters!TheDate.Value, Fields!MetricValue.Value)) = 0, "Yellow", True, "Lime") Does anyone have any better ideas?
December 13th, 2010 10:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics