Common RDL functions

How to remove split order key ending

The following code will enable the removal of the order key endings that are specific to split orders. For example: instead of FBO-1234-1, FBO-1234-2 and so on, the displayed value will always be FBO-1234 

For your own report, you will need to copy the functions into the Code property of the report. To insert the functions, click the report background, and in the properties window, view the Code property.

The helper function
Public Function RemoveSplitOrderEnding(orderKey As String) as String
  If(Len(orderKey) - Len(Replace(orderKey,"-","")) > 1) Then
	Return Left(orderKey, InStrRev(orderKey, "-") - 1)
  Else
	Return orderKey
  End If
End Function

To display the trimmed orderKey in a text box or grid cell, you can use the following expression

=Code.RemoveSplitOrderEnding(Fields!OrderKey.Value)

How to sum multiple values from a lookup

Function SumLookup(ByVal items As Object()) As Decimal

If items Is Nothing Then Return Nothing

Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()

suma = 0
ct = 0

For Each item As Object In items

  suma += Convert.ToDecimal(item)
  ct += 1

Next

If (ct = 0) Then return 0 else return suma 

End Function 

Used in an expression:

=Code.SumLookup(Lookupset(Fields!DebtorContactID.Value,Fields!DebtorContactID.Value,Fields!AmountExclVAT.Value, "Services"))

Date formatting expressions

There are numerous requirements and ways to format dates which range from a simple expression =Format(CDate(Fields!MostActualArrivalDateTimeLT.Value), "dd-MM-yyyy") which will provide the result 01-Jul-2019. In some cases this date time values may be blank which will render a fake date e.g 01-Jan-00 rather than a blank cell.

The following expression will return the text "Pending invoice" if there is no invoice date yet.

=IIF(IsNothing(Fields!InvoiceDate.Value),"Pending Invoice",Fields!InvoiceDate.Value).

The following example will leave the date field blank if the date is blank, if not it will return the date value formatted ddd-dd-MMM-yy e.g Mon 01-Jul-2019

=IIF(IsNothing(Fields!DateOfBirth.Value),"",Format(Cdate(Fields!DateOfBirth.Value),"ddd dd-MMM-yyyy"))

The following example will set the value "No Flight" if the arrival/departure date is blank otherwise it will return the value as a time e.g 11:17

=IIF(IsNothing(Fields!ArrivalDepartureDateTimeLT.Value),"No Flight",CDate(Fields!ArrivalDepartureDateTimeLT.Value).ToString("hh:mm"))

Cell formatting expresions

Create a table with alternating line colours e.g  white vs light gray

=IIF(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")

Format the text colour of a date based on if it's the current date e.g black today and gray for other days

=IIF(FORMAT(CDATE(Fields!MostActualArrivalDateTimeLT.Value),"dd-MMM-yy")<>TODAY,"Gray","Black")

Format text colour of a date based on if the date is in the past e.g red for past dates otherwise black (good for expiry dates) 

=IIF(FORMAT(CDATE(Fields!PassportExpiryDate.Value),"dd-MMM-yy")<=TODAY,"Red","Black")

Format text colour based on a numberic value e.g if the amount is above 5,000 show in lime green colour, if below 2,500 show red otherwise show in yellow.

=IIF(sum(fields.invoiceDate.value)>=5000, "Lime", IFF(sum(Fields.InvoiceDate.value)<2500,"Red","Yellow"))

The following expression will fix issues with 'Number stored as text' error messages when exporting to Excel. For example if a debtor contact number are 100022 and FBO1234:

=IIF(IsNumeric(Fields!DebtorContactNumber.Value.ToString()), CDBL(IIF(IsNumeric(Fields!DebtorContactNumber.Value.ToString()), Fields!DebtorContactNumber.Value.ToString(), "0")), Fields!DebtorContactNumber.Value.ToString())

Lookup expressions

Similar to an excel Vlookup it's possible to look up a value and return a single value.

=Lookup(Fields!OrderKey.Value, Fields!OrderKey.Value, Fields!PlanBoardMessagesShort.Value, "Orders")

This example is a lookup returning multiple values 

=Join(LookupSet(Fields!OrderKey.Value,Fields!OrderKey.Value,Fields!PlanboardMessages.Value,"Orders"),",")

Other expressions

Calculate the duration in minutes between two dates/times 

=IIF(IsNothing(Fields!ProgressEndLt.Value),"",DateDiff(DateInterval.Minute, Fields!ProgressStartLt.Value, Fields!ProgressEndLt.Value))

Calculate the age of a someone

=IIF(Fields!DateOfBirth.Value="" ,"?",DateDiff(DateInterval.Year,Fields!DateOfBirth.Value,Today())) or =IIF(CStr(Fields!DateOfBirth.Value)="" ,"?",DateDiff(DateInterval.Year,Fields!DateOfBirth.Value,Today()))

Insert a new line (return carriage) between expressions using +vbLf+. The below example will return the aircraft registration and on the line below the aircraft type icao code

=Fields!RegistrationCode.Value+vbLf+Fields!AircraftTypeIcao.Value