3.6. Using Formulas & the Formula Builder

ReportDesigner 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]Note

Some of the syntax for ReportDesigner formulas has changed greatly between v2.51 and v3.0. In most cases the deprecated syntax will continue to work correctly. However, it is recommended 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.

3.6.1. Creating a Formula

When you select to insert a new formula into the report (by selecting Insert Formula from the Insert menu or by 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]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)

3.6.1.1. The Formula Builder

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 3.6.2.6 - Formula Parameters.

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.

3.6.2. Formula Syntax

The following section details the various operators and functions available in ReportDesigner and how to use them.

3.6.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 field 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 are 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 the 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. However, sometimes 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 ReportDesigner 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.

3.6.2.1.1. Column Aggregation

You can aggregate any column field (including computed columns) using one of the aggregation functions provided with ReportDesigner. 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(<ColumnField>).

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. Therefore, 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).

3.6.2.2. Using Formulas

ReportDesigner 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 the example, if you had created a formula {UnitPrice}*{Quantity} called Total, you could retrieve the result of this formula by typing @Total.

If you are 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.

3.6.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 2.4.3.2.2 - Parameterized Queries. For more about formula parameters, see Section 3.6.2.6 - Formula Parameters.

To reference a parameter value use the following syntax :<ParameterName> for query parameters, and ?<ParameterName> for formula parameters. 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 are 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, if you type a formula parameter name wrong, the Formula Builder will assume that you are trying to define a new parameter.

3.6.2.4. Using Database Fields

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.

3.6.2.5. Constants

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. For example, 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 as 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.

3.6.2.6. Formula Parameters

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]Note

If you want to define a new parameter within a formula, 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.

3.6.2.6.1. Initializing Formula Parameters

Like query parameters (detailed in Section 2.4.3.2.2.2 - Initializing Query Parameters), 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]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.

3.6.2.7. Operators

ReportDesigner 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. <argument1> <operator> <argument2>.

You can automatically insert operators into a formula by clicking one of the operator buttons in the formula builder.

3.6.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.

3.6.2.7.2. Boolean Operators

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 Section 3.7 - Scripting.

3.6.2.8. Functions

In addition to the basic operators, ReportDesigner 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.

3.6.2.8.1. Numeric Functions

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 e^3 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.

3.6.2.8.2. String Functions

The following functions take string arguments:

getAllRowData() -

Returns whole row of data in a format like (COLUMN1_NAME) VALUE1 (COLUMN2_NAME) VALUE2 ... Does not require any 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 stringoccurs. 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, the result is positive. If the first argument is smaller than the second, 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("ReportDesigner") would return 14.

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.

3.6.2.8.3. Date/Time Functions

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.

ERADAY_OF_MONTHHOUR
YEARDAY_OF_YEARHOUR_OF_DAY
MONTHDAY_OF_WEEKMINUTE
WEEK_OF_YEARDAY_OF_WEEK_IN_MONTHSECOND
WEEK_OF_MONTHAM_PMMILLISECOND
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 &amp; 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:

CharacterRepresentsOutput (text/number)Example
GeratextAD
yyearnumber1996, 96
Mmonth in yeartext or number (dependson length)July, Jul, 07
dday in monthnumber10
hhour am/pm (1-12)number1
Hhour 24 hr. (0-23)number18
mminute in hournumber30
ssecond in minutenumber55
Smillisecondnumber978
Eday in weektextTuesday, Tue
Dday in yearnumber189
Fday of week in monthnumber2 (as in 2 nd Wed. in July)
wweek in yearnumber27
Wweek in monthnumber2
aam/pm markertextAM, PM
khour 24 hr (1-24)number24
Khour am/pm (0-11)number0
ztime zonetextPacific 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 with four or more characters in a group,the full form of the element to be used. If less than four 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, four or more in a group will display the full version, three will display the abbreviation, and two 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.

3.6.2.8.4. Custom Functions

In addition to the functions provided with ReportDesigner, 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 7.1.5.8.6 - Open Report Designer with Custom Functions.

3.6.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]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 create a formula of SUB1.{UnitPrice} and add it as a column in the main report. Then, create a second formula which applies aggregation to the first formula (ie, SUM(@sub_total)).

Although the main report can access any of the sub-report's columns or folders, sub-reports cannot access the main report's or any other sub-report's 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 then 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:

Click to view larger image

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.