 Functions are really what make expressions so powerful. If SSRS did not have functions, developers would not be able to manipulate data in any of the collections. SSRS comes with two generic types of built-in functions. The first are the functions used for aggregations of data. The rest are functions having to do with scope. SSRS also allows you to reference any of the standard or custom assemblies that come with the Common Language Runtime. In fact, three of them are already referenced for you: Microsoft.VisualBasic , System.Math , and System.Convert . Lastly, SSRS gives you the ability to write your own functions using custom code blocks (in VB.NET). Visual Basic Runtime Functions Runtime functions are provided through Microsoft .NET library's namespaces: Microsoft.VisualBasic , System.Math , and System.Convert . Note that the Visual Basic namespace adds a couple of functions to System.Math ; one of those functions is the random number generator, Rnd . The functions fall in the following categories: Conversion (for example, Str ), DateAndTime (for example, DateAdd ), Financial (for example, NPV [net present value] calculation), Inspection (for example, IsNull ), Interaction (for example, IIF ; Expression Editor includes this in the Program Flow group ), Strings (for example, Len ), and Mathematical (for example, Ceiling). Expression Editor displays a subset of all available functions. However, Intellisense recognizes all available functions and provides signature information. One of the functions that is not available for selection inside Expression Editor is Partition(Input, RangeStart, RangeEnd, Interval) . This function evaluates an Input expression and returns string "#:#" that represents a partition where number is located. For example, Partition(56, 0, 100, 5) returns the string "55:59". This function can be used to group expressions within a range of values. The set of functions outlined in this chapter is the set that SSRS allows to execute by default. By default, all reports run with ExecuteOnly permission. This means, for example, that functions such as Dir that access file system functionality will not execute by default. However, an administrator can give report processing additional permissions by editing the CAS policy file, but this would be applied to all reports on the server. In this case, the report publishing right must be restricted to the trusted personnel. This is because anybody who has the right to publish will be able to execute any method, which can result in security- related issues. Table 8.2 provides a comprehensive list of available functions, categorized by their most likely usage. Table 8.2. Available Script Functions | Conversion Functions Action | Functions | | ANSI value to string | Chr , ChrW | | String to lowercase or uppercase | Format , LCase , UCase | | Date to serial number | DateSerial , DateValue | | Decimal number to other bases | Hex , Oct | | Number to string | Format , Str | | One data type to another | CBool , CByte , CDate , CDbl , CDec , CInt , CLng , CSng , CShort , CStr , CType , Fix , Int | | Date to day, month, weekday, or year | Day , Month , Weekday , Year | | Time to hour , minute, or second | Hour , Minute , Second | | String to ASCII value | Asc , AscW | | String to number | Val | | Time to serial number | TimeSerial , TimeValue | | Partition where parameter is located | Partition | | Date and Time Functions | | | Action | Functions | | Get the current date or time. | Now , Today , TimeOfDay | | Perform date calculations. | DateAdd , DateDiff , DatePart | | Return a date. | DateSerial , DateValue , MonthName , WeekDayName | | Return a time. | TimeSerial , TimeValue | | Set the date or time. | DateString , TimeOfDay , TimeString , Today Chr , ChrW | | Time a process. | Timer | | String Manipulation Functions | | | Action | Functions | | Compare two strings. | StrComp | | Convert strings. | StrConv | | Reverse a string. | InStrRev , StrReverse | | Convert to lowercase or uppercase. | Format , LCase , UCase | | Create a string of repeating characters . | Space , StrDup | | Find the length of a string. | Len | | Format a string. | Format , FormatCurrency , FormatDateTime , FormatNumber , FormatPercent | | Manipulate strings. | InStr , Left , LTrim , Mid , Right , RTrim , trim | | Work with ASCII and ANSI values. | Asc , AscW , Chr , ChrW | | Replace a specified substring. | Replace | | Return a filter-based string array. | Filter | | Return a specified number of substrings. | Split , Join | | Financial Functions | | | Action | Functions | | Depreciation | DDB , SLN , SYD | | Future value | FV | | Interest rate | Rate | | Internal rate of return | IRR , MIRR | | Number of periods | NPer | | Payments | IPmt , Pmt , PPmt | | Present value | NPV , PV | | Math Functions | | | Action | Functions | | Random number generation. | Randomize , Rnd | | Absolute value and sign of a specified number | Abs , Sign | | Reverse trigonometric functions. For example, Acos returns an angle for a specified Cos value. | Acos , Asin , Atan , Atan2 | | Produce the full product of two 32-bit numbers. Multiplies two big numbers . | BigMul | | Smallest/Largest/Nearest whole number greater/less than or equal to the specified number. | Ceiling / Floor / Round | | Trigonometric functions | Cos , Cosh , Sin , Sinh , Tan , Tanh | | Quotient (division result) of two numbers, also passing the remainder as an output parameter. | DivRem | | Result of e raised to the specified power. | Exp | | Remainder resulting from the division of a specified number by another specified number. | IEEERemainder | | Logarithm and Base 10 Log of a specified number. | Log , Log10 | | Larger and smallest of two specified numbers. | Max , Min | | Result of specified number raised to the specified power. | Pow | | Square root of a specified number. | Sqrt | | Information Functions | | | Action | Functions | | Check if the parameter of the specified type. | IsNothing (or IS Nothing ), IsDate , IsNumeric , IsError , IsArray , IsReference | | Examine array bounds. | LBound , UBound | | Examine the type of the expression, return the string name of the type. | TypeName |
Function IsNothing can be used to check for the Null values; for example, an expression =IsNothing(Fields!SalesPersonId.Value) checks if SalesPersonId is equal to Null . This expression is equivalent to =Fields!SalesPersonId.Value IS Nothing (that is, comparison to the keyword Nothing ). Do not use IsDBNull in your expressions; this function does not have useful applications in SSRS expressions. Table 8.3 lists the functions related to program flow. These functions help with programmatic decision making. Table 8.3. Program Flow Functions | Function | Description | Example | | Choose | Selects and returns a value from a list of arguments. | = CStr(Choose(Value, "Red", "Yellow", " Green ")) returns color depending on the Value (1-3). This example can be used to control "traffic light" highlight. | | IIf | Selects and returns one of two objects, depending on the evaluation of an expression. | = IIf(TestMe < 0, "Red", "Green") returns color, depending on the value. This example can be used to highlight negative values. | | Switch | Evaluates a list of expressions and returns an Object value of an expression associated with the first expression in the list that is True. | =Switch(Fields!City.Value = "London", "English (United Kingdom)", Fields!City.Value = "Rome", "Italian (Italy)", Fields!City.Value = " Paris ", "French (France)") . Returns locale depending on the city. Can be used in the Language property to provide appropriate format defaults. |
Aggregate Functions Aggregate functions are used to aggregate data over a certain scope. Some examples of these types of operations are Sum and Average . Aggregate functions are very simple to use. The big trick to them is scope. All aggregate functions contain scope as a parameter. If scope is not entered, it defaults based on a number of criteria. Because of this defaulting, you will usually see aggregates used without a reference to scope. For example, with a sum in a table or a matrix, the scope is assumed to be the innermost grouping within the table or matrix. Outside of a data region, the scope is simply the name of the data set. For example, if you had a text box outside a table in which you intended to put a total sales value, you would put the following expression inside the text box value property: =Sum(Fields!Sales.Value,"SalesDataset")
If a report has only one data set, the scope parameter does not need to be specified. If you are in a data region, and you want to override the default group for an aggregate, simply give the group name you want or Nothing to specify the outermost grouping available. With that out of the way, Table 8.4 provides a list of available aggregate functions. Table 8.4. Aggregate Functions | Function Signature | Expression Type | Return Type | Description | | Aggregate (Expression,[Scope]) | | | Returns a custom aggregate of the specified expression, as defined by the data provider | | Avg(Expression, [Scope, [Recursive]]) | Float | Float | Returns the average of all nonnull values from the specified expression | | Count(Expression, [Scope, [Recursive]]) | Object | Integer | Returns a count of all nonnull values of the specified expression | | CountDistinct(Expression,[Scope, [Recursive]]) | Object | Integer | Returns a count of all distinct values from the specified expression | | CountRows([Scope], [Recursive]) | N/A | Integer | Returns a count of rows within the specified scope | | First(Expression, [Scope]) | Object | Same as type of Expression | Returns the first value from the specified expression after all sorting has been applied to the data | | Last(Expression, [Scope]) | Object | Same as type of Expression | Returns the last value from the specified expression after all sorting has been applied to the data | | Max(Expression, [Scope, [Recursive]]) | Object | Same as type of Expression | Returns the maximum value from all nonnull values of the specified expression | | Min(Expression, [Scope, [Recursive]]) | Object | Same as type of Expression | Returns the minimum value from all nonnull values of the specified expression | | StDev(Expression, [Scope,[Recursive]]) | Integer or Float | Float | Returns the standard deviation of all nonnull values of the specified expression | | StDevP(Expression, [Scope, [Recursive]]) | Integer or Float | Float | Returns the population standard deviation of all nonnull values of the specified expression | | Sum(Expression, [Scope, [Recursive]]) | Integer or Float | Float, Decimal, or Double [*] | Returns a sum of the values of the specified expression | | Var(Expression, [Scope, [Recursive]]) | Integer or Float | Float, Decimal, or Double [*] | Returns the variance of all nonnull values of the specified expression | | VarP(Expression, [Scope, [Recursive]]) | Integer or Float | Float, Decimal, or Double [*] | Returns the population variance of all nonnull values of the specified expression |
[*] Depending on the type of expression [*] Depending on the type of expression [*] Depending on the type of expression An aggregate function can be used in expressions for any report item. Call syntax for the majority of aggregate functions is: =Function(Expression,[Scope, [Recursive]])
This syntax indicates that both Scope and Recursive parameters can be omitted, but if Recursive is specified, then scope also has to be present. Aggregate functions cannot call other aggregate functions. -
Expression A valid SSRS expression on which to perform the aggregation. The expression cannot contain aggregate functions. -
Function A name of an aggregate function. -
Recursive A modifier directing aggregate function to include aggregations from lower levels plus aggregation of the current level. -
Scope String, the name of a dataset, grouping, or data region to which an aggregate function is applied. |
