Expression Examples in Reporting Services

Expression Examples in Reporting Se...

Community Content

In this section

Statistics Annotations (2)

Click here to expand

Collapse All

Language Filter

Want more? Here are some additional resources on this topic:

SQL Server 2005 Books Online (November 2008)

Expression Examples in Reporting Services

Some expressions are frequently used in reports. These include expressions to change the appearance of data in a report, change properties of report items, and affect how data is retrieved. This topic describes some expressions that can be used for common tasks in a report. For more information, see Creating Expressions in Reporting Services.

Functions

Many expressions in a report contain functions. You can format data, apply logic, and access report metadata using these functions. You can write expressions that use functions from the Visual Basic Run-Time Library, and from the System.Convert and System.Math namespaces. You can add references to functions from other assemblies or custom code. You can also use classes from the Microsoft .NET Framework, including System.Text.RegularExpressions. For more information about Visual Basic functions supported in expressions, see "Visual Basic Run-Time Library" at msdn.microsoft.com.

Visual Basic Functions

You can use Microsoft Visual Basic functions to manipulate the data that is displayed in text boxes or that is used for parameters, properties, or other areas of the report. This section provides examples demonstrating some of these functions. For more information about Visual Basic functions, see the Visual Basic documentation.

Date Functions

You can use Visual Basic functions to provide date information in your report.

The following expression contains the Today function, which provides the current date. This expression can be used in a text box to display the date on the report, or in a parameter to filter data based on the current date.
```
=Today()
```
The DateAdd function is useful for supplying a range of dates based on a single parameter. The following expression provides a date that is six months after the date from a parameter named StartDate.
```
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)
```
The following expression contains the Year function, which displays the year for a particular date. You can use this to group dates together or to display the year as a label for a set of dates. This expression provides the year for a given group of order dates. The Month function and other functions can also be used to manipulate dates. For more information, see the Visual Basic .NET documentation.
```
=Year(Fields!OrderDate.Value)
```

String Functions

You can use Visual Basic functions to manipulate strings in your report.

You can combine more than one field by using concatenation operators and Visual Basic constants. The following expression returns two fields, each on a separate line in the same text box.
```
=Fields!FirstName.Value & vbCrLf & Fields!LastName.Value 
```
You can format dates and numbers in a string with the Format function. The following expression displays values of the StartDate and EndDate parameters in long date format.
```
=Format(Parameters!StartDate.Value, "D") & " through " &  Format(Parameters!EndDate.Value, "D")  
```
If the text box contains only a date or number, you should use the Format property of the text box to apply formatting instead of the Format function within the text box.
The Right, Len, and InStr functions are useful for returning a substring, for example, trimming DOMAIN\username to just the user name. The following expression returns the part of the string to the right of a backslash (\) character from a parameter named User:
```
=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))
```
The following expression results in the same value as the previous one, using members of the .NET Framework String class instead of Visual Basic functions:
```
=Parameters!User.Value.Substring(Parameters!User.Value.IndexOf("\")+1, Parameters!User.Value.Length-Parameters!User.Value.IndexOf("\")-1)
```
You can display the selected values from a multivalue parameter. The following example uses the Join function to concatenate the selected values of the parameter MySelection into a single string that can be set as an expression for the value of a text box in a report item.
```
= Join(Parameters!MySelection.Value)
```
If the text box contains only a date or number, you should use the Format property of the text box to apply formatting instead of the Format function in the text box.
The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from the form nnn-nnn-nnnn to the form (nnn) nnn-nnnn:
```
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")
```

Conversion Functions

You can use Visual Basic functions to convert data types as needed in your report.

You can use Visual Basic functions to convert data types as needed. Convert functions are frequently used to eliminate or format #Error messages in a report. The following expression displays the number of values selected for the multivalue parameter MySelection.
```
=CStr(Parameters!MySelection.Count)
```

Decision Functions

You can use Visual Basic functions to evaluate an input value and return another value depending on the result.

The Iif function returns one of two values depending on whether the expression evaluated is true or not. The following expression uses the Iif function to return a Boolean value of True if the value of LineTotal exceeds 100. Otherwise it returns False:
```
=Iif(Fields!LineTotal.Value > 100, True, False)
```
The following expression uses multiple Iif functions (also known as "nested Iifs") to return one of three values depending on the value of PctComplete.
```
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value >= .5, "Amber", "Red"))
```
The following expression also returns one of three values based on the value of PctComplete, but uses the Switch function instead, which returns the value associated with the first expression in a series that evaluates to true:
```
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value >= .5, "Amber", Fields!PctComplete.Value < .5, "Red")
```
The following expression tests the value of the ImportantDate field and returns "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item.
```
=IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>7,"Red","Blue")
```
The following expression tests the value of a field PhoneNumber to see whether it is null (Nothing in Visual Basic) and returns "No Value" instead of the null value. This expression can be used to control the value of a text box in a report item.
```
=IIF(Fields!PhoneNumber.Value Is Nothing,"No Value",Fields!PhoneNumber.Value)
```
The following expression tests the value of the Department field and returns either a subreport name or a null (Nothing in Visual Basic). This expression can be used for conditional drillthrough subreports.
```
=Iif(Fields!Department.Value = "Development", "EmployeeReport", Nothing)
```
The following expression tests if a field value is null. This expression can be used to control the Hidden property of an image report item.
```
=Iif(IsNothing(Fields!LargePhoto.Value),True,False)
```

Report Functions

Reporting Services provides additional report functions that you can use to manipulate data in a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).

The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:
```
=Sum(Fields!LineTotal.Value, "Order")
```
An expression containing the RowNumber function, when used in a text box within a data region, displays the row number for each instance of the text box in which the expression appears. This function can be useful to number rows in a table. It can also be useful for more complex tasks, such as providing page breaks based on number of rows. For more information, see "Page Breaks" later in this topic.
The following expression displays the row number from the first row in the outermost data region to the last. The Nothing keyword indicates that the function will start counting at the first row in the outermost data region. To start counting within child data regions, use the name of the data region.
```
=RowNumber(Nothing)
```

Appearance of Report Data

You can use expressions to manipulate how data appears on a report. For example, you can display the values of two fields in a single text box, display information about the report, or affect how page breaks are inserted in the report.

Page Headers and Footers

When designing a report, you may want to display the name of the report and page number in the report footer. To do this, you can use the following expressions:

The following expression provides the name of the report and the time it was run. It can be placed in a text box in the report footer or in the body of the report. The time is formatted with the .NET Framework formatting string for short date:
```
=Globals.ReportName & ", dated " & Format(Globals.ExecutionTime, "d")
```
The following expression, placed in a text box in the footer of a report, provides page number and total pages in the report:
```
=Globals.PageNumber & " of " & Globals.TotalPages
```

You can also use expressions in the report header or footer to report items from the body of the report. The following examples describe how to display the first and last values from a page in the page header, similar to what you might find in a directory listing. The example assumes a data region that contains a text box named LastName.

The following expression, placed in a text box on the left side of the page header, provides the first value of the LastName text box on the page:
```
=First(ReportItems("LastName").Value)
```
The following expression, placed in a text box on the right side of the page header, provides the last value of the LastName text box on the page:
```
=Last(ReportItems("LastName").Value)
```

You can apply aggregates to a report item reference in a page header or footer. (However, you cannot apply an aggregate to a report item reference in the report body.) The following example describes how to display a page total. The example assumes a data region that contains a text box named Cost.

The following expression, placed in the page header or footer, provides the sum of the values in the Cost text box for the page:
```
=Sum(ReportItems("Cost").Value)
```
Note:
You can refer to only one report item per expression in a page header or footer.

Note:
You can refer to only one report item per expression in a page header or footer.

Page Breaks

In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group in a data region (typically a group immediately outside the detail), add a page break to the group, and then add a group expression to group by a specified number of rows.

The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.
```
=Int((RowNumber(Nothing)-1)/25)
```

Properties

Expressions are not only used to display data in text boxes. They can also be used to change how properties are applied to report items. You can change style information for a report item, or change its visibility.

Formatting

You can use expressions to vary the appearance of report items in a report.

The following expression, when used in the Color property of a text box, changes the color of the text depending on the value of the Profit field:
```
=Iif(Fields!Profit.Value < 0, "Red", "Black")
```
The following expression, when used in the BackgroundColor property of a report item in a data region, alternates the background color of each row between pale green and white:
```
=Iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")
```
If you are using an expression for a specified scope, you may have to indicate the dataset for the aggregate function:
```
=Iif(RowNumber("Employees") Mod 2, "PaleGreen", "White")
```

Visibility

You can show and hide items in a report using the visibility properties for the report item. In a data region such as a table, you can initially hide detail rows based on the value in an expression.

The following expression, when used for initial visibility of detail rows in a group, shows the detail rows for all sales exceeding 90 percent in the PctQuota field:
```
=Iif(Fields!PctQuota.Value>.9, False, True)
```
The following expression, when set in the Visibility, Hidden property of a table, shows the table only if it has more than 12 rows:
```
=IIF(CountRows()>12,true,false)
```

Report Data

Expressions can be used to manipulate the data that is used in the report. You can refer to parameters and other report information. You can even change the query that is used to retrieve data for the report.

Parameters

You can use expressions in a parameter to vary the default value for the parameter. For example, you could use a parameter to filter data to a particular user based on the user ID that is used to run the report.

The following expression, when used as the default value for a parameter, collects the user ID of the person running the report:
```
=User!UserID
```
You can use the following expression to refer to the parameter in a query parameter, filter expression, text box, or other areas of the report. This example assumes that the parameter is named User:
```
=Parameters!User.Value
```

Custom Code

You can use custom code in a report. Custom code is either embedded in a report or stored in a custom assembly which is used in the report. For more information about custom code, see Using Custom Code References in Expressions (Reporting Services).

The following example calls the embedded code method ToUSD, which converts the StandardCost field value to a dollar value:
```
=Code.ToUSD(Fields!StandardCost.Value)
```
The following example shows how to define some custom constants and variables.
[Visual Basic]
```
Public Const MyNote = "Authored by Bob"
Public Const NCopies As Int32 = 2
Public Dim  MyVersion As String = "123.456"
Public Dim MyDoubleVersion As Double = 123.456
```
Although custom constants and variables do not appear in the Expression Editor Constants view (which only displays built-in constants), you can add references to them from any expression, as shown in the following examples. These are treated as Variants.
[Visual Basic]
```
=Code.MyNote
=Code.NCopies 
=Code.MyVersion
=Code.MyDoubleVersion
```
The following example calls the embedded code method FixSpelling, which substitutes Bicycle for all occurrences of the text Bike in SubCategory.Value.
```
=Code.FixSpelling(Fields!SubCategory.Value)
```
The following code, when embedded in a report definition, shows an implementation of the FixSpelling method. The first time this custom code runs, a MessageBox displays the substituted text. This example shows you how to refer to the .NET Framework StringBuilder class and the System.Windows.Forms.MessageBox class. You must add a reference to your report properties for System.Windows.Forms. For more information, see How to: Add Code to a Report (Report Designer) and How to: Add an Assembly Reference to a Report (Report Designer).
[Visual Basic]
```
Dim firstTime As Boolean = True 
Public Function FixSpelling(ByVal s As String) As String
   Dim strBuilder As New System.Text.StringBuilder(s)
   If s.Contains("Bike") Then
      strBuilder.Replace("Bike", "Bicycle")
      If firstTime Then
        System.Windows.Forms.MessageBox.Show(strBuilder.ToString())
'       or MsgBox(strBuilder.ToString())
        firstTime = False
      End If
      Return strBuilder.ToString()
      Else : Return s
   End If
End Function
```

Concepts

Using Report Functions in Expressions (Reporting Services)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Changed content: Added examples: regular expression for telephone number; custom constant and variables; text box color; multiparameter selection and selection count. Testing for nulls.
5 December 2005	Changed content: Added custom code example.