String functions
- Use the concatenation operator and the Visual Basic constants to group multiple fields together. The following expression returns two fields, each in a different row of the same text box:
Copy Code
=fields! Firstname.value & VbCrLf & fields! Lastname.value
- You can format dates and numbers in a string by using the Format function. The following expression displays the values of the startdate and EndDate parameters in a long date format:
Copy 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 property of the text box to apply formatting instead of using the Format function in the text box.
- the right,Len , and InStr functions can be used to return substrings, such as trimming DOMAIN\username to return only the user name. The following expression returns the string part to the right of the backslash (\) character from a parameter named User :
Copy Code
=right (parameters! User.value, Len (parameters! User.value)-InStr (parameters! User.value, "\"))
The following expression uses a member of the. NET Framework System.String class instead of a Visual Basic function to get the same value as the previous expression:
Copy Code
=parameters! User.Value.Substring (parameters! User.Value.IndexOf ("\") +1, parameters! user.value.length-parameters! User.Value.IndexOf ("\")-1)
- Displays the selected value for the multi-valued 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 of the value of a text box in a report item:
Copy Code
= Join (parameters! Myselection.value)
- The Regex function in the. NET Framework System.Text.RegularExpressions is useful for changing the format of existing strings, such as formatting phone numbers. The following expression uses the Replace function to change the format of the 10-digit phone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn -nnnn":
Copy Code
=system.text.regularexpressions.regex.replace (fields! Phone.value, "(\d{3}) [-.] * (\d{3}) [-.] * (\d{4}) "," ($) $2-$3 ")
Attention: |
Verify fields! The value of Phone.value has no extra spaces and the type is System.String. |
Conversion functions
You can use Visual Basic functions to convert a field from one data type to another with a different data type. Conversion functions can be used to convert the default data type of a field to the data type needed for the calculation or to combine text.
- The following expression converts a constant of 500 to a Decimal type to compare it to the Transact-SQL Money data type in the filter Expression Value field.
Copy Code
=cdec (500)
- The following expression displays the number of values selected for the multivalued parameter myselection .
Copy Code
=cstr (parameters! Myselection.count)
Decision function
- The Iif function returns one of two values based on the evaluated result (True or False) of the expression. The following expression uses the Iif function to return a Boolean value of Truewhen the value of LineTotal exceeds 100. Otherwise, it will return False:
Copy Code
=iif (fields! Linetotal.value >, True, False)
- Using multiple iif functions (also known as "nested IIF") can return one of three values based on the value of Pctcomplete. The following expression can be placed in the fill color of a text box to change the background color based on the value in the text box.
Copy Code
=iif (fields! Pctcomplete.value >=, "Green", IIF (fields! Pctcomplete.value >= 1, "Blue", "Red")
A value greater than or equal to 10 o'clock displays a green background with a blue background between 1 and 9, and less than 1 o'clock, which shows a red background.
- There is another way to implement the same function, which is to use the Switch function. TheSwitch function is useful if you want to test three or more conditions. The Switch function returns a value associated with the first expression that evaluates to True in the sequence:
Copy Code
=switch (fields! Pctcomplete.value >=, "Green", fields! Pctcomplete.value >= 1, "Blue", fields! Pctcomplete.value = 1, "Yellow", fields! Pctcomplete.value <= 0, "Red",)
A value greater than or equal to 10 o'clock displays a green background, a blue background between 1 and 9, a yellow background at 1 o'clock, or less than or equal to 0 o'clock, which shows a red background.
- The third way to achieve the same functionality is to use the Choose function. The Choose function uses the first parameter as the index of one of the remaining function parameters. The first argument must be an integer. If you set the background color of a text box in a table to this expression, the value of Myindex controls the color.
Copy Code
=choose (fields! Myindex.value, "Red", "Green", "Yellow")
For example, when Myindex is 1 o'clock, the background color is "Red". In the query result set, verify that the Myindex data is not outside the scope of the parameter list.
- Tests the value of the Importantdate field, returns "Red" if the value is greater than one week, or returns "Blue". This expression can be used to control the Color property of a text box in a report item:
Copy Code
=iif (DateDiff ("D", fields! Importantdate.value, now ()) >7, "Red", "Blue")
- Tests the value of the PhoneNumber field, returns "No value" if null(Nothing in Visual Basic), or returns the phone number 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)
- Test the value of the Department field and return the subreport name or null(Nothing in Visual Basic ). This expression can be used for conditional drillthrough subreports.
Copy Code
=iif (fields! Department.value = "Development", "Employeereport", Nothing)
- Tests whether the field value is empty. This expression can be used to control the Hidden property of an image report item. In the following example, the image specified by the field [LargePhoto] is displayed only if the field value is not empty.
- 1. Connection characters
- =fields! Firstname.value + fields! Lastname.value connect two fields in a cell, and a row displays
- =fields! Firstname.value & VbCrLf & fields! Lastname.value connection Two fields are displayed in one cell, but line breaks appear
- 2. An If expression uses
- =iif (fields! Linetotal.value >, True, False) if an expression that executes True is set, the False expression is executed, and I can write other expressions instead of the code.
- 3. The count expression uses
- =count (fields! Dailycount. Value, "weldingreportdataset_t_welderdaily") calculates how many rows are dailycount in the weldingreportdataset_t_welderdaily data set
- 3. The sum expression uses
- =sum (fields! Dailycount. Value, "weldingreportdataset_t_welderdaily") calculates the sum of Dailycount in the weldingreportdataset_t_welderdaily data set
- 4. The format expression uses the
- =cdate (fields! Topaintdate. Value). ToString ("dd MMM yyyy") applies the format of date
RDLC Report Functions
