Skip to main content

Configure User Defined Captions and Calculations

User Defined Captions

A user defined caption (also referred to as UDC) allows you to define a custom caption for a field or item throughout your inquiry:

  • Item – an individual item in any field, e.g. Period 1 can be represented with a UDC naming the specific month ‘January’.
  • Field – a generic field, e.g. the Business Unit Field could be represented with a user defined caption called ‘Branch Plant’.

Once defined, a user defined caption displays everywhere in your inquiry including:

  • Calculations
  • Column headings
  • Columns, Sorting & Grouping dialog
  • Descriptions dialog
  • Dynamic title selections
  • Filter name
  • Filter selections
  • Pivot Inquiry dialog
  • Time Series

The user defined captions set by default in the templates vary depending on the module. In the General Ledger templates, for example, these are the default captions:

  • General Ledger (AA Ledger) = Actual
  • Budget Ledger (BA Ledger) = Budget
  • BU Category = BU Cat Codes
  • Currency = Account Currency
  • Description = Alternate Object Description
  • Object Category = Object Cat Codes
  • PE Code = Posting Edit Code
  • Subledger Category = Subledger Cat Codes

Examples of custom User Defined Captions:

  • Period 1 = name of month, e.g. January
  • Period CE1 = Cumulative + name of month, e.g. Cumulative January
  • Period YT1 = YT + name of month, e.g. YT January
  • Year 2019 = ‘19
  • Year 2022 = ‘22

Create a User Defined Caption

To create a User Defined Caption (UDC), follow the below steps.

  1. On the Inquiry Objects menu, click User Defined Captions. The User Defined Captions dialog is displayed.

  2. Click on the Items or Fields tab, depending on which you wish to create a caption for.

  3. Click on New.
  4. Enter required fields:
    1. Field – the field you wish to create a caption for.
    2. If creating a user defined caption for an item, enter the specific item by either manually typing it in or using the Visual Assist.
    3. New caption – the new caption you wish to use.

  1. Click OK.

User Defined Captions and RIO

A user with a Configurator License can save user defined captions to RIO (Reusable Inquiry Objects) so that they can be used in other inquiries.

Any user can subscribe to user defined captions that are RIO objects.

Calculations

Inquiries can include pre-defined or user-defined calculations. Calculations can be used in many ways, such as providing variances, aggregations or Key Performance Indicators that are not directly available in your ERP system data. A calculation condition may be defined on any column, regardless of whether or not it is currently displayed in the result set.

Once defined, each calculated item can be manipulated just like any other inquiry item; you can move it, format it and even base other calculated items on it.

When a calculation is used, it appears as a column. The only exception to this is with row-based calculations, which are used with hierarchies.

There are 4 types of calculations that can be used:

  1. Static calculations – calculations based on specific data, no matter what filter selections have been made.
  2. Dynamic calculations – calculations based on filter criteria so the calculations update accordingly when the filter selections change.
  3. Cross-dimensional calculations – calculations based on multiple fields, such as a period number and a ledger type.
  4. Conditional calculations – calculations based on one or more conditions.

Pre-defined Calculations

Depending on the template, there are various pre-defined calculations that are appropriate for that specific module and template. Calculations are categorized; anything listed under General or Standard are those that are predefined. Uniquely within the Job Cost module, another section called Job Cost will be displayed, providing a series of predefined calculations specific to that module. When accessing calculations through the Calculations button, you will see these categories as tabs within the dialog, as well as a tab called All that includes all calculations sorted by name.

To add a pre-defined calculation to your inquiry.

  1. From the Inquiry Objects menu, click Calculations.
  2. In the Calculations dialog, mark the checkbox next to the calculation to add it to your inquiry.

Creating a Custom Calculation

Follow these steps to create a custom calculation.

  1. Access the Calculations dialog from the Inquiry Objects menu.
  1. Click New to open the Define New Calculation dialog.
  1. Create the calculation by adding the following:
    1. Name – a unique name used to identify the calculation, it also forms the ‘short code’ which you can type directly into a filter to include in the inquiry.
    2. Caption – used as the column name in the inquiry.
    3. Category – the tab under which the calculation will be stored in the dialog (by default, when you create a new calculation, a category called ‘User Defined’ is used unless it is changed by the user).
  1. Click Define to specify the default definition. In this dialog, there are 2 sections: the top section, which is designed to hold and edit your calculation, and the lower section, which is designed to provide ways of finding the specific items that make up your calculation:

    1. To add the formula, using the Filter Control (the drop-down menu in the middle of the dialog) choose the field you wish to base the result set on. (These must be defined as columns in the inquiry.)
    2. Within the field you choose, such as Year, you can choose the Visible or Search tab:
      1. Visible tab - displays the specific items currently visible in the inquiry.
      2. Search tab - search for items that are not currently in the inquiry to use them in the calculation (can do a wide open search by clicking the Search button or use the filter to narrow down your selection criteria before clicking Search).
    1. Double-click the item in the lower section to add it to the formula. (Alternatively, you can type it directly into the top section of the dialog.)
    2. Use the buttons from the Quick Formula Creation Bar to add operations to the formula as needed.
    3. If you have a list of items to add to the formula that will all be using the same operator in between them, highlight the items (you can use SHIFT + CTRL to multi-select) and then right- click to see the batch add commands: add, minus, multiply, and divide. Select the operator you wish to use in between all the highlighted selections.

    4. To use the calculation functions to select items by their filter selection position (explained in further detail below), click on the fn(x) button and choose the desired item:

    5. Once the field is added to the calculation formula, type in the filter position, such as Period(1) to signify the item listed first in the Period filter.
    6. Once the calculation formula is defined, click OK to return to the Define New Calculation dialog.
    7. Click OK to return to the main Calculations dialog.
    8. Mark the checkbox next to the calculation to include it in the inquiry.
    9. Click OK to return to the inquiry.
    10. Click OK to save changes and close the Add Default Calculation dialog.
  1. If creating a condition:
    1. Select the checkbox next to Conditional Overrides.
    2. Click New to define the default condition(s) in the Define Calculation Condition dialog.

      1. Select Label or Value.Then select Simple Condition, Value List Condition, Range Condition or Functional as required:
      • Simple Condition – specify a single value, such as 1.
      • Value List Condition – specify the individual values such as 1,2,4,5.
      • Range Condition – specify all values within a range such as 1:5.
      • Functional - this condition is used to return whether or not column values are null, or not null. When it is selected, ‘Is null’ and ‘Is not null’ are available for selection from the condition value drop-down.
      1. Select the required label or value condition from the Apply the calculation when list.
      2. Select the required operator (e.g. “equal to”) from the next list and enter an operand (if required) in the field next to it .
      3. The AND and OR options are used when specifying multiple conditions (see below).
    3. Click OK when you have finished creating the condition to save changes and close the Define Calculation Condition dialog.
    4. Specify the definition of the default calculation.
    5. Click OK to save changes and close the Add New Condition for Calculation dialog.
  2. Click OK in the Define New Calculation dialog to save changes and return to your inquiry.
Basing a calculation on a calculation

When defining the calculation formula, use the Filter Control to select Calculations to choose the calculations you wish to base your new calculation on. You can then choose specific calculation to use in the formula.

Static Calculations

A static calculation is based on static numbers and/or data from your inquiry. It is always based on the same information no matter what else changes in the inquiry, such as filter selections.

For example, you may want a static calculation that always adds certain periods together for a total amount. In this case, the formula would like this: {Period.1} + {Period.2}. The periods were selected from the Filter Control in the Edit Default Calculation dialog and the + sign was used between them to add them together:

Since this calculation is based on periods, it is automatically added to the Period Filter once you add it to the inquiry. The inquiry result would look like this if the periods as well as the calculation are in the Period Filter:

Dynamic Calculations

A dynamic calculation is based on filter selections so that when the filter selections change, the calculation updates dynamically.

For example, you may want to show the difference between your actual and budget ledger columns. Using the Quick Formula Creation Bar, select Ledger Types, and from the list select AA, click the – sign, and then select ledger BA. The individual selections are enclosed in curly brackets to aid legibility.

Since this calculation is based on ledgers, it is automatically added to the Ledger Filter once you add it to the inquiry. The inquiry result would look like this if both ledgers as well as the calculation are in the Ledger Filter:

You can now change the filter selection for Period and see that the calculation is now taking Period 2 AA – Period 2 BA:

You can also set up a dynamic calculation set on positional items within the filter, meaning it looks at the filter selection as far as in which order they are listed in the filter. In this case, traditional parentheses are used to define the positional items.

In the calculation definition, use the Calculations Function to select Ledger(…):

Ledger(…) appears in the calculation formula; at this point type in the filter position, such as 1 to signify the first ledger that is in the filter selection:

Now click the – sign and use the Calculations Function to again select Ledger(…) to finish the formula. Type in the specific filter position to complete the formula.

Again, since this calculation is based on ledgers, it is automatically added to the Ledger Filter once you add it to the inquiry. The inquiry result would look like this if both ledgers as well as the calculation are added to the Ledger Filter:

If you change the order of the filter selection so the BA ledger is listed before the AA ledger, the formula would change accordingly since it is taking the ledger in the first filter position and subtracting the ledger in the second position:

Cross-dimensional Calculations

A cross-dimensional calculation is one that is based off two or more fields, such as a period number and a ledger type. When a calculation is based off more than 1 field, it will appear in the filter for each of the fields being used.

For example, say you are displaying multiple periods in the inquiry but you only want to show the difference between the actual and budget ledgers for period CE12, you can use a multi-dimensional calculation to accomplish this. {Period.CE12} refers only to Cumulative December in the Period filter while {Period.CE12}{Ledger.AA} refers to the specific combination of CE12 in the Period filter and Actual in the Ledger Type filter. This would designate ‘Actual figures for Cumulative December’.

To calculate the difference between the actual ledger for period Cumulative December and the budget ledger for Cumulative December, it would look like this:

This is an example of what the inquiry results may look like with this calculation. Note that the calculation displays in both the Period and Ledger filters; this is because the calculation is based off both of those fields/dimensions. The difference between the budget and actual amounts is only being done for period CE12 and not for period 12 since that is what the calculation specifies.

Conditional Calculations

Conditional calculations allow you to define not only formulas as previously described, but to provide alternate calculations based on certain conditions. The types of conditions are:

  • Simple Condition – specify a single value, such as 1.
  • Value List Condition – specify the individual values such as 1,2,4,5.
  • Range Condition – specify all values within a range such as 1:5.
  • Functional - this condition is used to return whether or not column values are null, or not null. When it is selected, ‘Is null’ and ‘Is not null’ are available for selection from the condition value drop-down.

Once a conditional calculation has been defined, it can be used as a regular calculation and can be included in other calculation.

The result of any calculation can be used in the definition of a conditional calculation expression. Calculations can be selected from the Apply the calculation when list. For example:

You do not have to have a default calculation in order to create conditional overrides. Simply uncheck the option to the left of Default Calculation if you do not wish to include one.

For example, if we want to create a conditional calculation for man hours, we only need to select and display accounts that are associated with labor, overtime, etc. For this we have a formula that shows the values in the AU ledger. However, for the rest of the accounts, we do not want to display anything at all.

Example: Conditional Calculation

In the example shown below, we will display one column with certain accounts showing the value for the AU ledger and other accounts showing the value for the AA ledger. The reason why, in this specific case, is that the AU ledger values represent headcount, and we want to display those values together in the same column with accounts that have values in the AA ledger.

Prior to creating the conditional calculation, the result set looks like this:

In the conditional calculation, we want all the accounts up to 9900 to display the AA ledger type amount and those over 9900 to display the AU ledger type amount. Below are the steps to set this up:

  1. From the Inquiry Objects menu, click Calculations.
  2. In the Calculations dialog, click New to open the Define New Calculation dialog.
  3. Provide a name and caption for the calculation.
  4. To the right of the Default Calculation, click Define.
  5. Select ledger type AA:

  1. Click OK to return to the Define New Calculation dialog.
  2. To create a condition, check the box next to Conditional Overrides and click New.
  3. In the Define Calculation Condition dialog, using the options and drop-down menus, define the condition: Apply the calculation when object account is between 9900 and 9999.

    4. Note: You must change the Settings radio button to Range Condition in order to apply the calculation.

  4. Click OK to define the calculation override.
  5. Select ledger type AU:

  6. Click OK to return to the Define New Calculation dialog.
  7. The conditional calculation that has been defined now displays in the dialog:

  8. Click OK to return to the main Calculations dialog.
  9. Mark the checkbox next to the calculation to add it to your inquiry.
  10. Click OK to return to your inquiry.

After running your inquiry, you see that the conditional calculation displays all the AA values in the new ‘Actuals’ column for object accounts up to 9900, and for those within the range of 9900 to 9999, the AU values are displayed.

Note: You can remove the AA and AU ledgers from the filter if you only wish to see the conditional calculation column.

Multiple Conditions in a Calculation

Follow these steps to set up multiple conditions in a calculation.

  1. In the Define Calculation Condition dialog, once you have defined the first condition, click New to specify the next condition.
  2. Using the drop-down menus in the lower portion of the dialog, set the next condition.
  3. Combine the conditions using the ‘and/or’ functionality – this determines whether a single condition or multiple conditions must apply for the calculation to be performed.
  4. Click OK and all conditions will be listed at the top of the dialog.
  5. If you need to remove a condition once it has been added, select it in the top portion of the dialog and click Remove.

Adding, Editing, or Removing the Conditions

You can add/edit/remove conditions by editing the calculation and then using the buttons in the Conditional Overrides section of the dialog. With Edit and Remove, you must first select the condition to the left that you wish to edit/remove.

  • New - define alternate conditions
  • Edit – edit existing conditions
  • Remove – permanently delete conditions

Total for Conditional Calculations

Conditional calculations behave differently from normal calculations when calculating the total. Conditional calculations check that inside a given set of data, there is only one condition matched. If this is the case, the total is calculated using that formula; if multiple conditions match, the default calculation is performed.

Using Dates to Define a Condition

Using dates in a conditional calculation can be set as a simple condition; you can set the date as any of the following:

  • Equal to
  • Not equal to
  • Greater than
  • Less than
  • Greater or equal than
  • Less or equal than

Or, alternatively, set as a range with the dates as one of the following:

  • Between
  • Not between

An example of a conditional calculation based on a date range can be seen below:

Custom Columns

Custom columns allow you to perform many calculation functions using label columns, including numeric columns that were previously not available (such as Lead Time) and dates (such as Requested Date). They are so named because each one will produce a single new column in your inquiry.

Creating a Custom Column

The example below will create a column to calculate the days between the GL Date and the Batch Date in a GL Transactions inquiry.

  1. When creating the calculation definition, click on the fn(x) button.
  2. Select More Functions.

  3. Choose a Type of calculation.
  4. Depending on the type of calculation you choose, you will have different details to enter to complete the definition.

  5. Click OK to go to the Edit Default Calculation dialog. You will see the custom column expression at the top of the dialog.

  6. Click OK to return to the Define New Calculation dialog.
  7. Click OK to return to the main Calculations dialog.
  8. Mark the checkbox next to the calculation to add it to the inquiry.
  9. Click OK to return to your inquiry.

Custom Column Functions

When defining a calculation and clicking on More Functions in order to create a custom column, the calculations you can choose from are all described below.

Fixed Value Functions:

  • Date (Fixed): create a column that shows a specific date in all rows; used as a base for other calculations (as custom columns can reference other custom columns).
  • Date (Today): create a column that displays the current local system date in all rows; this can be used as a basis for calculations that require today’s date to be input (such as for late deliveries).
  • Numeric (Single): create a column that shows a specific value in all rows.
  • String (Single): create a column with the same text in all rows.
  • String (Dynamic): create a column that converts all label numeric columns (Dates, Account numbers etc.) into strings so that the created calculation can list both dates (as strings) and text and other mixed combinations, such as numbers and text, in the same column.

Date-based Calculations:

  • Days Back: count back from a given date by a specified number that is contained in another column. In a purchasing scenario, this would allow you to use the requested date and lead time in order to calculate the last possible order date for a product.
  • Days Between: display the difference between any two given date columns
  • Days Forward: count forward from a given date by a specified number that is contained in another column. In a purchasing scenario, this would allow you to calculate the expected arrival date of an item based on the order date and the lead time.
  • Date (Maximum): returns the maximum date from a list of dates.
  • Date (Minimum): returns the minimum date from a list of dates.

Data Part based Calculations:

  • Parts of a date: based on the day, month, quarter, or year of a selected date (see below an example of how to use these).

Using a Custom Column Function - Example

Creating custom columns to display the day, month, year and calendar quarter for a selected GL date.

  1. In the New Custom Column dialog, for the new column select Date Part from the Type list.
  2. Select GL date from the Select Date Column list.

  3. Next, choose Day or Month or Year or Calendar Quarter from the Select Date Part list.
  4. Click OK to exit out of all the dialogs until the Calculations dialog is the active dialog again.
  5. Now apply the defined calculation or calculations by clicking on their Active checkboxes and click OK.
  6. After running the inquiry you will now see the custom columns you defined displaying the DAY, YEAR, CALENDAR QUARTER, MONTH for the GL date field.
Aggregation Functions

The average and count functions are particularly useful when the data has been aggregated through the Columns, Sorting and Grouping function.

  • Average: returns the average value of a numeric column. There are two options available:
    • Average - excludes null values, which is useful for analyzing dates where null values can distort an average.
    • Average (Including Nulls).
Other Functions
  • Concatenate String: concatenates the contents of multiple columns.
  • Count: returns the number of values in the selected column. There are four options available:
    • Count - excludes null values.
    • Count (Including Nulls).
    • Count (Unique) counts only unique values. For example if you had the values 1,1,2,3, you would have a count of 3. (Within SQL this would be known as a SELECT DISTINCT.) This option will also exclude null values.
    • Count (Unique) (Including Nulls).
  • Maximum: return the highest numeric value in the selected column.
  • Minimum: return the lowest numeric value in the selected column.
  • Substring: create a new column that contains a portion of another column and specify the starting character (“Start Index”) and number of characters (“Length”) in the substring. The column you are basing the substring column on must be included in the inquiry. When setting up a substring column based on the Business Unit Column, you must take the column padding into account. There are 12 characters in the column, so you must count those characters when setting up the Start Index in the substring definition.
  • Subtotal: return the subtotal of the selected column and level.
  • Total: return the sum of the selected column.
  • Total (Distinct): return the sum of distinct values (distinct values including both duplicate values and values with the same amount).

Advanced Calculations

Using a Custom Column within another Custom Column Definition

You can use a custom column to define another custom column. Listed below are several examples of when you may do this.

  1. On an aggregated inquiry, you could find the average number of days between two dates by using the following function:
    Avg(DaysBetween(Column(ID_4311DATEORDER_DATE) ,
    Column(ID_DATEACTUALSHIPDATEPD_DATE))))
  2. Identifying negative and positive values (using the IsNegative and IsPositive functions) is particularly useful when you are trying to identify data that fits a Yes/No condition. A good example is ‘days late’, where orders can either be late or not late, depending on the number of days late. Using the above example, we can identify when the ‘average days between’ result is positive by using the following function: 
    IsPositive(Avg(DaysBetween(Column(ID_4311DATEORDER_DATE) ,
    
			Column(ID_DATEACTUALSHIPDATEPD_DATE))))
  3. By combining the Days Between, Today and As Of Date functions, we can determine that the AsOf date selected was x number of days in the past: 
    DaysBetween(Today() , AsOf())
  4. You can add, subtract, multiply and divide custom columns in association with standard numeric columns. The following is a ‘days between’ custom column being used in a calculation that involves a Lead Time column and a fixed numeric amount of three:
    5. Column(ID__COL__F4102_IBLTCM)-
    DaysBetween(Column(ID_4311DATEORDER_DATE) ,
    Column(ID_DATEACTUALSHIPDATEPD_DATE)) + Numeric(3)
  5. You can use custom columns within a conditional calculation. For example, this could be used for multiplying the value outstanding on a debt by the days late and only displaying a value if it is, in fact, late.
  6. You can use multiple functions in a calculation. For example, create an example of a percentage calculation made up of two days between. The first ‘days between’ identifies the total number of orders that are late. The second ‘days between’ provides the total count of orders. The division of the two provides the average: 
    Total(IsNegative(DaysBetween(Column(ID_4311DATEORDER_DATE) ,
    Column(ID_DATEACTUALSHIPDATEPD_DATE))))/
    Count(DaysBetween(Column(ID_4311DATEORDER_DATE) ,
    Column(ID_DATEACTUALSHIPDATEPD_DATE)))* Numeric(100)
Example: Concatenating fields

Use the Catstr custom column function to concatenate multiple columns in an inquiry. The asso- ciated text is ‘Catstr(Column1,Column2)’. The following is a Catstr custom column being used to concatenate the Second Item Number (LITM) and associated Description (DSC1) on a purchase order:

Catstr(Column(ID_SECONDITEMNUMBER_2ND_ITEM_NUMBER),Column(ID__CO
L__F4311_PDDSC1))
The end result is ‘3000Cyclometer’.

This function can also be nested to provide greater combinations of data. The following is a Cat- str custom column being used to concatenate Object Account and Subsidiary. Please note that a dot separator is included by using the String Command:

Catstr(Catstr(Column(ID_OBJECT),String(.)),Column(ID_SUB))
The end result is ‘1343.1140’.

To create a custom column using this function, you can use the Concatenate String function within More Functions, located in the Calculation Definition dialog:

You can also manually type in the concatenate string function within the Calculation dialog using this text: Catstr(Column1,Column2), and, optionally, any string of characters between the col- umns if desired (such as in the example described above).

Example: Creating a blank column

Blank columns are created using custom calculations. The below steps explain how to do this.

  1. From the Inquiry Objects menu, select Calculations.
  2. Click New to create a calculation/custom column.
  3. Define a name and caption for the calculation/custom column (note that the caption can be removed later), and then click Define.
  4. At the right side of the screen, click Functions (fn(x)) and then select More Functions:

  5. Select String (Single) as the Type, and for the Fixed String Contents, hit the space bar a few times before clicking OK (the spaces are used to create blank content in the column):
  6. Click OK twice to get back to the main Calculations dialog.
  7. Mark the checkbox to activate your new calculation and click OK.

  8. Position the blank column, as desired, in the report.
  9. To create a blank column heading, rather than having the caption display as the heading, use spaces as the title. This can be done by either:
    1. Right-clicking on the column and going to Set Column Header. Hit the space bar a couple times and then click OK.
    2. In Columns, Sorting and Grouping, highlight the column under Displayed Columns. Go to the Heading section, hit the space bar a couple times, and click OK.

  1. Now the column with a blank header in this case, displays in the inquiry:

Business Unit

Object Account

 

Subsidiary

Period 6 Actual 2013

 Cumulative 6 Actual 2013
1 000S   -11,553,912, 00    -33,899,370.63
1 1105   223,824.99  3,629,296.84

1

1110

AUTOBKST

358,119.97   

  5,806,874.28

1

1110

AUTOBSTl

537,179.96   

  8,710,311.41

1

1110

AUTOBST2

447,649.97  

   7,258,592.84

1

1110

AUTOBST3

223,824.98    

 3,629,296.42

1

1110

BEAR

358,119.97   

  5,806,874.28

1

1110

FIB

447,649.97    

 7,258,592.84

1

1110

FRANCE

447,649.97  

   7,258,592.84

1

1110

PAYROLL

537,179.96   

  8,710,311.41

1

1131

 

549)09,94    

 13,262,535.70

Grand Total

 

 

-7,423,002.32

 37,431,908,23

Basing a Calculation on another Calculation

You can base a calculation on other calculations.

The below example shows how the Budget Variance Amount (VARA) is calculated by subtracting the Actual Amount (ACTAMT) calculation from the Revised Budget Amount (RBA) calculation:

Calculated items can be included in the formula of a new calculation by selecting the Calculations option from the drop-down list and then selecting the appropriate calculation from the list of available selections:

The list of available selections is based upon which calculations have previously been used. Using a calculation causes it to load. This ensures the best possible performance so that the system does not load unused calculations.

Other Functions in the Calculations Dialog

Editing a Calculation

To edit a calculation:

  1. In the Calculations dialog, highlight the calculation you wish to edit.
  2. Click Edit.

Removing a Calculation

To remove a calculation:

  1. In the Calculations dialog, highlight the calculation you wish to permanently delete from the inquiry.
  2. Click Remove.

Copying a Calculation

To copy a calculation:

  1. In the Calculations dialog, highlight the calculation you wish to copy.
  2. Click Copy.
  3. Provide a different name for the new calculation.
  4. You can now use the Edit button to edit the new calculation’s properties to make it unique.

Reset - If you remove a default calculation and want to get it back, click on Reset, shown in the bottom left-hand side of the Calculations dialog.

Precedence - The order in which calculations are performed is listed within the Calculation Precedence dialog. Since you can create row-based and columnar calculations, it is important to specify the precedence when these intersect.

To change the order in which calculations are performed, follow the below steps:

  1. From the Inquiry Objects menu, click Calculations.
  2. Click Precedence to open the Calculation Precedence dialog.

  3. Uncheck Automatic to override the automatic settings.
  4. Using the arrows, re-sequence the calculations.
  5. Click OK.

Execute Calculations for Totals Option

This option determines how the total for the calculation column is performed, whether it performs the same calculation in the total row or adds up all the values in the column for a total.

Option selected - the calculation is performed in the total row for a total value

Option not selected - all the values in the column are added up for a total value

The effect of this option can be seen in the following two examples, which are using the standard Variance % calculation provided with the General Ledger’s Balances Template.

The Variance % calculation provides a percentage based on (Ledger(1) – Ledger (2))/Ledger(2) *100. In this example this is the General Ledger – Budget Amount Ledger / General Ledger * 100.

In the first example, the Execute calculation for totals option is enabled for the calculated column Variance %.

As a result, the Grand Total is correctly showing 42.38% for the Variance % Column; the Grand Total lines for Actual and Budget ledgers were used to calculate this value.

When the Execute calculation for totals option is disabled, the Grand Total for the Variance % column is showing a percentage of -12.32%. This is the sum of the variance % column values and is incorrect for the purpose of this inquiry.

Consolidate Conditions Option

With the Consolidate Conditions option, you are able to apply a condition at a more detailed level than what is displaying in the inquiry. This means that you can apply a condition to a column even if that column is not being used in the inquiry. Without this option enabled, columns being used in the conditional calculation have to be displayed in the inquiry in order to obtain correct results.

The following example shows a series of expense accounts for various business units. As part of a next year budgeting cycle, a calculation has been created to increase certain expense accounts to look at what the values would be if increased:

The result is shown below:

At this point, you may wish to consolidate the results down to the specific business unit as shown below, which is done by removing the object account and description columns. When you do this, you see that the calculated column is no longer calculating the correct results. This is because the object account column, which the conditions are based on, is no longer being used in the inquiry.

To be able to perform the conditional calculation in a consolidated inquiry, enable the Conditional Overrides option:

When you click OK, the below dialog will be displayed if you also have a Default Calculation. You can choose whether to use the default calculation AND the conditional overrides or only use the conditional overrides.

Now the Consolidate Conditions functionality only takes into account the Conditional Overrides included within the calculation.

The change in the result set is shown below:

This Consolidated Total for Next Year Expenses matches the total of the non-consolidated result set as shown below:

Calculations and RIO

A user with a Configurator License can add a calculation to RIO, or Reusable Inquiry Objects, making it available to other users in order to use it in their inquiries.

To add a calculation to RIO:

  1. Open the inquiry where the item exists.
  2. Access RIO by clicking on Reusable Inquiry Objects from the Inquiry Objects menu. The Reusable Inquiry Objects dialog is displayed.

  3. Highlight the calculation on the left panel to be moved into RIO.
  4. Highlight the folder where you want it moved to on the right panel.
  5. Click the arrow in the middle of the screen to move the item over to the right.

Subscribing to a Calculation in RIO

If a user with a Configurator License has added a calculation to RIO, users can subscribe to it in their own inquiries. To subscribe to a calculation that has been added to RIO:

  1. Open the inquiry in which you want to subscribe to the calculation.
  2. Access RIO by clicking on Reusable Inquiry Objects from the Inquiry Objects menu.
  3. Subscribe to the calculation by checking the box to the left of it.
  4. Click either Apply to apply the calculation or OK to apply it and exit the dialog at the same time.
Example: Using the SubList function in a custom calculation

Oracle EBS users can use the SubList function in a custom calculation. This is particularly useful if you have stored segmented sections of data in a custom field and you would like to display a particular section of that data (e.g. city, state or country) into a separate column.

In the example below, the Derived Locale column contains three segmented sections (city, state, country), separated by a comma. A custom column has been created to display the first section only (city) in the Conditional SubList column.

The calculation is created by using a SubList command as shown below. The components of the calculation are:

  • SubList
  • Select the column from the inquiry
  • String (%COMMA%)
  • Note that you should be able to use any delimiter of you choosing, with the exception of parenthesis (). For example, if you would like to use colon (:), the String would be (:). Comma is the only symbol that requires the special syntax of the percent sign %.
  • Numeric (x): This is where you indicate which segment you would like to display in your column. In the example below, the first segment of the data will be displayed in the column. The calculation shows that the data between Numeric(0), and Numeric(1) will be displayed. Numeric(0) is no comma and Numeric(1) is the first comma.

Example: Displaying the length of a column

You can display the length of a column by creating a custom calculation. In the following example, the calculation is set up to display the length of the subsidiary.

  1. From the Inquiry Objects menu, click Calculations.
  2. Select New to open the Define New Calculation dialog.
  3. Input Length for the name of the calculation (this must be the name of the calculation).
  4. Input an appropriate caption.
  5. Select Define.
  6. The calculation must be keyed in manually as length cannot be selected from the list of functions.

The length of the subsidiary now appears on the inquiry:

Note: For SQL Server, this function will return the length of the actual characters from the column selected, excluding any trailing blanks that may exist.
For Oracle and DB2, the LENGTH function needs to be used with the Trim function to return the actual number of characters from the column selected.