Search code examples
reporting-serviceschartsssrs-2008averagereportbuilder3.0

SSRS Report Builder 3.0 - Average StripLine per serie

I have a run chart where the X-Axis is time (split in 13 periods, kind of like months but random dates), the Y-Axis is Number of admissions and my group series is the the Financial year. I need to display 2 years at a time, which means that I have 2 series displayed on my graph being that each year has 13 period.

Goal: to create a an "avg number of admissions" line for EACH year.

Current result:

I went into Y-axis stripline properties, created 2 striplines and in the "IntervalOffset" I wrote the following but it still gives me a stripline with an average for both years (together), instead of 2 different striplines each with the year's respective average.

=IIF(Fields!Year.Value=2013, Avg(Fields!Admissions.Value), Nothing)

=IIF(Fields!Year.Value=2015, Avg(Fields!Admissions.Value), Nothing)

My current graph looks like this: enter image description here

Note: As you can see with the Averages in the table next to it, my graph stripline simply does an average of all the values available, so instead of having Stripline1 at 91.23 and Stripline2 at 81.85, I have only 1 Stripline displaying something like +/-87

My dataset looks as follows:

Year |  Period   |   Admissions

2015 |   01      |    35

2015 |   02      |    50

[...]

2015 |   12      |    43

2015 |   13      |    64

2013 |   01      |    61

2013 |   02      |    52

[...]

2013 |   12      |    45

2013 |   13      |    42

Any ideas?

P.S. My only other alternative (that I can come up with) is to create 2 seperate data sets, put filters on each according to the desired, and then use LookUp to insert it into a Stripline?? But it seems to me like an overkill. There has to be something, simpler =/

Thanks!

Solution

  • Try adding two additional expressions to the Values pane of your line chart.

    2013 Average:

    =AVG(
IIF(Fields!Year.Value=2013,Fields!Admissions.Value,Nothing)
,"DataSetName")

    2015 Average:

    =AVG(
IIF(Fields!Year.Value=2015,Fields!Admissions.Value,Nothing)
,"DataSetName")

    Replace DataSetName by the actual name of your dataset.