Calculating Median - Report Builder 3 - SSRS - "Median=0" - What is wrong with my code?

Question

These are my datasets:

These are my data sets

Average Cost column and expression

This is my Median column and expression

Details Group Property variables and expression

Code that I am using to TRY and calculate the median

These are the results.

These are the results

The Median is always zero and I would really like to know why. If any further information is needed to try and resolve this please leave a comment and I will do it ASAP.

score 1 · Accepted Answer · answered Oct 28 '14 at 16:12

1

What if you returned the median in the AddValue function?

Function AddValue(newValue As Decimal)
    If values Is Nothing Then
        values = New System.Collections.ArrayList
    End If

    values.Add(newValue)

    Return GetMedian()
End Function

You could then use AddValue as the expression for the Median column:

="Median = " & Code.AddValue(Fields!AvgCost.Value)

answered Oct 28 '14 at 16:12

Stijn

1,970
3
27
36

You see, depending on the parameters, I receive all of the same values on each row. They are never different. I just noticed this. So where if the parameter is set to a week ago, "Median = 0" and if the parameter is set to a year ago, "Median = $500" for every row. – QuestionQuestion Oct 28 '14 at 20:11
When I was testing this earlier, the function returned the median of the current list for every row. Depending on the values, the median differed for each row. You can try my code [here](https://dotnetfiddle.net/9pUBX7). – Stijn Oct 28 '14 at 22:05
Hi tino, thank you for your time. two questions! 1. On the link to your code, the "Console.WriteLine(code.AddValue(10))" is displayed as having a median of "9.5". How does that work? And also, to implement this code into my code, how would I call my AvgPrice values? Just like how you used the Console.WriteLine to call your values, but with fields! – QuestionQuestion Oct 29 '14 at 13:12
That's just example code for testing. Don't mind the `Console.WriteLine` stuff, I only use it to call `AddValue`, which, on its turn, will return `GetMedian` for the current list. `Console.WriteLine` will then print the returned value in a "console window". I don't think `Console.WriteLine` even exists in SSRS. You only need the 2 functions. – Stijn Oct 29 '14 at 13:23
I know! :) I removed all that stuff and the End Class stuff aswell! It runs now, but I don't know if it is running correctly. I am creating a thread now and if I may, post the link here for you to look at VERY quickly? Please! – QuestionQuestion Oct 29 '14 at 13:35
The median is the middle value in a sorted list. As your list contains at least 11 zero's, it's possible for the median to be 0. It doesn't matter how large the numbers are. – Stijn Oct 29 '14 at 13:58
Would you recommend writing a query for a percentile function? as opposed to this way? Because I think that's what I am going to do. – QuestionQuestion Oct 29 '14 at 14:09
It's not clear from your original question what your full intentions are. The problem you had showing the median should have been solved by the answer I provided. I can't help you with issues with your logic. – Stijn Oct 29 '14 at 14:24

Calculating Median - Report Builder 3 - SSRS - "Median=0" - What is wrong with my code?

1 Answers1