You can create new fields in an Ad Hoc View by applying mathematical formulas to a View’s existing numeric fields. For example, consider a View that includes both a Cost and a Revenue field. You could calculate the profit for each record by creating a custom field that subtracts the Cost field from the Revenue field. Create a custom field by selecting an existing field in the Measures section of the Data Selection panel of a table-type View; the Create Custom Field menu item appears on the context menu when you right-click a numeric field.
The Ad Hoc Editor supports three types of functions:
• Basic functions include addition, subtraction, multiplication, and division. You can use these functions with a constant (such as multiplying the cost by two to calculate a standard 50% markup), or you can use them with multiple fields. Select multiple fields using Ctrl-click; the columns’ borders change color to indicate that multiple fields are selected; right-click to open the context menu and create a custom field.
• Advanced functions include round, rank, percent of total, percent of row group, and percent of column group. These functions don’t take constants, nor do they support multiple fields.
• Date functions can calculate the difference between two dates in a number of intervals, such as years, weeks, days, and seconds.
A custom field can include only a single function. To use more than one function, create two custom fields, with one building on the other. In this case, pay special attention to the order in which you create the custom fields, as this may affect the results; for example, rounding then multiplying is different from multiplying then rounding.
Because custom fields can build upon one another, you can create complex calculations. For example, you can divide the Profit custom field in the previous example by the Revenue field to express each record’s margin as a percent. When a custom field is the basis of another field, you can’t delete it until you delete the one that builds on it.
When working with multiple fields:
• You can only select basic functions and date differences.
• You can multiply or add any number of fields, but ordered functions (subtract and divide) and date functions can only be used with two fields at a time.
• For ordered operations, the order in which you select fields matters. For example, to calculate profit in the example above, click Revenue first, then Ctrl-click Cost. When you right-click, select Create Custom Field and choose subtraction, the Ad Hoc Editor assumes you are subtracting the Cost field from the Revenue field. You can always change the order of fields by clicking Swap.
To create a custom field based on one field:
1. In the Measures section of the Data Selection panel, right-click a numeric field and select Create Custom Field from the context menu.
2. Depending on the type of function you want to create, select options in Basic Functions or Advanced Functions.
3. In Basic Functions, you can add, subtract, multiply, or divide the field values by a constant, which you enter as a number in the selected field; this number is the constant to use in the formula.
4. If you selected a basic function, click Swap to put the constant before the field in the formula; this is only useful in conjunction with ordered operations.
5. Click Create Field.
The custom field appears in the list of available measures.
To create a custom field based on multiple numeric fields:
1. In the Measures section of the Data Selection panel, Ctrl-click two or more numeric fields, right-click one of the selected fields, and select Create Custom Field from the context menu.
2. Select from the following options:
• If you selected just two fields, choose +, -, *, or /.
• If you selected more than two fields, choose Add All or Multiply All.
3. Click Swap from the context menu to change the order of the fields in the formula; this is only useful in conjunction with ordered functions.
4. Click Create Field.
The custom field appears in the list of available measures.
To create a custom field based on two date fields:
1. In the Data Selection panel, Ctrl-click two date fields.
2. Right-click, and select Create Custom Field from the context menu.
3. Select one of the Date Difference intervals.
You can compare dates only when exactly two date fields are selected.
4. Click Create Field.
The custom field appears in the list of available measures.
Keep the following in mind when creating custom fields:
• To edit an existing custom field, right-click it and select Edit Formula from the context menu. You can then choose a different function, or enter a different value (if you are editing a basic function).
• When you create a custom field based on a field on the panel, the new field appears in the panel; when you create a custom field from a field in Data Selection, it appears at the bottom of the list.
• The percent of group functions are unique among the custom functions in that they calculate values based on the grouping defined in the report. Thus, the following limitations apply:
• The percent of column group functions are meaningless in charts and tables. In these contexts, these functions always return 100.
• Custom fields using the special percent functions cannot be used as a group, as a filter, nor as the basis for other custom fields.
• The summary function for custom fields using the percent functions is always Sum; this cannot be modified. Furthermore, the percentage is always calculated on the sum of the original field, regardless of the summary function applied to the field in the table or crosstab, if any.
• The Round function uses the standard method of rounding (arithmetic rounding), in which decimal values greater than 0.5 are rounded to the next largest whole number, and values less than 0.5 are rounded down.
• A custom field’s label is determined by the fields, constants, and functions it includes; for example, the default label for the Profit field in the example above is Store Sales - Store Cost. Right-click the column and select Edit Label to enter a more compelling name.
• By default, the Ad Hoc Editor supports only two decimal places. If the custom fields return data that are significant to the third decimal place, you can add new masking options by editing configuration files. For more information, refer to the JasperReports Server Administrator Guide.
• You can’t delete a custom field that is used in the Ad Hoc View panel. First, remove the custom field from the Ad Hoc View panel and then delete it from the Data Selection panel.