iTekExpert iTekExpert - 8 months ago 39
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.

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
    AddValue = values.Count
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

    GetRankPercentile = Math.Ceiling((countGreater + (countEqual / 2)) / countTotal * 5)
End Function