CODEWITHSUNDEEP
reporting-servicescharts
Kodithic
Kodithic

Reputation: 170

SSRS 2008 R2 Calculated Series (Median) ignores custom formatting

I have created a a range chart in the Visual Studio for SSRS 2008 R2, and to this I added a median line. However, the SSRS will happily ignore any formatting I set for this median line UNLESS the same setting for the range formatting is set to none/automatic. So it will inherit the range's color, marker, and size. For reference:

Bad Graph 1

Note the faint, long line along the top (very hard to see) that is the same color as the range. I added a Moving Average (dark blue) line as well to show that it's not just using a calculated series that is the problem.

Bad Graph 2

Here I've set the range to "Automatic", which means that the line takes the red color I set it to, but still doesn't take on the desired thickness. (I set the Median line to Red here for better contrast.)

Bad Graph 3

And this is what I'm trying to accomplish. (Not the desired final color/width, but this shows the difference between settings and what is created.)

Is there any way to get the calculated line to stop inheriting the range's settings? I did find this, but the work-around listed there is not working for me. If not, is there a way I can calculate the median using my own line, rather than a calculated series?

Upvotes: 1

Views: 1674

Answers (1)

Kodithic
Kodithic

Reputation: 170

Talking to my boss today, I need to have the AVG(), not the median. Such is life.

But I had figured out a workaround to make the Median its own part of the graph so it's not tied to the range. I do not consider this the answer (though it does answer my second question,) since it takes quite a bit of extra work, but if someone must have a median in their range graph and Google takes them to this question, I thought sharing this would be worthwhile:

Solution One (Easy Mode)

(This will only help if you don't care about the Range's color)

Set the Color of the Range to Automatic, then set the Color of the Derived/Calculated Series to what you want. To change the Border and Marker formatting for the Derived Series, change them in the underlying Range (the Range graph type seems to ignore these properties, at least as far as I can tell.) Note that setting the BorderStyle on the Range to None will keep the Median from showing up at all, no matter the other settings.

Solution Two (Hard mode)

This is what I was working on before I was told to use AVG() instead. And, since that's how these things go, I was basically done.

Getting just the Median

First, I found this answer from another question for a nice and clean way to find the Median. Unfortunately, this doesn't involve any kind of grouping, so I had to extend it. I started by taking the data originally returned and putting it into a temporary table @t. Then I changed the query to be an INNER JOIN with the subqueries grouped as I wanted, getting the following:

SELECT (BottomHalf+TopHalf)/2 as Median, B.PeriodRaw, B.WeekNumber FROM
    (SELECT MAX(Ratio) as BottomHalf, PeriodRaw, WeekNumber FROM
        (SELECT TOP 50 PERCENT Ratio, PeriodRaw, WeekNumber FROM @t ORDER BY Ratio) m
        GROUP BY PeriodRaw, WeekNumber) AS B
   INNER JOIN 
    (SELECT MIN(Ratio) as TopHalf, PeriodRaw, WeekNumber FROM
        (SELECT TOP 50 PERCENT Ratio, PeriodRaw, WeekNumber FROM @t ORDER BY Ratio DESC) n
        GROUP BY PeriodRaw, WeekNumber) AS T

Turn it Into a Chart w/ Range

Then I had to use Max/Min in combination to get a single table I could use in a chart:

SELECT (BottomHalf+TopHalf)/2 as Median, MaxRatio, MinRatio, B.PeriodRaw, B.WeekNumber FROM
    (SELECT MAX(Ratio) as BottomHalf, PeriodRaw, WeekNumber FROM
        (SELECT TOP 50 PERCENT Ratio, PeriodRaw, WeekNumber FROM @t ORDER BY Ratio) m
        GROUP BY PeriodRaw, WeekNumber) AS B
   INNER JOIN 
    (SELECT MIN(Ratio) as TopHalf, PeriodRaw, WeekNumber FROM
        (SELECT TOP 50 PERCENT Ratio, PeriodRaw, WeekNumber FROM @t ORDER BY Ratio DESC) n
        GROUP BY PeriodRaw, WeekNumber) AS T
    ON B.PeriodRaw = T.PeriodRaw AND B.WeekNumber = T.WeekNumber
    INNER JOIN 
    (SELECT MAX(Ratio) as MaxRatio, PeriodRaw, WeekNumber FROM @t GROUP BY PeriodRaw, WeekNumber) X
    ON B.PeriodRaw = X.PeriodRaw AND B.WeekNumber = X.WeekNumber
    INNER JOIN 
    (SELECT MIN(Ratio) as MinRatio, PeriodRaw, WeekNumber FROM @t GROUP BY PeriodRaw, WeekNumber) N
    ON B.PeriodRaw = N.PeriodRaw AND B.WeekNumber = N.WeekNumber

From here it's simple. Create your Range chart as you normally would, then add a new Value for the Median. Right click on the series (in the Chart Data box or on the chart itself, doesn't matter) and click on Change Chart Type. From that list, select the Line of your choice and hit OK. You may have to change the value data back to Median (if it says "Y Value"). That's it! Now you have your Median in a separate line so you can format it to your heart's content.

However, this is quite the workaround (that also requires a separate DataSet from the original unless you want to further join the two), so if you can avoid it I would recommend it; and, as I said, it's not a solution to the original problem (likely a bug, so there may never be one unless it's fixed in a later version.)

Upvotes: 0

Related Questions