Below are all of the Functions available on the Select view grouped by the different function categories.
Aggregates
Aggregates perform calculations on multiple values and return a single value. For example, if you wanted to summarize salary amounts you would use the SUM aggregate. These functions require no extra input.
| Function | Description |
|---|---|
| Avg | Returns the average value of a numeric column. |
| Count | Returns the number of rows of a column. |
| Count Distinct | Returns the number of unique rows of a column. |
| Max | Returns the largest value of the selected column. |
| Min | Returns the lowest value of the selected column. |
| Sum | Returns the total of the selected column. |
Text Adjustments
Text Adjustments are used to adjust the column string. These functions require no extra input.
| Function | Description |
|---|---|
| Lower | Converts the text to lower-case. |
| Upper | Converts the text to upper-case. |
| LTrim | Removes leading spaces from a string. |
| Rtrim | Removes trailing spaces from a string. |
| Trim | Removes leading and trailing spaces from a string. |
| Len | Returns the length of the string. |
| Isnull | Replaces NULL values in a field with the value you specify. |
| Left | Returns the number of characters specified starting from the left side of the string. |
| Right | Returns the number of characters specified starting from the right side of the string. |
| Substring | Returns the number of characters specified starting from the starting position specified. |
| Replace | Replaces a specific string of text with another string of text. |
Blank Column
If your report will have report grouping then it is recommended to add a blank column as the first column. This is used for the header-footer group descriptions.
| Function | Description |
|---|---|
| Blank Top | This inserts a blank row as the first column. This is needed when using the report design layout. The blank column is used for the header/footer group descriptions. |
| Blank Bottom | This inserts a blank row as the last column. You can use the drag and drop feature to move this column around to create space between two other columns. |
Case Statements
The Case statement goes through conditions and returns a value when the first condition is met. This is the equivalent of If Then Else statements.
Formulas
If you want the column to generate as an Excel formula then you want to use the Formula function. When using this function you reference other columns using C# where # represents the column number.
Using the Next Fx Button
The Next Fx button is used to apply multiple functions against the same column. Each time you click Next Fx you are expected to select a new function and then finally click the Add button to add the column.
Post your comment on this topic.