Expressions and built-in collections — SSRS — SQL Server Reporting Services

4. Expressions and built-in collections

Intermediate★ Must knowLesson 4 of 10

Expressions start with = and use VB.NET syntax.

=Fields!Amount.Value                                 ' field
=Sum(Fields!Amount.Value)                            ' aggregate in current scope
=Sum(Fields!Amount.Value, "RegionGroup")             ' aggregate in a named scope
=Fields!Amount.Value / Sum(Fields!Amount.Value, "DataSet1")      ' % of total
=IIf(Fields!Amount.Value < 0, "Red", "Black")        ' conditional formatting (Color property)
=Switch(Fields!Score.Value >= 90, "A", Fields!Score.Value >= 75, "B", True, "C")
=Format(Fields!SaleDate.Value, "dd-MMM-yyyy")
=RunningValue(Fields!Amount.Value, Sum, "RegionGroup")
=RowNumber(Nothing)
=IIf(RowNumber(Nothing) Mod 2 = 0, "WhiteSmoke", "White")     ' green-bar (alternating) rows
=Lookup(Fields!ProductId.Value, Fields!ProductId.Value, Fields!Name.Value, "ProductsDS")   ' join another dataset
=Parameters!Region.Label                             ' parameter display text
=Globals!PageNumber & " of " & Globals!TotalPages
=User!UserID                                         ' current user
=Join(Parameters!Regions.Label, ", ")                ' multi-value parameter labels

Built-in collections: Fields, Parameters, Globals, User, ReportItems, DataSources, DataSets, Variables. Custom code: Report Properties → Code (VB functions called as Code.MyFunction(...)) or custom assemblies.

Lookup, LookupSet and MultiLookup (2008 R2+) join datasets in the report — useful when data comes from two sources.