Some useful functions for MS CRM 2011 Reports

Here is a couple of useful functions for MS CRM 2011 reports:

Function that used to remove the duplicate records

Public Shared Function RemoveDups(ByVal items As String) As String

         Dim noDups As New System.Collections.ArrayList()

        

         Dim SpStr

         SpStr = Split(items ,",")

         For i As Integer=0 To Ubound(Spstr)

                   If Not noDups.Contains(SpStr(i).Trim()) Then

                            noDups.Add(SpStr(i).Trim())                       

                   End If

         Next

         Dim uniqueItems As String() = New String(noDups.Count-1){}

         noDups.CopyTo(uniqueItems)

        

         Return String.Join(",", uniqueItems)

End Function

Function that show all values of multiple value Parameter in the textbox

=JOIN(Parameters!ReportParameter1.Value, ",")

Show/Hide control based on date value

=iif( Format( CDate(Fields!createdon.Value) , "d-MMM-yyyy") > "01-Mar-2011", False, True)

GetMedian function:

Dim values As System.Collections.ArrayList

Function AddValue(ByVal newValue As Decimal)

    If (values Is Nothing) Then

        values = New System.Collections.ArrayList()

    End If

    values.Add(newValue)

End Function

Function GetMedian() As Decimal

    Dim count As Integer = values.Count

    If (count > 0) Then

        values.Sort()

        GetMedian = values(count / 2)

    End If

End Function

Function that divides the first by the second values

Public Shared Function calcloadPounds(ByVal loadPounds As Double, ByVal divisions As Double) As Object

        If ISNOTHING(divisions) Or divisions = 0 Then

            calcloadPounds = "n/a"

        ElseIf loadPounds = 0 Then

            calcloadPounds = 0

        Else

            calcloadPounds = loadPounds / divisions

        End If

    End Function

Function that will change the font color based on the quantity of units sold

=Switch(Fields!TotalQuantity.Value <= 10000, "Red" ,Fields!TotalQuantity.Value >= 20000, "Green", Fields!TotalQuantity.Value >  10000, "Black")