reporting services - SSRS 2008 R2 Calculated Series (Median) ignores custom formatting -

i have created a range chart in visual studio ssrs 2008 r2, , added median line. however, ssrs happily ignore formatting set median line unless same setting range formatting set none/automatic. inherit range's color, marker, , size. reference:

note faint, long line along top (very hard see) same color range. added moving average (dark blue) line show it's not using calculated series problem.

here i've set range "automatic", means line takes red color set to, still doesn't take on desired thickness. (i set median line red here better contrast.)

and i'm trying accomplish. (not desired final color/width, shows difference between settings , created.)

is there way calculated line stop inheriting range's settings? did find this, work-around listed there not working me. if not, there way can calculate median using own line, rather calculated series?

talking boss today, need have avg(), not median. such life.

but had figured out workaround make median own part of graph it's not tied range. not consider answer (though answer second question,) since takes quite bit of work, if must have median in range graph , google takes them question, thought sharing worthwhile:

solution 1 (easy mode)

(this if don't care range's color)

set color of range automatic, set color of derived/calculated series want. change border , marker formatting derived series, change them in underlying range (the range graph type seems ignore these properties, @ least far can tell.) note setting borderstyle on range none keep median showing @ all, no matter other settings.

solution 2 (hard mode)

this working on before told use avg() instead. and, since that's how these things go, i done.

getting median

first, found this answer question nice , clean way find median. unfortunately, doesn't involve kind of grouping, had extend it. started taking data returned , putting temporary table @t. changed query inner join subqueries grouped wanted, getting following:

select (bottomhalf+tophalf)/2 median, b.periodraw, b.weeknumber     (select max(ratio) bottomhalf, periodraw, weeknumber         (select top 50 percent ratio, periodraw, weeknumber @t order ratio) m         group periodraw, weeknumber) b    inner join      (select min(ratio) tophalf, periodraw, weeknumber         (select top 50 percent ratio, periodraw, weeknumber @t order ratio desc) n         group periodraw, weeknumber) t 

turn chart w/ range

then had use max/min in combination single table use in chart:

select (bottomhalf+tophalf)/2 median, maxratio, minratio, b.periodraw, b.weeknumber     (select max(ratio) bottomhalf, periodraw, weeknumber         (select top 50 percent ratio, periodraw, weeknumber @t order ratio) m         group periodraw, weeknumber) b    inner join      (select min(ratio) tophalf, periodraw, weeknumber         (select top 50 percent ratio, periodraw, weeknumber @t order ratio desc) n         group periodraw, weeknumber) t     on b.periodraw = t.periodraw , b.weeknumber = t.weeknumber     inner join      (select max(ratio) maxratio, periodraw, weeknumber @t group periodraw, weeknumber) x     on b.periodraw = x.periodraw , b.weeknumber = x.weeknumber     inner join      (select min(ratio) minratio, periodraw, weeknumber @t group periodraw, weeknumber) n     on b.periodraw = n.periodraw , b.weeknumber = n.weeknumber 

from here it's simple. create range chart would, add new value median. right click on series (in chart data box or on chart itself, doesn't matter) , click on change chart type. list, select line of choice , hit ok. may have change value data median (if says "y value"). that's it! have median in separate line can format heart's content.

however, quite workaround (that requires separate dataset original unless want further join two), if can avoid recommend it; and, said, it's not solution original problem (likely bug, there may never 1 unless it's fixed in later version.)