[Microsoft BI] Functions In SSRS

 

Functions in SSRS

Built-in Functions of SSRS

 

In Reporting Services, expressions are used to provide dynamic flexibility for controlling the content and appearance of a report. Some expressions are created for you automatically. For example, when you create a new report and drag fields from datasets onto report items, the values of text boxes are automatically set to expressions that refer to the dataset fields by name. During report processing, each expression evaluates to a single value that replaces the expression when a report is rendered. Expressions are also used throughout the report definition to specify or calculate values for report item properties, text box properties, parameters, queries, bookmarks, document maps, filters, and group and sort definitions. 

You create expressions on report items by entering text in text boxes, in properties, or through a dialog box. An expression is written in Microsoft Visual Basic. An expression begins with an equal sign (=) and consists of references to constants, operators, functions, and built-in report global collections including fields and parameters. You can also create references to .NET Framework classes, custom code, and assemblies external to report processing.

 

Examples

Aggregate(field expr [,scope]) 
Returns an array containing the values of the grouped field. For example, =Code.AggrToString(Aggregate(Fields!Year.Value)) // In Code element Public Function AggrToString(o as object) As String Dim ar as System.Collections.ArrayList = o Dim sb as System.Text.StringBuilder = New System.Text.StringBuilder Dim n as Integer For n = 0 To ar.Count-1 sb.Append(ar(n)) If n <> 

Asc(string)
Converts the first letter in the passed string to ANSI code. 

Avg(field expr [,scope]) 
Returns the average value of the grouped field. Returns decimal if the argument type is decimal, otherwise double. 

CBool(object) 
Converts the passed argument to Boolean. 

CByte(string) 
Converts the passed argument to Byte. 

CCur(string) 
Converts the argument to type Currency (really Decimal). 

Choose(number, expr1, expr2, ... exprn) 
Evaluates the number and return the result of the coorespodning exprn. For example, if number results in 3 then expr3 is returned. 

CDate(string) 
Converts a string to type DateTime 

CDbl(object) 
Converts the passed parameter to double. 

Chr(int) 
Converts the specified ANSI code to a character. 

CInt(object) 
Converts the argument to integer. 

CLng(object) 
Converts the argument to long. 

Count(field expr [,scope]) 
Returns the number of values in the grouped field. Null values don't count. 

Countrows([scope]) 
Returns the number of rows in the group. 

Countdistinct(field expr [,scope]) 
Returns the number distinct values in the grouped field. Null values don't count. 

CSng(object) 
Converts the argument to Single. 

CStr(object) 
Converts the argument to String. 

Day(datetime) 
Returns the integer day of month given a date. 

First(field expr [,scope]) 
Returns the first value in the group. 

Format(string1 [,string2) 
Format string1 using the format string2. Some valid formats include '#,##0', '$#,##0.00', 'MM/dd/yyyy', 'yyy-MM-dd HH:mm:ss'... string2 is a .NET Framework formatting string. 

Hex(number) 
Returns the hexadecimal value of a passed number. 

Hour(datetime) 
Returns the integer hour given a date/time variable. 

Iif(bool-expr, expr2, expr3) 
The Iif function evaluates bool-expr and when true returns the result of expr2 otherwise the result of expr3. expr2 and expr3 must be the same data type. 

InStr([ioffset,] string1, string2 [,icase]) 
1 based offset of string2 in string1. You can optionally pass an integer offset as the first argument. You can also optionally pass a 1 as the last argument if you want the search to be case insensitive. 

InStrRev(string1, string2[,offset[,case]]) 
1 based offset of string2 (second argument) in string1 (first argument) starting from the end of string1. You can optionally pass an integer offset as the third argument. You can also optionally pass a 1 as the fourth argument if you want the search to be case insensitive. 

Last(field expr [,scope]) 
Returns the last value in the group. 

LCase(string) 
Returns the lower case of the passed string. 

Left(string) 
Returns the left n characters from the string. 

Len(string) 
Returns the lenght of the string. 

LTrim(string) 
Removes leading blanks from the passed string. 

Max(field expr [,scope]) 
Returns the maximum value in the group. 

Mid 
Returns the portion of the string (arg 1) denoted by the start (arg 2) and length (arg 3). 

Min(field expr [,scope]) 
Returns the minimum value in the group. 

Minute(datetime) 
Returns the integer minute given a date/time variable. 

Month(datetime) 
Returns the integer month given a date. 

MonthName(datetime) 
Get the month name given a date. If the optional second argument is 'True' then the abbreviated month name will be returned. 

Next(field expr [,scope]) 
Returns the value of the next row in the group. 

Oct(number) 
Returns the octal value of a specified number. 

Previous(field expr [,scope]) 
Returns the value of the previous row in the group. 

Replace 
Returns a string replacing 'count' instances of the searched for text (optionally case insensitive) starting at position start with the replace text. The function form is Replace(string,find,replacewith[,start[,count[,compare]]]). 

Right(string, number) 
Returns a string of the rightmost characters of a string. 

Rownumber() 
Returns the row number. 

RTrim(string) 
Removes trailing blanks from string. 

Runningvalue(field expr, string1 [,scope]) 
Returns the current running value of the specified aggregate function. string1 is an expression returning one of the following aggregate function: "sum", "avg", "count", "max", "min", "stdev", "stdevp", "var", "varp". 

Second(datetime) 
Returns the integer second given a date/time variable. 

Space(number) 
Returns a string containing the number of spaces requested. 

Stdev(field expr [,scope]) 
Returns the standard deviation of the group. 

Stdevp(field expr [,scope]) 
Returns the standard deviation of the group. Use stdevp instead of stdev when the group contains the entire population of values. 

StrComp(string1, string2, compare) 
Compares the strings; optionally with case insensitivity. When string1 < string1 =" string2:"> string2: 1 

String(number, char) 
Return string with the character repeated for the length. 

StrReverse(string) 
Returns a string with the characters reversed. 

Sum(field expr [,scope]) 
Returns the total of the group. 

Switch(bool-expr, result1 [, bool-expr-n, result-n]) 
The arguments are pairs of expression. When the bool-expr is true the result is returned. bool-expr-n is evaluated until one is results in true then the cooresponding result-n expression is returned. 

Today() 
Return the current date/time on the computer running the report. 

Trim(string) 
Removes whitespace from beginning and end of string. 

UCase(string) 
Returns the uppercase version of the string. 

Var(field expr [,scope]) 
Returns the variance of the group. 

Varp(field expr [,scope]) 
Returns the variance of the group. Use varp instead of var when the group contains the entire population of values. 

Year(datetime) 
Obtains the year from the passed date. 

Weekday() 
Returns the integer day of week: 1=Sunday, 2=Monday, ..., 7=Saturday given a date. 

WeekdayName(iday [,abbr]) 
Returns the name of the day of week given the integer Weekday. The optional second argument will return the abbreviated day of week if 'True'.

Custom Functions

Custom functions There are about 100 common functions in SSRS 2005, and they can handle most of what you will need to do. Occasionally, however, you will still need to set an expression with more control than the common functions can manage. So, to make SSRS even more flexible, you can write custom VB.NET or C# functions and use them in expressions. Open the report and navigate to the Layout tab. From the Report menu select Report Properties and then jump to the Code tab. We are going to write a custom function that returns a different color, depending on the value that is passed to it. We will then use that function to set the background color for the status field in the report detail table. Copy the following code into the code window:         Public Function GetColor(ByVal status as String) as String             IF status = "1" Then                 Return "White"             End IF             IF status = "2" Then                 Return "Yellow"             End IF             IF status = "3" Then                 Return "Tomato"             End IF         End Function     Click OK and close the window. Now that we have a function that returns color names we need to wire up that function to an expression.  Click on the Customer Status cell and open the Properties window. Find the Background Color property and choose Expression from the dropdown. Add the following line of code to create an expression: =code.GetColor(Fields!ChStatus.Value) When the report runs and this expression is resolved, SSRS will call your GetColor function and pass the value of the customer status for that instance of the row to the function. The function takes over and decides which color should be returned. SSRS then uses that value as the value for the background property for that cell. Please note that custom functions must be called using =code.<myfunction>. Now navigate to the Preview tab and run the report..