iTekExpert - 1 year ago 80
SQL Question

# SSRS expression - query from dataset, with group

Let's say for each student, I have a note of an exam and I need to calculate the percentile rank... of each question, each group of question and total exam (each student).

Do to that I need, for each question, group of question, and total exam:

1) x = the score (That I have of course)

2) the count of score above x

3) the count of score equal to x

4) the count total of score

Do to that it looks like I need to use sub-select for me, in a T-SQL query. Calculate everything inside a big dataset and use it.

Is there a way to acheive that inside SSRS?

I found that interesting post about the percentile function in SSRS, I will give it a try.

https://www.katieandemil.com/ssrs-percentile-function-2008-r2-calculation-custom-code-example?tab=article

I had to create another function to return the rank, but the main idea was there:

``````Public Shared Dim values As System.Collections.ArrayList

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

Public Shared Function GetRankPercentile(ByVal CurrentValue As Decimal) As Decimal
Dim countTotal As Integer = values.Count 'nombre total de donnĂ©es
Dim countGreater As Integer = 0
Dim countEqual As Integer = 0
Dim iLoop As Integer
Dim tmpArray as system.array
tmpArray = values.ToArray()

For iLoop = LBound(tmpArray) To UBound(tmpArray)
If tmpArray(iLoop)  CurrentValue Then countGreater = countGreater + 1
If tmpArray(iLoop) = CurrentValue Then countEqual = countEqual + 1
Next

GetRankPercentile = Math.Ceiling((countGreater + (countEqual / 2)) / countTotal * 5)
End Function
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download