Visual Basic Functions

There is a wide range of Visual Basic (VB) functions that you can use within SunSystems Reporting Services. This is only a sample of the types of function that are available:

  • General
  • Date
  • Decision

General Functions

These general functions and operators are commonly used:

General VB Function Description
ABS(Expression) This returns the absolute value of an expression.
MOD You can use the expression RowNumber(Nothing) MOD 2 to determine the even and odd rows of a dataset.
MID, LEFT, RIGHT, LEN and TRIM These are string functions that manipulate textual data.
LCASE, UCASE These convert string values to lower or upper case.
FORMAT(Expression, "Standard") This formats a numeric string to the standard number format.
CSTR, CDATE, CDBL These convert expressions to a string, date or a numeric double.
Note: It may often be necessary to convert a complex IF statement that is expected to return a numeric value by enveloping the IF statement in a CDBL function.
ME.Value This expression returns the current value of the text box and is useful in a Visibility or Conditional Formatting expression when the condition depends on the text box value.

Date Functions

The most commonly used date functions are:

Date Functions Description
DAY(Date) This returns an integer value from 1 to 31 representing the day of the month.
MONTH(Date) This returns an integer value from 1 to 12 representing the month.
MONTHNAME(Date) This returns the name of the month, for example September.
WEEKDAY(Date) This returns an integer value from 1 (representing Sunday) to 7 (representing Saturday).
WEEKDAYNAME(Date) This returns the name of the weekday, for example Tuesday.
YEAR(Date) This returns an integer value from 1 to 9999, representing the year, from the specified date.
DATEPART("interval", Date) This returns an integer value containing the specified component of a given Date value where interval is:
  • d: Day (of month)
  • y: Day (of year)
  • h: Hour
  • n: Minute
  • m: Month
  • q: Quarter
  • s: Second
  • w: Week (day of week)
  • ww: Calendar week (week of year, 1-53)
  • yyyy: Year
DATEADD("interval", number, Date) This returns a Date value containing a date and time value to which a specified time interval has been added where interval is:
  • d: Day; truncated to integral value
  • y: Day (of year); truncated to integral value
  • h: Hour; rounded to nearest millisecond
  • n: Minute; rounded to nearest millisecond
  • m: Month; truncated to integral value
  • q: Quarter; truncated to integral value
  • s: Second; rounded to nearest millisecond
  • w: Day (of week); truncated to integral value
  • ww: Week (of year); truncated to integral value
  • yyyy: Year; truncated to integral value
DATEDIFF("interval", Date1, Date2) This returns a Long value specifying the difference in time interval units between Date2 and Date1 where interval is:
  • d: Day
  • y: Day (of year, treated same as Day)
  • h: Hour
  • n: Minute
  • m: Month
  • q: Quarter
  • s: Second
  • w: Week
  • ww: Calendar week of year (uses first day of week)
DATESERIAL(Year, Month, Day) Where
  • Year is required. Integer expression from 1 to 9999. However, values below this range are also accepted. If Year is 0 to 99, it is interpreted as being between 1930 and 2029. If Year is less than 1, it is subtracted from the current year.
  • Month is required. Integer expression from 1 to 12. However, values outside this range are also accepted. The value of Month is offset by 1 and applied to January of the calculated year. In other words, (Month - 1) is added to January. The year is recalculated if necessary. The following results illustrate this effect:
    • If Month is 1, the result is January of the calculated year.
    • If Month is 0, the result is December of the previous year.
    • If Month is -1, the result is November of the previous year.
    • If Month is 13, the result is January of the following year.
  • Day is required. Integer expression from 1 to 31. However, values outside this range are also accepted. The value of Day is offset by 1 and applied to the first day of the calculated month. In other words, (Day - 1) is added to the first of the month. The month and year are recalculated if necessary. The following results illustrate this effect:
    • If Day is 1, the result is the first day of the calculated month.
    • If Day is 0, the result is the last day of the previous month.
    • If Day is -1, the result is the penultimate day of the previous month.
    • If Day is past the end of the current month, the result is the appropriate day of the following month. For example, if Month is 4 and Day is 31, the result is May 1.

The following expression also returns one of three values based on the value of Marker, but uses the Switch function instead, which returns the value associated with the first expression in a series that evaluates to TRUE: 

=Switch(Fields!Marker.Value >= 7, "Green", Fields!Marker.Value >= 5, "Amber", Fields!Marker.Value < 5, "Red")

Using Expressions with Counters

The Report Counters facility allows you to use an expression to specify a start number at the start of the report run and increment the number as each document is processed. Specify whether the start number is to be entered at run-time as a parameter or retrieved from a data field value.

You can increment a counter based on the change of a value of a field and specify that it only applies where specific filters are applied at runtime.

When you have created a counter, you can add it to the report. Select View > Special Fields to display the available counters in the Special Fields Browser and drag and drop the counter you require onto the report.