Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Common Functions - SQL Server Reporting Services

RSS
Modified on Fri, Sep 09, 2011, 3:25 PM by Administrator Categorized as SSRS (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


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.

  • (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")

  Name Size
- ReportPropertiesDialogCodeTab.png 18.42 KB

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.