Formula List Dialog
8.) Using Formulas & the Formula Builder
EspressReport supports a wide variety of formulas, giving you an important mechanism for manipulating and displaying report data. Using formulas users can add summaries and aggregation, perform basic calculations, and build complex expressions using one of over 70 built-in functions.
*Note - Some of the syntax for EspressReport formulas has changed greatly between v2.51 and v3.0. In most cases the deprecated syntax will continue to work correctly. It is recommended, however, that you check older templates to make sure the functions still return correctly if you have upgraded to v3.0 or higher from v2.51 or lower.
When you elect to insert a new formula into the report (by selecting 'Insert Formula' from the Insert menu, or clicking on the 'Insert Formula button on the toolbar) a list of all the defined formulas in the report will appear.
Formula List Dialog
To insert a formula into the report, select a formula from the list, and click the 'Insert' button. The formula list dialog will then close, and you will be allowed to click to place the formula.
The report section in which you place the formula is extremely important, since the formula will reset each time that section repeats in the report. For example, if you place a formula in the Group Footer of a summary break report, the formula will recalculate for each group in the report. If the formula references a column value, then it will use only the data within each break group. If you were to place the same formula in the Report Footer section, then it would only calculate once, and it would use all of the data in the column field regardless of breaks and grouping.
Formulas and labels that are placed in the Table Data section of the report become computed columns, and can be treated as column fields as well as formulas.
*Note: Labels are automatically treated as Strings, and can be edited by double-clicking on them. (ie, to use a number as an an integer in a formula, open the formula builder and remove the double-quotes around the number)
Formulas are constructed using the formula builder interface. To launch the formula builder, you can select to create a new formula by selecting 'New' from the formula list dialog. This will prompt you to specify a name for the new formula. Once you have specified a name the formula builder will open allowing you to construct a formula. You can also use the formula builder to edit an existing formula. To edit a formula, you can select a formula from the list and click the 'Edit' button, or double-click on a formula cell in the design window.
Formula Builder Window
The main window of the formula builder contains the text of the formula. The folders on the right-hand side contain various elements that can be added to the formula including column fields, other formulas, parameter values, database fields, and built-in functions. The first two rows of buttons contain the most common arithmetic and Boolean operators. (Operators and functions are discussed in the next section). The last two rows of buttons serve as command buttons and perform the following functions:
Insert Text: This inserts the currently selected object from the right-hand side. You can also insert an object into the formula by double-clicking it.
Browse Data Field: This is for database fields. This will query the database and return the first 20 values for the column.
Initialize Parameter: This will bring up the parameter initialization dialog for any parameters defined in the formula. Parameters are discussed in section 8.2.6.
Test: This will check the current formula and see if it is correct.
Ok: This will close the formula builder and return to the formula list dialog, allowing you to insert the formula into the report.
Save As: This allows you to save the current formula under a different name. Because you can re-use a formula in different places in the report, using the save as option allows you to modify a formula in one place, without changing every place else the formula is referenced.
Cancel: This will close the formula builder without saving the formula/changes.
The following section details the various operators and functions available in EspressReport, and how to use them.
8.2.1.) Using Column Field Data
Often you will want to use data from column fields for your formulas. This can easily be accomplished in formulas using the following syntax: {Field Name}. The braces delimit the field, and the name is the name of the column field you're referencing. So in a basic example: {UnitPrice} * {Quantity} would multiply two column fields together.
In most cases the column name is the name specified in the data source (i.e. the database column name or alias). If you're uncertain about the correct name, you can simply select the column field you would like to use from the "Columns" node in the right-hand panel in the formula builder.
There are two other functions that you can use to retrieve column data:
id() - This will allow you to retrieve the value of any cell in the report including column fields. The syntax is id(Object ID). You can retrieve an object's ID by right-clicking on the cell and selecting 'Properties' from the pop-up menu. You can also retrieve the ID for column fields by selecting 'View Column Mapping' from the Data menu.
You can also assign a custom ID to elements in the report, instead of using the default ID. To assign a custom ID to an element, right-click on it in the Report Designer, and select Custom ID from the pop-up menu. This will bring up a dialog allowing you to enter an ID for that element. The argument for the id() function can either be the original ID for the element, or the defined custom ID.
Element IDs can also be used to get a handle to an element in the Report API. You can pass in either the original ID or the custom ID into the getData() method to get a handle to an element.
The id() function also serves another important purpose. It can be used to delay calculation of computed columns. Normally formulas in the Table Data section of the report will compute prior to any aggregations in the group or table footers. Sometimes, however, you may want to calculate the aggregations prior to the column fields. For example, say you wanted to create a column that calculates each row's percentage of a total. In order to get the correct result the total would have to be calculated before the column. To do this, simply reference the aggregation in the Table/Group Footer using the id() function. The finished function in the Table Data section would look like this {Quantity} / id(TBL0_FTR_FORM0).
col() - This is a deprecated function that is used in earlier version of EspressReport to retrieve data from column fields. Although this function is still valid, it is recommended that you use the new field notation. The syntax for this function is col(Column Index). To view the column index select 'View Column Mapping' from the Data menu. This will display the assigned index number for each column field. Index values are assigned based on the order in which the columns are selected for the report. The first column has an index of 0.
You can aggregate any column field (including computed columns) using one of the aggregation functions provided with EspressReport. The following aggregation options are available:
average() - This returns the mean value of the specified column. The syntax is average(Column Field). To return the average value of a column field named "UnitPrice", you would use the following formula: average({UnitPrice}).
median() - This returns the median value for the specified column. The syntax is median(Column Field). If the median of the column is two values (for an even number of rows), the aggregation will return the average of the two values.
count() - This returns a count of the values in the specified column. The syntax is count(Column Field).
countdistinct() - This returns a count of all the distinct values in the specified column The syntax is countdistinct(Column Field).
max() - This returns the maximum value for the specified column. The syntax is max(Column Field).
min() - This returns the minimum value for the specified column. The syntax is min(Column Field).
stddev() - This returns the standard deviation for the specified column. The syntax is stddev(Column Field).
sum() - This returns the sum of the specified column. The syntax is sum(Column Field).
sumsquare() - This returns the sum of squares for the specified column. The syntax is sumsquare(Column Field).
variance() - This returns the variance for the specified column. The syntax is variance(Column Field).
It is important to note that aggregation functions can only take column fields as an argument. You cannot use an expression or another function as an argument, hence sum({UnitPrice}*{Quantity}) is not valid. To make this calculation you would first need to add {UnitPrice}*{Quantity} as a formula in the Table Data section of the report. Then you can sum the computed column created by the first formula using sum(@formulaname).
EspressReport allows you to easily re-use report formulas, by plugging them directly into new formulas. You can reference a formula using the following syntax: @FormulaName. The formula name, is the name that you assigned when you first created the formula. So for example if you had created a formula {UnitPrice}*{Quantity} called "Total". You could retrieve the result of this formula by typing @Total.
If you're uncertain about the correct name, you can simply select the formula you would like to use from the "Formulas" node in the right-hand panel in the formula builder.
You can also use this notation to aggregate computed columns if the formula has been placed in the Data Table section of the report.
8.2.3.) Using Parameter Values
In addition to column field, and formula values, you can also access parameter values as part of a formula. Parameter values are the user supplied values to either query or formula parameters. For more about query parameters, see section 3.2.2.2. For more about formula parameters, see section 8.2.6.
To reference a parameter value use the following syntax :ParameterName for query parameters, and ?ParameterName for formula parameters. So for example, if the report has a query parameter named "StartDate" that prompts the user to supply a date, :StartDate will return the date that the user has supplied when the report is run.
If you're uncertain about the correct name, you can simply select the parameter you would like to use from the "Parameters" node in the right-hand panel in the formula builder. Also, note that if you type a formula parameter name wrong, the formula builder will assume you're trying to define a new parameter.
If your report uses a database as the data source, you can also use database fields that were not selected for the report in a formula. Database fields are referenced in a similar manner to column fields, and use the syntax {Table Name.Field Name}. If your database requires three part names, then the fields should be referenced accordingly.
You can select database fields to add from the "Database Fields" node in the right-hand panel in the formula builder. In addition, the formula builder allows you to preview database fields. To do this, first select a field in the right-hand panel, and then click the 'Browse Data Field' button. This will bring up a new dialog showing the first few rows from the selected column.
When using constants in formulas there are certain formats that are required for each data type.
Numeric Data: To represent a numeric constant, you simply have to type the number. Both integers and decimals can be accepted, however you cannot type thousands separators. So to specify the number 12.28 as a constant in a formula, type 12.28.
String Data: To represent a string constant, type the string delimited with double quotes. To specify a string Hello, you would type "Hello".
Boolean Data: To represent a Boolean constant, type either true or false without any delimiters. To specify a false Boolean value, you would type false. Note that these are not case sensitive.
Date Data: To represent a date constant, type the date in the following format without delimiters MM/dd/yyyy. To specify the date August 5th, 2002, you would type 08/05/2002.
Time Data: To represent a time constant, type the time in the following format without delimiters hh:mm:ss. To specify the time 2:30 PM, you would type 14:30:00. Note that time must be in 24-hour representation.
Timestamp Data: To represent a timestamp constant, type the date and time in the following format without delimiters MM/dd/yyyy hh:mm:ss. To specify the time stamp August 5th, 2002 at 2:30 PM, you would type 08/05/2002 14:30:00.
Rather than adding constants into a formula, you can specify a parameter. Using a parameter you can collect constant values from the user (or elsewhere) at run-time and dynamically compute the formula based on those values. To specify a parameter in the formula use a question mark "?" followed by the parameter name.
For example the following formula {Quantity}+?Value would add a user supplied value to the "Quantity" field in a report.
*Note - if you want to define a new parameter within a formula, then you must take care to use a unique name. If you specify a parameter name of a parameter that is defined in another formula within the report, then the new formula will return the value from the pre-defined parameter.
8.2.6.1.) Initializing Formula Parameters
Like query parameters (detailed in section 3.2.2.2.2), you must initialize a formula parameter before a formula can be used in a report. To initialize any parameters defined in the current formula, click the 'Initialize Parameter' button in the formula builder. This will bring up a dialog prompting you to specify options for the parameter.
Initialize Formula Parameter Dialog
From this dialog, you can specify a default value for the parameter, map the parameter to a report column (this will give the user a drop-down list of distinct values, rather than having them type in the parameter), specify the data type for the supplied parameter, as well as specify the prompt for the end user. Clicking on the 'Previous Parameter' and 'Next Parameter' buttons allow you to initialize each of the parameters that have been defined in the query. Click 'OK' when you have finished setting the options for all of the parameters.
*Note - for formula parameters you must specify a default value for each parameter. Also, if the parameter is mapped to a report column, the first time the report is previewed, the dialog will only contain the first twenty values of that column. This is because the full data set is not retrieved when the report is first created. At run-time the dialog will contain all the available values in the report column.
EspressReport provides several arithmetic and Boolean operators that allow you to create expressions in formulas. Operators use in-fix notation, that places the operator between the arguments in the expression i.e. argument operator argument2.
You can automatically insert operators into a formula, by clicking one of the operator buttons in the formula builder.
8.2.7.1.) Arithmetic Operators
Four basic arithmetic operators are supported "+", "-", "*", and, "/". These will add, subtract, multiply, and divide two objects respectively. For example 1 + 2 will return 3.
Generally arithmetic operators will take numbers as arguments and will return numbers with two exceptions. "+" can be used to concatenate strings for example "Hello" + "World" would return "Hello World". Also, "-" can be used to subtract two date objects and return the difference in days for example 8/2/2002 - 4/7/2002 would return 117.
Eight Boolean operators are supported "AND", "OR", "==", "<", "<=", ">", ">", and "<>". "AND" and "OR" take Boolean arguments and return Boolean values. For example true AND true would return true or 1 < 2 OR 4 < 3 would return true.
"==", "<", "<=", ">", ">=", and "<>" are comparison operators that signify equal to, less than, less than equal to, greater than, greater than equal to, and not equal respectively. Comparison operators compare two objects of the same data type and will return a Boolean value for example 3 < 4 would return true and "Yes" == "No" would return false.
Boolean operators are more commonly used in cell scripting rather than formulas. Cell scripting is covered in Chapter 9.
In addition to the basic operators, EspressReport provides a number of built-in functions. Function syntax generally takes the form of a function name outside of a set of parenthesis enclosing the comma-separated arguments function(argument, argument).
You can insert functions into the formula, by selecting the function you would like under the "Numeric Functions", "String Functions", or "Date Functions" nodes in the formula builder. Functions are inserted with hints indicating the type and number of arguments it should take. In order for the function to return properly, these hints have to be removed and replaced with valid arguments.
The following functions take numeric arguments.
abs() - This will return the absolute value of a number. The syntax is abs(Number). For example abs(-12) would return 12.
acos() - This will return the arc cosine of an angle, in the range of 0 through pi. Syntax for this is acos(radians). For example, acos(-sqrt(2)/2) would return 3*pi/4.
asin() - This will return the arc sine of an angle in the range of -pi/2 through pi/2. The syntax is asin(radians). For example, asin(sqrt(2)/2) would return pi/4.
atan() - This will return the arc tangent of an angle in the range of -pi/2 through pi/2. The syntax is atan(radians). For example, atan(1) would return pi/4.
atan2() - This will convert rectangular coordinates (b, a) into polar coordinates (r, theta). This function returns theta by taking the arc tangent of b/a in the range of -pi to pi. The syntax is atan2(y_coordinate, x_coordinate). For example atan2(1,0) would return pi/2.
ceil() - This will return the lowest integer value that is greater than the specified number. The syntax is ceil(Number). For example, ceil(15.3) would return 16.
cos() - This will return the cosine of an angle in radians. The syntax is cos(radians). For example cos(3*pi()/4) would return -sqrt(2)/2.
e() - This will return the value e, the natural logarithm base. The syntax is e() without any arguments.
exp() - This will return e raised to the nth power. The syntax is exp(Number). For example exp(3) would return e3 or 20.086.
factorial() - This will return the factorial of the specified object. The syntax is factorial(Number). For example factorial(3) would return 6.
floor() - This will return the highest integer value that is less than the specified argument. The syntax is floor(Number). For example floor(5.52) would return 5.
getColumnCount() - This will return an integer indicating the total number of columns in the report. The syntax is getColumnCount() without any arguments. This function returns the total number of columns whether they are visible or not.
getRowIndex() - This will return an integer indicating the index value of the current row (of Table Data) of the report. The syntax is getRowIndex() without any arguments.
getTotalRowIndex() - This will return a count of all the rows (in Table Data) in the report. The syntax is getTotalRowIndex() without any arguments.
getRowIndexOfCurrentTable() - This will return the current row in the current group (table) in a report. This function is generally only relevant in reports that have grouped data like the summary break and master & details layout. For example, if the current row is the third row in a group of data getRowIndexOfCurrentTable() will always return 2 regardless of what the row index is for the total report (which you could retrieve using the getRowIndex() function). The syntax is getRowIndexOfCurrentTable() without any arguments.
getTotalRowIndexOfCurrentTable() - This will return a count of all the rows in the current group (table) in a report. This function is generally only relevant in reports that have grouped data like the summary break and master & details layout. The syntax is getTotalRowIndexOfCurrentTable() without any arguments.
getSiblingCount() - This will return a count of the number of sibling groups within a nesting level. This function is generally only relevant in reports that have grouped data like the summary break and master & details layout. For example, say you have a summary break report with one level of grouping. There are three distinct values in your row break column creating three groups at the level. Within the group (i.e. Group Header, Table Data, or Group Footer sections) getSiblingCount() would return 3. The syntax is getSiblingCount() without any arguments.
getChildCount() - This will return a count of the number of child groups inside a nesting level. This function is generally only relevant in reports that have grouped data like the summary break and master & details layout. For example, say you have the same example as described for the previous function. Within the group the getChildCount() function would return 0 as there is only one level of grouping. However, outside the group (i.e. Table Header or Table Footer sections) getChildCount() would return 3. The syntax is getChildCount() without any arguments.
getGroupIndex() - This will return an integer indication the index value of the current group. This function is only relevant in reports that have grouped data like the summary break and master & details layout. This function will only return the index of the inner most group for reports with nested groups.
log() - This will return the natural logarithm of the specified number. The syntax is log(Number). For example log(10) would return 2.303.
mod() - This will return the remainder after dividing two numbers. The first argument is the numerator, and the second is the denominator. The syntax is mod(Number, Number). For example mod(12, 7) would return 5.
pi() - This will return the value pi. The syntax is pi() without any arguments.
pow() - This will return the value of the first argument raised to the power of a second argument. The syntax is pow(Number, Number). For example pow(2, 3) would return 8.
random() - This will return a random value greater than or equal to 0 and less than 1. The syntax is random() without any arguments.
rint() - This will round the specified argument to the nearest integer. The syntax is rint(Number). For example rint(5.6) would return 6.
sin() - This will return the sine of an angle in radians. The syntax is sin(radians). For example sin(pi()/2) would return 1.
sqrt() - This will return the square root of the specified number. The syntax is sqrt(Number). For example sqrt(64) would return 8.
tan() - This will return the tangent of an angle in radians. The syntax is tan(radians). For example tan(pi()/4) would return 1.
toDegrees() - This will convert an angle measured in radians to degrees. The syntax is toDegrees(Number). For example, toDegrees(pi()) would return 180.
toRadians() - This will convert an angle measured in degrees to radians. The syntax is toRadians(Number). For example, toRadians(180) would return 3.142.
toString() - This will convert a number into a string. The syntax is toString(Number, Number of Decimals, Round Up(True/False)). For example toString(12.216, 2, True) would return "12.22" as a string.
The following functions take string arguments.
getHeader() - This will return the column name for a column field. The syntax is getHeader(Column Field). For example if you have a column named "UnitPrice" getHeader({UnitPrice}) would return "UnitPrice" as a string.
getPage() - This will return the current page number. The syntax is getPage() without any arguments.
getTotalPages() - This will return the total number of pages for the current report. The syntax is getTotalPages() without any arguments.
getTotalSections() - This will return the total number of sections (i.e., the number of pages needed horizontally) for the current report. The syntax is getTotalSections() without any objects.
indexOf() - This will return the first index value where a specified pattern within a string occurs. There are two syntaxes for this function.
1.) indexOf("String", "Pattern"). For example indexOf("Banana", "an") would return 1.
2.) indexOf("String", "Pattern", Starting Index). For example indexOf("Banana", "an", 2) would return 3.
insert() - This allows you to insert new characters into a string. The syntax is insert("String", Character Number, "New Characters"). For example insert("Wood Natural Furniture", 4, "View") would return "WoodView Natural Furniture".
lastIndexOf() - This will return the last index value where a specified pattern within a string occurs. There are two syntaxes for this function.
1.) lastIndexOf("String", "Pattern"). For example lastIndexOf("Banana", "an") would return 3.
2.) lastIndexOf("String", "Pattern", Starting Index). For example lastIndexOf("Banana", "an", 2) would return 1.
replace() - This allows you to replace one set of characters in a string with another. There are two syntaxes for this function.
1.) replace("String", Character Start Number, Character End Number, "New Characters"). For example replace("Today is a rainy day", 11, 16, "sunny") would return "Today is a sunny day".
2.) replace("String", "Old Characters", "New Characters"). For example replace("Today is a rainy day", "rainy", "sunny") would return "Today is a sunny day".
setMaxLength() - This allows you to set the maximum length of a string. The syntax is setMaxLength("String", Maximum Number of Characters). For example setMaxLength("You're a firefighter",13) would return "You're a fire".
strcmp() - This compares two strings and returns the lexicographical difference between them. If the first argument is larger than the second then the result is positive, if the first argument is larger than the second then the result is negative. If the strings are the same, then the result is zero. The syntax is strcmp("String", "String"). For example strcmp("a", "c") would return -2.
strcmpIgnoreCase() - This compares two strings and returns the lexicographical difference between them while ignoring case. The syntax is strcmpIgnoreCase("String", "String"). For example strcmpIgnoreCase("a", "A") would return 0.
strcat() - This concatenates multiple strings together. The syntax is strcat("String", "String", "String"...). For example strcat("Wood", "View") would return "WoodView". You can also use the "+" operator to concatenate strings.
strlen() - This will return an integer indicating the number of characters in a specified string. The syntax is strlen("String"). For example strlen("EspressReport") would return 13.
substring() - This will return a portion of a string as specified by an argument. There are two syntaxes for this function.
1.) substring("String", Character Start Number). For example substring("unhappy", 2) would return "happy".
2.) substring("String", Character Start Number, Character End Number). For example substring("smiles", 1, 5) would return "mile".
trim() - This will remove any leading or trailing spaces from the specified string. The syntax
is trim("String"). For example
trim(" Hello ") would return "Hello".
toLowerCase() - This will render any uppercase letters within the specified string to lowercase. The syntax is toLowerCase("String"). For example toLowerCase("ABCdef") would return "abcdef".
toNumeric() - This will turn a string into a double. The syntax is toNumeric("String"). For example toNumeric("425.52") would return 425.52. For this formula to work correctly the string argument must contain numeric characters.
toUpperCase() - This will render any lowercase letters within the specified string to uppercase. The syntax is toUpperCase("String"). For example toUpperCase("ABCdef") would return "ABCDEF".
The following functions use date/time arguments. Some of the date/time functions use a special argument that indicates a calendar field. To specify a calendar field argument, type one of the following without any delimiters.
| ERA | DAY_OF_MONTH | HOUR |
| YEAR | DAY_OF_YEAR | HOUR_OF_DAY |
| MONTH | DAY_OF_WEEK | MINUTE |
| WEEK_OF_YEAR | DAY_OF_WEEK_IN_MONTH | SECOND |
| WEEK_OF_MONTH | AM_PM | MILLISECOND |
addTime() - This adds a specified amount of time to a given date/time. The syntax is addTime(Date/Time, Calendar Field, Number). The number specifies the amount that the calendar field should be added to the date/time object. For example addTime(12/5/1998, MONTH, 5) would return May 5, 1999, and addTime(12/5/1998, DAY_OF_MONTH, -25) would return Nov, 10 1998.
getAmPm() - This will return AM or PM as a string for a given time. The syntax is getAmPm(Time). For example getAmPm(13:24:00) would return "PM".
getCurrentDate() - This will return the current date from the system. The syntax is getCurrentDate() without any arguments.
getCurrentDateTime() - This will return the current date and time from the system. The syntax is getCurrentDateTime() without any arguments.
getCurrentTime() - This will return the current time from the system. The syntax is getCurrentTime() without any arguments.
getDateTime() - This will return the value of a specified calendar field for a given date/time. The syntax is getDateTime(Date/Time, Calendar Field). For example getDateTime(12:24:00, MINUTE) would return 24, and getDateTime(10/10/2001, DAY_OF_WEEK) would return 4 (meaning Wednesday).
getDayDifference() - This will return the difference in days as an integer between two dates. The syntax is getDayDifference(Date, Date). For example getDayDifference(5/1/2001, 3/1/2001) would return 61. You can also use the "-" operator to return the difference between to dates.
getDayOfWeek() - This will return the day of the week for the specified date. The syntax is getDayOfWeek(Date). For example getDayOfWeek(10/10/2001) will return "Wednesday" as a string.
getEra() - This will return the era for the specified date. The syntax is getEra(Date). For example getEra(10/10/2000) would return "AD" as a string.
getMonth() - This will return the month for the specified date. The syntax is getMonth(Date). For example getMonth(10/10/2000) would return "October" as a string.
rollTime() - This is a time rolling function. The syntax is rollTime(Date/Time, Calendar Field, Number). Unlike the addTime() function, this function will only adjust the specified calendar field by the specified amount, and will have no effect on the other fields. For example rollTime(12/5/1998, MONTH, 5) would return May 5, 1998, and rollTime(12/5/1998, DAY_OF_MONTH, -25) would return Dec 11, 1998. For the latter example, when the count reaches the beginning of the month, it starts over at the end without changing the month field.
printDate(), printDateTime(), printTime() - These three functions allow you to set the way in which date/time information is displayed beyond what is capable through the data formatting options. These functions will return date and/or time information as a string. Their syntax is as follows:
printDate(Date, "Date Format")
printDateTime(Date, "Date & Time Format")
printTime(Time, "Time Format")
The date and/or time format is entered as a series of characters and delimiters. Letters are used to represent different elements of date/time data. The characters and what each represent are listed below:
| Character | Represents | Output (text/number) | Example |
| G | era | text | AD |
| y | year | number | 1996, 96 |
| M | month in year | text or number (depends on length) | July, Jul, 07 |
| d | day in month | number | 10 |
| h | hour am/pm (1-12) | number | 1 |
| H | hour 24 hr. (0-23) | number | 18 |
| m | minute in hour | number | 30 |
| s | second in minute | number | 55 |
| S | millisecond | number | 978 |
| E | day in week | text | Tuesday, Tue |
| D | day in year | number | 189 |
| F | day of week in month | number | 2 (as in 2nd Wed. in July) |
| w | week in year | number | 27 |
| W | week in month | number | 2 |
| a | am/pm marker | text | AM, PM |
| k | hour 24 hr (1-24) | number | 24 |
| K | hour am/pm (0-11) | number | 0 |
| z | time zone | text | Pacific Standard Time, PST |
You can piece together almost any combination of these characters to produce a date expression in the format that you would like. The count of groups of characters determines the form that the element will take. For text elements 4 or more characters in a group will cause the full form of the element to be used. If less than 4 characters are used the short form will be used if one exists. For example EEEE would return "Monday" and EE would return "Mon". For month M which can display as either text or a number, 4 or more in a group will display the full version, 3 will display the abbreviation, and 2 or less will display the number form.
For numeric elements, the count of characters is the minimum number of digits that the element will take. Shorter numbers will implement leading zeros. For example if the day of the date is 2, dd would return "02" and d would return "2".
Any character that is not a-z or A-Z like ";", ":", "@", etc. can be inserted anywhere within the string expression, and will display as they are entered. You can also insert words and expressions by enclosing them within single quotes (type two single quotes to insert an apostrophe as text). Several examples are listed below:
printDateTime(10/10/2001 21:15:12, "MMMM dd, yyyy 'at' hh:mm a z")
would return "October 10, 2001 at 9:15 PM PDT"
printDate(10/10/2001, "EEE MMM dd'th', yyyy")
would return "Wed Oct 10th, 2001"
printTime(13:22:12, "h 'o''clock' m 'minutes and' s 'seconds'")
would return "1 o'clock 22 minutes and 12 seconds"
printDateTime(10/10/2001 13:22:12, "MM/dd/yy HH:mm:ss")
would return "10/10/01 13:22:12"
If you do not enter any information for the date/time format, the date will display as yyyy-mm-dd and the time will default to HH:mm:ss.S. So printDateTime(10/10/2001 13:22:12) would return "2001-10-10 13:22:12.0".
toDate() - This will convert a long integer into a timestamp. The syntax is toDate(Number). For example toDate(1025039526306) would return "Jun 25, 2002 2:12:06 PM".
In addition to the functions provided with EspressReport, it is also possible to include your own custom functions in the formula builder when launching the Report Designer via the API. Any Java function can be supported. For more about this feature, please see section 3.5.8.6 of the Programming Guide.
8.3.) Subreport Formula Access
A main report can access its sub-reports’ columns and formulas and use them as its own by using the prefix SUB# where '#' is the number of the subreport, beginning with 1. For example, the syntax to access the column "UnitPrice" from sub-report 1 is: SUB1.{UnitPrice} or SUB1.COL(i) where ”i” is the column number of "UnitPrice". Syntax to access the formula "Sales" from sub-report 1 is : SUB1.@Sales.
Note: Accessing a subreport column (i.e. SUB1.{UnitPrice}) returns a single value--the value from the first row of the column. You can get the value at a particular row by appending .row(index) at the end where 'index' is the row number beginning with 0, but it will always only return a single value.
In the formula builder’s help panel, under both Columns and Formulas, there is a folder for each subreport:
Sub-report columns and formulas can be used to build more complex expressions or scripts (i.e. SUB1.@Total + SUB2.@Total).
Although sub-report columns and formulas can be used in complex expressions, sub-report columns and formulas cannot be used in aggregation funtions(SUM, COUNT, MAX, AVERAGE, etc.) For example, SUM({UnitPrice}) is a valid formula, but SUM(SUB1.{UnitPrice}) is not. To accomplish this, instead make SUB1.{UnitPrice} a column in the main report and then apply aggregation to the column.
Although the main report can access any of the sub-reports' columns or folders, sub-reports cannot access the main report's or any other sub-reports’ columns or formulas.
If a sub-report formula or column that is used by the main report is deleted, the formula in the main report will display a NULL value when running the report and script will not be applied.
For example, if product information is spread across several databases, but you would like to sum up all the units in stock across the three databases, you could create one main report and add several sub-reports, and create a formula to insert to the report footer adding up all the units in stock. To demonstrate this feature, consider a report created from the Inventory XML file with the fields Category Name, Product ID, Product Name, Unit Price, and Units In Stock. Proceed to add subreports to this, one from the Woodview Access database, and one from the Woodview HSQL database, containing the same fields.
Within each subreport, as well as within the main report itself create a formula called UnitsInStock, SUM({UnitsInStock}). Then, within the main report you can create the ultimate formula using these three:
Creating a Formula Using Sub-report Formulas
This formula will take the formula values from each of the three reports, and add them together to calculate the final value.
This feature is also applicable to linked sub-reports, although the application of it is different. For example, if you create a summary break report containing customer addresses with a sub-report in the group footer detailing all purchaes made by that particular customer, you can sum up all the sales info within the main report.
First, create a formula within the sub-report entitled "Sum_Sales" which sums up all the sales in the sub-report. Return to the main report, and create a formula "sub_total" which is simply SUB1.@Sum_Sales. Insert this into the Table Data Section, it can be invisible if you would like, but it must be in the table data section. Then, create a second formula in the main report, "total", SUM(@sub_total). This formula is added to the report footer, and will sum up all the sales columns from all of the sub-reports.