Common Functions - SQL Server Reporting Services

Summary



Using the Functions

Each function has its source code shown below. To use the function in a report, you would copy the source code into each report where needed. In Visual Studio 2005, you do this by navigating to the Report menu > Report Properties > Code tab and pasting your code there.

Report Properties dialog, Code tab

Report Properties dialog, Code tab


DateRange

The following function returns one of the following, depending on which date(s) are NULL.


{copytext|DateRange}
'==================================================================================================
Function DateRange(ByVal beginDate As Date, ByVal endDate As Date) As String

    Dim result As String = ""
    Dim b As String = FormatDate(beginDate, "MM/dd/yyyy")
    Dim e As String = FormatDate(endDate, "MM/dd/yyyy")

    If b Is Nothing And e Is Nothing Then

        result = "(not specified)"

    ElseIf b Is Nothing Then

        result = "On and before " + e

    ElseIf e Is Nothing Then

        result = "On and after " + b

    Else

        result = b + " to " + e

    End If

    Return result

End Function

FormatDate

This function returns a date formatted if it's not NULL; or an empty string otherwise.

{copytext|FormatDate}
'==================================================================================================
Function FormatDate(ByVal inputDate As Date, ByVal format As String) As String

    Dim result As String = Nothing

    If inputDate.ToString("MM/dd/yyyy") <> "01/01/0001" Then
        result = inputDate.ToString(format)
    End If

    Return result

End Function

FiscalPeriod

This function returns one of the following depending on which argument(s) are greater than zero.


{copytext|FiscalPeriod}
'==================================================================================================
Function FiscalPeriod(ByVal fiscalMonth As Integer, ByVal fiscalYear As Integer) As String

    Dim result As String = ""

    If fiscalMonth > 0 And fiscalYear > 0 Then

        result &= fiscalMonth.ToString()
        result &= "/"
        result &= fiscalYear.ToString()

    ElseIf fiscalMonth > 0 Then

        result &= "Month "
        result &= fiscalMonth.ToString()
        result &= " for all available years"

    ElseIf fiscalYear > 0 Then

        result &= "Fiscal year "
        result &= fiscalYear.ToString()

    Else

        result &= "(all)"

    End If

    Return result

End Function

PageNumber

This function is useful for displaying the page number in the body of a report.

{copytext|PageNumber}
'==================================================================================================
Function PageNumber() As String
    Return Me.Report.Globals!PageNumber
End Function

ConcatDateTime

Ths function returns a date or a date+time formatted as a string.

{copytext|ConcatDateTime}
'==================================================================================================
Public Function ConcatDateTime(ByVal d As DateTime, ByVal t As DateTime) As String

    Dim result As String = ""
    result = d.ToString("MM/dd/yyyy")
    If t > DateTime.MinValue And t.ToString("h:mm tt") <> "12:00 AM" Then
        result &= " "
        result &= t.ToString("h:mm tt")
    End If

    Return result

End Function

LabelAndValue

This function returns a label followed by a value if the value is neither NULL nor an empty string (excluding whitespace), and an empty string otherwise.

{copytext|LabelAndValue}
'==================================================================================================
Public Function LabelAndValue(ByVal label As String, ByVal value As String) As String

    Dim result As String = ""

    If value IsNot Nothing AndAlso value.Trim().Length > 0 Then
        result = label & value
    End If

    Return result

End Function

Concat

This function concatenates its arguments. It assumes that argument alternate between values and delimiters. The first value that's not NULL and not an empty string is appended to the return string. After that a value and its preceding delimiter is appended only if the value is neither NULL nor an empty string.

See also: Concat Function - SQL Server

{copytext|Concat}
'==================================================================================================
Public Function Concat(ByVal ParamArray a As String()) As String

    Dim result As String = ""
    Dim imax As Integer = a.GetUpperBound(0)

    If imax >= 0 Then

        For i As Integer = 0 To imax
            If a(i) Is Nothing Then
                a(i) = ""
            End If
        Next

        result = a(0)
        For i As Integer = 2 To imax Step 2

            If result.Length = 0 Then
                result = a(i)
            ElseIf a(i).Trim().Length > 0 Then
                result &= a(i - 1)
                result &= a(i)
            End If
        Next
    End If

    Return result

End Function

Sample Usage of the Concat Function

{copytext|sample}
=Code.Concat(Fields!ShipToAddress1.Value
	,vbCrLf, Fields!ShipToAddress2.Value
	,vbCrLf, Fields!ShipToAddress3.Value
	,vbCrLf, Code.Concat(Fields!ShipToCity.Value
		,", ", Fields!ShipToState.Value
		," ", Fields!ShipToZipCode.Value)
	,vbCrLf, Code.LabelAndValue("Contact: ", Fields!ShipToContactName.Value)
	,vbCrLf, Code.LabelAndValue("Phone: ", Fields!ShipToPhoneNumber.Value)
	)

ParameterValues

For a multi-value report parameter, this function returns a concatenation of the labels if the number of selections is at most maxCount; or "(multiple)" otherwise.

{copytext|ParameterValues}
'==================================================================================================
Function ParameterValues(prefix as String, params as Parameters, _
name as String, maxCount as Integer) as String

    Dim result as String = ""
    Dim p as Parameter = params(name)

    If p Is nothing
        result = prefix & "*** Parameter [" & name & "] not found***"
    ElseIf Not p.IsMultiValue Then
        result = prefix
        If p.Label IsNot Nothing then
            result &= p.Label.ToString()
        ElseIf p.Value IsNot Nothing
            result &= p.Value.ToString()
        Else
            result &= "(NULL)"
        End If
    Else        
        result = String.Join(",", p.Label)
        If result.ToUpper().Contains("*** SELECT IF NEEDED ***") Then
            result = ""
        ElseIf p.Count > maxCount Then
            result = prefix & "(multiple)"
        Else
            result = prefix & result
        End If
    End If

    Return result

End Function

Sample Usage of the ParmeterValues Function

{copytext|ParameterValuesSample}
=Code.ParameterValues("Facility: ", Parameters, "Facility", 10)


DisplayParameters

The DisplayParameters function will return a string contatenation the selected values of multiple parameters. A common usage of this function is at the top of a report to display what parameter values the user selected when running the report.

NOTE: This function depends on the ParameterValues function, found above.

{copytext|DisplayParameters}
'==================================================================================================
Function DisplayParameters(ByVal params As Parameters, ByVal maxCount As Integer, ByVal _
ParamArray a As String()) As String

    Dim result As String = ""
    Dim imax As Integer = a.GetUpperBound(0)

    If imax >= 0 Then

        For i As Integer = 0 To imax
            If a(i) Is Nothing Then
                a(i) = ""
            End If
        Next

        For i As Integer = 1 To imax Step 2

            Dim s As String = ParameterValues(a(i - 1), params, a(i), maxCount)
            If s.Length > 0 Then
                If result.Length > 0 Then
                    result += "; "
                End If
                result += s
            End If

        Next

    End If

    Return result

End Function

Sample Usage of the DisplayParameters function

The following code will return, for example, "Fiscal Month: 10; Fiscal Year: 2010; Business Type: Luxury Sedans; Ship From Country: USA".

=Code.DisplayParameters(Parameters, 10, "Fiscal Month: ", "FiscalMonth", 
"Fiscal Year: ", "FiscalYear", 
"Business Type: ", "BusinessType", 
"Ship From Country: ", "ShipFromCountry")