Common Functions - SQL Server Reporting Services

Summary¶

DateRange — returns a formatting of two dates, depending on which is NULL
FormatDate — returns a formatted date if it's not NULL, or an empty string otherwise
FiscalPeriod — returns a formatted fiscal period, depending on which arguments (month or year) are greater than zero
PageNumber — returns the current page number for display in the body of a report
ConcatDateTime — returns a date or a date+time formatted as a string, depending on whether the tim is past 12:00am
LabelAndValue — returns a label followed by a value if neither is NULL nor an empty string; and an empty string otherwise
Concat — concatenates its non-NULL arguments
ParameterValues — displays multi-value parameter values, using "(multiple)" if too many are selected
DisplayParameters — displays the values of multiple parameters

Table of Contents [Hide/Show]

   Summary
   Using the Functions
   DateRange
   FormatDate
   FiscalPeriod
   PageNumber
   ConcatDateTime
   LabelAndValue
   Concat
         Sample Usage of the Concat Function
   ParameterValues
         Sample Usage of the ParmeterValues Function
   DisplayParameters
         Sample Usage of the DisplayParameters function

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

DateRange¶

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

(not specified)
On and before xx/xx/xxxx
On and after xx/xx/xxxx
xx/xx/xxxx to xx/xx/xxxx

{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.

m/yyyy
Month m for all available years
Fiscal year yyyy
(all)

{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")