I am converting a report created using Crystal Reports 10 to Reporting Services. The report contains a list of items with dollar values. The original report displays both the Average and Median value. I can easily ( using avg(Field1.Value!) ) determine the average but cannot find a function to determine the median.
How can I add the Median to the report
Thanks in advance,
Greg
Calculate Median Value
dwerner_80
Try changing the AddToMedianArray to return a value (any value). Also don't use Aggregate (it has different semantics than other aggregate functions).
- Fang
Thomas Andersson
Hi,
I am also having problem with creating a custom median function. I am currently creating a graph with average values and I simply want to change this to medians, so instead of =avg(Fields!DurationYears.Value) in the Edit Expression dialog box, I want to be able to type in something like =code.median(Fields!DurationYears.Value). But what I am unclear about is how to write the median function. From the other posts it seems like two functions are required, although I can only see one place to enter the function - i.e. in the Edit Expression dialog box!
Many thanks for any help that anyone can offer!
Nightmare_BE
Greg,
Could you share the custom code for Step 1 please
Thanks.
LINAAR
Thank you for your response. I have created a function to add the values to an array and another to determine the median value in the array. But I am having trouble with Step 2 of your answer.
When I insert the aggregate with the instruction to add the value to the array I receive the following error.
textbox24 is in the details section of a table. The contents of the textbox are
I get the same error when I try using Max instead of Aggregate.
Thanks again for the help.
Greg
Jack Burton
I added a return to step 1 of the field value, and now it is properly populating the array. The interesting bit is when I attempt to retrieve the Median value, it is for the previous group in the table. Which leaves thefield for the first group 0 and the final group median is not displayed.
Also, when I try to display TotalMedianValue al that is displayed is 0. The array is never populated.
Here is all of the code.
=================== Begin Code ======================
Public GroupMedianArray(0) as Decimal
Public TotalMedianArray(0) as Decimal
Public function addToMedianArray(fieldValue as Decimal)
Dim g as Integer
Dim t as Integer
'Add to Group Array
g = uBound(GroupMedianArray) + 1
ReDim Preserve GroupMedianArray(g)
GroupMedianArray(g) = fieldValue
'Add to Total Array
t = uBound(TotalMedianArray) + 1
ReDim Preserve TotalMedianArray(t)
TotalMedianArray(t) = fieldValue
Return fieldValue
end function
Public function GroupMedianValue()
Dim g As Decimal
g = uBound(GroupMedianArray)
g = CInt(g/2)
Array.Sort(GroupMedianArray)
g = GroupMedianArray(g)
ReDim GroupMedianArray(0)
return g
end function
Public function TotalMedianValue()
Dim t As Decimal
t = uBound(TotalMedianArray)
t = CInt(t/2)
Array.Sort(TotalMedianArray)
return TotalMedianArray(t)
end function
===================== End Code =======================
WhiteGandalf
Thanks
Sinisa
MarkR_OmniVue
Could you please inform me where you are placing your calls to
a) addToMedianArray()
b) GroupMedianValue()
in the report. I am facing the same issue and if I place the calls in the cell where I need the median to be displayed in the following form
=Code.median(Count(Code.getValues( Fields!receivedToOffer.Value)))
I get the median displayed only for the top group and all the other groups' medians are displayed as zero.
Thanks a lot in advance,
Raghu.
PowerPoster
Median is not currently supported, and is on our wishlist for future releases.
For now, you can use custom code to calculate median:
1. define a array in custom code and a function that takes a value and adds it to the array
2. wrap the call to the function in an aggregate and add it to an expression in the scope where you want to calculate the median. E.g. =Max(Code.AddValue(Fields!field.Name)).
3. In the textbox that you want to display the median, call into a function that calculate the median value from the array. E.g. =Code.GetMedian()
Sanitario
Firstly the sql needs to be returned in the order that it is being returned in the report. This is easily achieved by using GROUP BY at the end of your querry.
The general program flow is this.
There are the following member variable to maintain info as we work through the processing of the report
Public GroupMedianArray(-1) As Decimal
Public startInt As Decimal
Any aggregate function (such as count or sum) will be called for every member in the dataset before any other parts of the report are rendered. So, you will call this (AddToMedianArray) function from anywhere in your report. Call the function using the following code snippet in the expression of any cell in the report. “=Count(Code.addToMedianArray(Fields!TotalDue.Value))” This will then add each number to the array. As you can see above the GroupMedianArray is set to -1 initially, this is because we increment the size of the array every time we add a number.
Public Function addToMedianArray(ByVal fieldValue As Decimal) 'as Decimal
Dim g As Integer
'Add to Group Array
g = uBound(GroupMedianArray) + 1
ReDim Preserve GroupMedianArray(g)
GroupMedianArray(g) = fieldValue
startInt = 0
End Function
At this point we have an array with every number in this column in the array. From here we need to pass the count of that group into a function. Put the following into a group header. “=Code.GroupMedianValue(Count(Fields!TotalDue.Value))” This function will use the startInt member variable above to keep track of where the first number for that group is in the array. Then a sub array will be made using the start int and the number given to us by the count parameter. After building this array it is sorted, and then the median is computed. Here’s the code.
Public Function GroupMedianValue(ByVal count As Decimal) As Decimal
Dim subArray(count - 1) As Decimal
Dim i As Decimal
For i = 0 To (count - 1)
subArray(i) = GroupMedianArray(startInt + i)
Next
Array.Sort(subArray)
startInt += count
Dim g As Decimal
If subArray.Length Mod 2 = 1 Then
g = subArray((subArray.Length / 2) - 0.5)
Else
Dim val1 As Decimal
Dim val2 As Decimal
val1 = subArray(subArray.Length / 2) * 0.5
val2 = subArray((subArray.Length / 2) - 1) * 0.5
g = val1 + val2
End If
Return g
End Function
Hope this makes enough sense. For multiple groups, you will need to add more startInt variables to keep track of where to start in the big array in order to make the sub array for that group.
ScottEDyer
Thanks very much for that, I managed to get it working.
However I was actually trying to get it to work for a graph - any idea where I would place the “=Count(Code.addToMedianArray(Fields!TotalDue.Value))” expression
Cheers,
Denham
ericlock
Good luck everyone.
Greg
Mitch Denny
All, sadly I have given up on this for now. I was never able to get an accurate median value for a sub group. only for the entire table. The group medians always appeared in the following group, not the group they belonged to.
Felix Jiang