In this topic, we talk about the functions supported by VBScript which are described in detail in the following section.
To increase compatibility with code written in Visual Basic and Microsoft Access (VBA), C1FlexReport exposes two functions that are not available in VBScript: Iif and Format.
Iif evaluates a Boolean expression and returns one of two values depending on the result. For example:
Iif(SalesAmount > 1000, "Yes", "No")
Format converts a value into a string formatted according to instructions contained in a format expression. The value may be a number, Boolean, date, or string. The format is a string built using syntax similar to the format string used in Visual Basic or VBA.
The following table describes the syntax used for the format string:
| Value Type | Format String | Description |
|---|---|---|
| Number | Percent, % | Formats a number as a percentage, with zero or two decimal places. For example: Format(0.33, "Percent") = "33%" |
| #,###.##0 | Formats a number using a mask. The following symbols are recognized: # digit placeholder 0 digit placeholder, force display , use thousand separators ( enclose negative values in parenthesis % format as percentage For example: Format(1234.1234, "#,###.##") = "1,234.12" |
|
| Currency | Currency, $ | Formats a number as a currency value. Displays number with thousand separator, if appropriate; displays two digits to the right of the decimal separator. For example: Format(1234, "$") = "$1,234.00" |
| Boolean | Yes/No | Returns "Yes" or "No". |
| Date | Long Date | Format(#12/5/1#, "long date") = "December 5, 2001" |
| Short Date | Format(#12/5/1#, "short date") = "12/5/2001" |
|
| Medium Date | Format(#12/5/1#, "medium date") = "05-Dec-01" |
|
| q,m,d,w,yyyy | Returns a date part (quarter, month, day of the month, week of the year, year). For example: Format(#12/5/1#, "q") = "4" |
|
| String | @@-@@/@@ | Formats a string using a mask. The "@" character is a placeholder for a single character (or for the whole value string if there is a single "@"). Other characters are intercodeted as literals. For example: Format("AC55512", "@@-@@/@@") = "AC-555/12" |
| @;Missing | Uses the format string on the left of the semi-colon if the value is not null or an empty string, otherwise returns the part on the right of the semi-colon. For example: Format("@;Missing", "UK") = "UK" |
Note that VBScript has its own built-in formatting functions (FormatNumber, FormatCurrency, FormatPercent, FormatDateTime, and so on). You may use them instead of the VBA-style Format function described here.
Aggregate functions are used to summarize data over the group being rendered. When used in a report header field, these expressions return aggregates over the entire dataset. When used in group headers or footers, they return the aggregate for the group.
All FlexReport aggregate functions take two arguments:
FlexReport defines the following aggregate functions:
| Function | Description |
|---|---|
| Avg | Average value of the expression within the current group. For example, the following expression calculates the average sales for the whole group and the average sales for a certain type of product: Avg(SalesAmount) |
| Sum | Sum of all values in the group. |
| Count | Count of records in the group with non-null values. Use an asterisk for the expression to include all records. For example, the following expressions count the number of employees with valid (non-null) addresses and the total number of employees: Count(Employees.Address) |
| CountDistinct | Count of records in the group with distinct non-null values. |
| Min, Max | Minimum and maximum values for the expression. For example: "Min Sale = " & Max(SaleAmount) |
| Range | Range between minimum and maximum values for the expression. |
| StDev, Var | Standard deviation and variance of the expression in the current group. The values are calculated using the sample (n-1) formulas, as in SQL and Microsoft Excel. |
| StDevP, VarP | Standard deviation and variance of the expression in the current group. These values are calculated using the population (n) formulas, as in SQL and Microsoft Excel. |
| Median | Returns median from the values in the group. |
| Mode | Returns mode from the values in the group. |
To use the aggregate functions, add a calculated field to a Header or Footer section, and assign the expression to the field's Text property.
For example, the "Employee Sales by Country" report in the sample NWind.xml file contains several aggregate fields. The report groups records by Country and by Employee.
The SalespersonTotal field in the Footer section of the Employee group contains the following expression:
=Sum([SaleAmount])
Because the field is in the Employee group footer, the expression returns the total sales for the current employee.
The CountryTotal and GrandTotal fields contain exactly the same expression. However, because these fields are in the Country group footer and report footer, the expression returns the total sales for the current country and for the entire recordset.
You may need to refer to a higher-level aggregate from within a group. For example, in the "Employee Sales by Country" report, there is a field that shows sales in the current country as a percentage of the grand total. Since all aggregates calculated within a country group refer to the current country, the report cannot calculate this directly. Instead, the PercentOfGrandTotal field uses the following expression:
=[CountryTotal]/[GrandTotal]
CountryTotal and GrandTotal are fields located in the Country and Report Footer sections. Therefore, CountryTotal holds the total for the current country and GrandTotal holds the total for the whole recordset.
It is important to realize that evaluating aggregate functions is time-consuming, since it requires the control to traverse the recordset. Because of this, you should try to use aggregate functions in a few calculated fields only. Other fields can then read the aggregate value directly from these fields, rather than evaluating the aggregate expression again.
For example, the "Employee Sales by Country" report in the NorthWind database has a detail field, PercentOfCountryTotal, that shows each sale as a percentage of the country's total sales. This field contains the following expression:
=[SaleAmount]/[CountryTotal]
SaleAmount is a reference to a recordset field, which varies for each detail record. CountryTotal is a reference to a report field that contains an aggregate function. When the control evaluates this expression, it gets the aggregate value directly from the report field, and does not recalculate the aggregate.
For the complete report, see report "Employee Sales by Country" in the Nwind.xml report definition file, which is available in the Documents\ComponentOne Samples\WinForms\vx.0\FlexReport\CS\FlexReportExplorer\Data folder.