Formulas

Expressions and Formulas

Majorsilence Reporting evaluates expressions written in VB.NET syntax. Expressions appear wherever a property accepts a dynamic value — cell content, colors, visibility, sort order, and more. Any expression must begin with =.

Shorthand field notation

v5 introduced a compact shorthand to reduce noise in expressions:

Shorthand	Full VB.NET equivalent	Notes
{FieldName}	Fields!FieldName.Value	Dataset field
{?ParamName}	Parameters!ParamName.Value	Report parameter
{!UserFieldName}	User!UserFieldName	Built-in user fields
{@GlobalName}	Globals!GlobalName	Built-in global values

The shorthand can be used anywhere inside a larger expression:

="Order #" & {OrderId} & " for " & {?CustomerName}

Globals

Global	Type	Value
{@PageNumber}	Integer	Current page number
{@TotalPages}	Integer	Total page count
{@ReportName}	String	Name of the report
{@ExecutionTime}	DateTime	When the report started

String functions

=Left({CustomerName}, 10)           ' first 10 characters
=Right({AccountNumber}, 4)          ' last 4 characters
=Mid({Description}, 3, 5)          ' 5 chars starting at position 3
=Len({Notes})                       ' character count
=UCase({StatusCode})                ' upper case
=LCase({Email})                     ' lower case
=Trim({RawInput})                   ' strip leading/trailing spaces
=Replace({Phone}, "-", "")          ' remove dashes
=Format({Amount}, "C2")             ' currency: $1,234.56
=Format({Ratio}, "P1")              ' percentage: 42.3%

Numeric functions

=Round({Amount}, 2)                 ' round to 2 decimal places
=Abs({Variance})                    ' absolute value
=Int({Score})                       ' truncate to integer
=Ceiling({Units})                   ' round up to next integer
=Floor({Rate})                      ' round down to next integer
=Math.Max({Low}, {High})            ' larger of two values

Date functions

=Now()                              ' current date and time
=Today()                            ' current date only
=Year({OrderDate})                  ' year part: 2024
=Month({OrderDate})                 ' month part: 1–12
=Day({OrderDate})                   ' day part: 1–31
=DateAdd("d", 30, {StartDate})      ' add 30 days
=DateDiff("d", {StartDate}, {EndDate})  ' days between two dates
=Format({OrderDate}, "yyyy-MM-dd")  ' formatted date string
=Format({OrderDate}, "MMMM d, yyyy") ' e.g. January 15, 2024

Date interval codes: "d" = days, "m" = months, "yyyy" = years, "h" = hours, "n" = minutes, "s" = seconds.

Conditional expressions

' IIf: two-branch conditional (always evaluates both branches)
=IIf({Quantity} > 0, "In Stock", "Out of Stock")

' Switch: multiple conditions, evaluates left to right, returns first true
=Switch(
    {Score} >= 90, "A",
    {Score} >= 80, "B",
    {Score} >= 70, "C",
    True,          "F"
)

Aggregate functions

Aggregates summarise values across a dataset or a named group scope.

=Sum({Amount})                      ' sum of Amount in current scope
=Count({OrderId})                   ' count of rows
=CountDistinct({CustomerId})        ' count of unique values
=Avg({Rating})                      ' average
=Max({SalePrice})                   ' maximum
=Min({SalePrice})                   ' minimum
=First({CustomerName})              ' first value in scope
=Last({CustomerName})               ' last value in scope

Scope parameter

Pass a dataset or group name as the second argument to aggregate across a different scope than the current one:

=Sum({LineTotal}, "OrderLines")     ' sum over the OrderLines dataset
=Sum({Amount}, "CategoryGroup")     ' sum over a named group

See Grouping and Aggregates for how scopes are defined.

Combining expressions

Any VB.NET operator or string method works inside expressions:

' String concatenation
=Fields!FirstName.Value & " " & Fields!LastName.Value

' Arithmetic
=({UnitPrice} * {Quantity}) * (1 - {Discount})

' Null coalescing
=IIf(IsNothing({Notes}), "—", {Notes})

' Type conversion
=CInt({StringId})
=CDate({DateString})
=CStr({NumericCode})

Where expressions appear

• Value property of a Textbox — the cell's content
• Color / BackgroundColor — for conditional formatting (see Conditional Formatting)
• Visibility/Hidden — =IIf({IsAdmin}, False, True) to show/hide items
• SortExpression — control sort order in tables
• Label of a chart axis or series
• GroupExpression — the value on which rows are grouped