Reportingservice expression reference

Some expressions are frequently used in reports. This includes changing the data appearance in the report, changing the report item attributes, and the expressions that affect the data retrieval method. This topic describes expressions that can be used for common tasks in reports. For more information, see create an expression in reporting services.

Function

Many expressions in the report contain functions. You can use these functions to set the data format, application logic, and Access Report metadata. You can write and use the Visual Basic Runtime Library, system. convert, and system. Math
The expression of the function in the namespace. You can add a reference to a function from another assembly or custom code. You can also use Microsoft. NET Framework
Including system. Text. regularexpressions. For the Visual Basic supported in expressions
For more information about functions, see "Visual Basic Runtime Library" on msdn.microsoft.com ".

Visual Basic functions

You can use Microsoft Visual Basic
Function to process the data displayed in the text box, or data used in parameters, properties, or other areas of the report. This section illustrates some of the functions. About Visual Basic
For more information about functions, see the Visual Basic documentation.

Date Functions

You can use the Visual Basic function to provide date information in the report.

• The following expression contains the today function for the current date. This expression can be used in the text box to display dates on the report, or in parameters to filter data based on the current date. Copy code

  =Today()

• To provide a date range based on a single parameter, you can use the dateadd function. The following expression provides the date six months after the date in the startdate parameter: copy the code

  =DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)

• Lower
  The following expression shows the year of the year to which the specified date belongs.
  Function. You can use this expression to combine dates, or display the year as a tag for a group of dates. This expression provides a year for a group of dates in a given order. Month
  Functions and other functions can also be used for date operations. For more information, see the Visual Basic. Net documentation. Copy code

  =Year(Fields!OrderDate.Value)

String Functions

You can use the Visual Basic Function to process strings in a report.

• You can use the concatenation operator and Visual Basic constant to combine multiple fields. The following expression returns two fields, which are located in a separate row in the same text box. Copy code

  =Fields!FirstName.Value & vbCrLf & Fields!LastName.Value 

• You can use the format function to set the format of dates and numbers in strings. The following expression displays the values of the startdate and enddate parameters in the long Date Format: copy the code

  =Format(Parameters!StartDate.Value, "D") & " through " &  Format(Parameters!EndDate.Value, "D")  

  If the text box contains only dates or numbers, you should use the format attribute of the text box (instead of using the format function in the text box) to apply format settings.

• The right, Len, and instr functions can be used to return substrings. For example, trim domain/username to only return the username. The following expression returns the string to the right of the backslash (/) character from the parameter named user: copy the code

  =Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "/"))

  The following expression uses a. NET Framework string class member instead of a visual basic function to obtain the same value as the previous expression: copy the code

  =Parameters!User.Value.Substring(Parameters!User.Value.IndexOf("/")+1, Parameters!User.Value.Length-Parameters!User.Value.IndexOf("/")-1)

• The value selected in the multi-value parameter is displayed. The following example uses the join function to concatenate the selected value of the myselection parameter to a single string. You can set this string as the expression of the text box value in the report item. Copy code

  = Join(Parameters!MySelection.Value)

  If the text box contains only dates or numbers, you should use the format attribute of the text box (instead of using the format function in the text box) to apply format settings.

• . Net
  RegEx in framework system. Text. regularexpressions
  The function is used to change the format of an existing string, for example, set the phone number format. The following expression uses the "replace" function to convert the format of the ten phone numbers in the field from 425-555-8080
  Format changed to (425) 555-8080 format: Copy code

  =System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(/d{3})[ -.]*(/d{3})[ -.]*(/d{4})", "($1) $2-$3")

Conversion functions

You can use the Visual Basic Function to convert the data type in the report as needed.

• You can use the Visual Basic Function to convert the data type as needed. Conversion functions are commonly used to eliminate # error messages in reports or set the format of such messages. The following expression shows the number of values selected by myselection. Copy code

  =CStr(Parameters!MySelection.Count)

Decision Functions

You can use the Visual Basic Function to calculate the input value and return another value based on the result.

• The IIF function returns one of the two values based on whether the calculation result of the expression is true. The following expression uses the IIF function. If the value of linetotal exceeds 100, true is returned; otherwise, false is returned. Copy the code.

  =Iif(Fields!LineTotal.Value > 100, True, False)

  The following expression uses multiple IIF functions (also known as the nested IIF function) to return one of the three values based on the pctcomplete value: copy the code

  =Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value >= .5, "Amber", "Red"))

• The following expression returns one of the three values based on the pctcomplete value, but the switch function is used, this function returns the value associated with the first expression in a series of expressions with the calculated result true: copy the code

  =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" when the value is earlier than one week. Otherwise, "blue" is returned ". This expression can be used to control the color attribute of the text box in a report item. Copy code

  =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>7,"Red","Blue")

• The following expression tests the value of the phonenumber field to see if the value is null (nothing in Visual Basic) and returns "null" instead of a null value. This expression can be used to control the value of a text box in a report item. Copy code

  =IIF(Fields!PhoneNumber.Value Is Nothing,"No Value",Fields!PhoneNumber.Value)

• The following expression tests the value of the "department" field and returns the subreport name or null (nothing in Visual Basic ). This expression can be used for conditional drilling subreports. Copy code

  =Iif(Fields!Department.Value = "Development", "EmployeeReport", Nothing)

• The following expression tests whether the field value is null. This expression can be used to control the hidden attribute of image report items. Copy code

  =Iif(IsNothing(Fields!LargePhoto.Value),True,False)

Report Functions

Reporting Services also provides other report functions that can be used to process data in reports. This section provides two examples. For more information about report functions and examples, see using report functions (Reporting Services) in expressions ).

• The sum function can calculate the total values of multiple values in a group or data area. This function can be used in the group header or tail of a table group. The following expression shows the sum of data in the order group or data area: copy the code

  =Sum(Fields!LineTotal.Value, "Order")

• Use an expression containing the rownumber function in the text box of the data area to display the row number of each text box instance where the expression is located. This function can be used to represent the numbers of rows in a table. It can also be used in more complex cases, such as inserting a paging character based on the number of rows. For more information, see "pagination" later in this topic ".
  The following expression shows the row number from the first row to the last row in the outermost data area. The Nothing keyword indicates that the function starts counting from the first row in the outermost data area. To count data from the sub-data area, you can use the name of the data area. Copy code

  =RowNumber(Nothing)

Appearance of report data

You can use expressions to control how data is displayed in a report. For example, you can display the values of two fields in a text box, information about the report, or set the insert mode of pagination characters in the report.

Report header and end

When designing a report, you may need to display the report name and page number at the end of the report table. To do this, you can use the following expression:

• The following expression provides the report name and running time. You can place the expression in the text box at the end of the report table or table body. Its time format is short date format. NET Framework format set string:
  
  
  
  Copy code

  =Globals.ReportName & ", dated " & Format(Globals.ExecutionTime, "d")

• The following expression is located in the text box at the end of the report table. The page number and total page number of the report are provided: copy the code

  =Globals.PageNumber & " of " & Globals.TotalPages

You can also use expressions in the report header or end to represent report items in the table body. The following example shows how to display the first and last values on the page in the header, similar to the form of a directory list. This example assumes that a data area contains a text box named lastname.

• The following expression is located in the text box on the left of the header, providing the first value of the lastname text box on the page: copy the code

  =First(ReportItems("LastName").Value)

• The following expression is located in the text box on the right of the header, providing the last value of the lastname text box on the page: copy the code

  =Last(ReportItems("LastName").Value)

You can apply an aggregate function to report items at the header or end of a table. (However, you cannot reference Aggregate functions for report items in the table body .) The following example shows how to display the total number of pages. This example assumes that a data area contains a text box named cost.

• The following expression is located at the header or end of the table, providing the sum of values in the cost text box on the page: copy the code

  =Sum(ReportItems("Cost").Value)

  Note:
  For each expression at the header or end of a table, only one report item can be referenced.

   

Paging character

In some reports, you may need to insert a pagination character in a specific group or report item after the specified number of rows. To this end, you can create a group in the data area (usually a group close to the outside of the detailed information area), add a paging character in the group, and then add a group expression to group by the specified number of rows.

• If the following expression is used in a group expression, you can specify a number for each 25 rows. If a paging character is defined for the group, this expression inserts a paging character at 25 rows. Copy code

  =Int((RowNumber(Nothing)-1)/25)

Attribute

The expression is used not only to display data in the text box. It can also be used to change the way attributes are applied to report items. You can change the style information of a report item or its visibility.

Format

You can use an expression to change the appearance of report items in a report.

• If the following expression is used in the color attribute of the text box, you can change the color of the text based on the value of the profit field:
  
  
  
  Copy code

  =Iif(Fields!Profit.Value < 0, "Red", "Black")

• If the following expression is used in the backgroundcolor attribute of a report item in the data area, you can change the background color of each row between pale green and white: copy the code

  =Iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")

  If the expression is used for the specified scope, you may need to specify the dataset of the aggregate function: copy the code

  =Iif(RowNumber("Employees") Mod 2, "PaleGreen", "White")

Visibility

You can use the visibility attribute of report items to display and hide the items in the report. In data areas such as tables, you can hide detailed information rows at the beginning based on the values in the expressions.

• If the following expression is used for the initial visibility of detailed information rows in a group, it can display the detailed information rows of all sales whose pctquota field value exceeds 90%: copy the code

  =Iif(Fields!PctQuota.Value>.9, False, True)

• The following expression (if set in the "visibility" and "hidden" attributes of the table) only displays tables containing at least 12 rows: Copy code

  =IIF(CountRows()>12,true,false)

Report data

You can use expressions to process the data used in the report. Parameters and other report information can be referenced. You can even modify the queries used to retrieve report data.

Parameters

You can use an expression to change the default value of a parameter. For example, you can use parameters to filter data of a specific user based on the user ID used to run the report.

• If the following expression is used as the default parameter value, you can collect the ID of the user who runs the report:
  
  
  
  Copy code

  =User!UserID

• You can use the following expressions to reference parameters in a query parameter, filter expression, text box, or other report area. This example assumes that the parameter name is user: copy the code

  =Parameters!User.Value

Custom Code

You can use custom code in a report. Custom Code is embedded in a report or stored in a custom Assembly used by the report. For more information about custom code, see use custom code reference (Reporting Services) in expressions ).

• The following example calls the tousd embedded code method, which converts the value of the standardcost field to the dollar value: copy the code

  =Code.ToUSD(Fields!StandardCost.Value)

• The following example shows how to define some custom constants and variables.
  [Visual Basic] copying code

  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 constant view (only built-in constants are displayed), you can add references to any expressions, as shown in the following example. These references are processed as variables.
  [Visual Basic] copying code

  =Code.MyNote
      =Code.NCopies
      =Code.MyVersion
      =Code.MyDoubleVersion

• The following example calls the embedded code method fixspelling, which usesBicycleReplace allBikeText. Copy code

  =Code.FixSpelling(Fields!SubCategory.Value)

  After the report definition is embedded, the following code shows the implementation of the fixspelling method. MessageBox
  The replaced text is displayed. This example shows how to reference the. NET Framework stringbuilder class and
  System. Windows. Forms. MessageBox class. You must be system. Windows. Forms
  Add reference to report properties. For more information, see How to add code to a report (Report designer) and how to add Assembly references to a report (Report designer ).
  [Visual Basic] copying code

  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