Skip to main content

Manage Report Variables

Custom report variables can be defined by users. These will then be available for selection when setting up the filter layout of an inquiry (in the Filter Layout dialog) and can be included in report titles.

This facility is accessed from the Report Variables button on the Inquiry Objects menu in the Inquiry Objects group. This will open the Manage Report Variables dialog:

The dialog lists the existing custom report variables. These can be edited as required, or deleted using the Delete button.

To create a new report variable, click Add and enter the details in the fields provided. Text, Date, Boolean and Numeric types of variables may be defined.

All fields, including the Default Value and Current Value fields, should be completed.

Aging

Aging provides a way of grouping amounts into columns using a specific date field. Aging can be used in any template that has a date field, such as AP Aging, As-Of AR Aging, Job Cost Transactions, GL Transactions, Inventory, Purchasing and Sales. These date ranges can be defined using Days, Weeks, Months and Year series settings, allowing date-based inquiries to show historical trends over specific date ranges. Any date field and any value field available in the inquiry can be used in creating aging

categories. Inquiries that share common aging categories can be linked with each other to provide more detailed analysis.

Accounts Payable and Accounts Receivable templates have Aging Categories set up by default but they can be added to inquiries in other Modules. In the AP and AR templates, the Aging Category needs to be enabled in order to be used on the inquiry.

Aging is accessed through the Aging Categories button on the Inquiry Objects menu in the Inquiry Objects group.

Use Aging

To use an Aging category that is already set up:

  1. From the Inquiry Objects menu, click Aging Categories to open the Aging Categories dialog:

  2. Check the box next to ‘AGED’ to enable all the aging categories in the series. Optionally, expand ‘AGED’ to see all the categories included in the series; any can be enabled/disabled for use in the inquiry.

  3. Once they are enabled, you will see the categories in the Amounts Filter when you return to the inquiry.

To add/edit an Aging Category:

  1. From the Inquiry Objects menu, click Aging Categories.
  2. Highlight the top level and click Edit.
  3. This launches the Define Aging Category dialog, where you can change the aging category as needed.

From the Aging Categories dialog, you can:

  • New – create a new Aging Category
  • Edit – edit an existing Aging Category
  • Rename – rename an Aging Category
  • Remove – delete an Aging Category

When you either create a new aging category or edit an existing one, you have access to the settings in the Define Aging Category dialog:

The General settings include:

  • Name – name of Aging Category
  • Short Code - a unique identifier for the Aging Category
  • Caption – caption to be used in the column header of the date range categories
  • Date Field – date that the categories are based on
  • Element to Age – select either a specific Value Field or Unique Count to age
  • Retain user defined captions – choose to apply any user defined captions that are set up

The Series settings include:

  • Days/Weeks/Months/Years – set the date range categories by days/weeks/months/years. Depending on the setting you choose here, the settings to the right change accordingly where you will define more information about the date ranges, such as start and end date if using a series other than days.
  • Custom Range – if using Days, you can base the series on a custom range. Each custom range is separated by a semicolon (;). Using the less than symbol (<) at the beginning and greater than (>) at the end will include data to any date.

Click Preview to view the categories as they are currently set up.

Custom Date Range in an Aging Category

As an example, create four custom date ranges based on 30-day ranges, where the first date range reflects AR invoices that were due in the last 30 days, and the last date range will show those invoices that are due within a period of 61 to 90 days. In this example, we are choosing to show invoices that are based on the Open Amount and Due Date. The overdue invoices will be highlighted in red to emphasize them.

The finalized aggregated AR report looks like this:

Aging Start Date Filter

Aging Start Date can be used as a filter on your reports so that your input data into this field will dynamically update the previously defined aging category buckets.

This is a great filter you can add to your reports to enable users to define whatever start date they desire to age their data.

Example of the Aging Start Date Filter

The following is an example of using the Aging Start Date Filter in a sales order inquiry. The aging categories display the amounts sold by Invoice Date for a sales order report.

  1. Define your Aging Categories as normal.
    1. From within the desired inquiry, select Inquiry Objects.
    2. Select Aging Categories.
    3. Select New and set up your aging categories as desired.

  2. Add the Aging Start Date Filter to your inquiry. (This requires a Configurator license.)
    1. From within the desired inquiry, select Configurator.
    2. Select Filter Layout.
    3. Aging Start Date will be found in the Variables folder in the left panel.
    4. Add AGED Start Date as a filter.

  3. On your inquiry, notice that your aging buckets will now be updated based on the date you have put in your Aged Start Date Filter.

  4. This Aged Start Date Filter can easily be added to your reports so that users can view updated aging information as necessary. You can select from the drop-down menu either Fixed Date or Today. Additionally you can select As of Date if the report contains an As of Date.

Note: If defining Aging by Week, Month, or Year, you will have both of the following Aging Dates to select and include as filters:

  • Aging Start Date,
  • Aging End Date

Aging Categories Based on Calculated Column Values

Users can pick any calculated value in a column when defining aging categories.

For example, the JDE Invoice Template includes Open Amount and Discount Amount values. A user may want to age the Open Amount by subtracting the Discount Amount, so they can see how much they need to pay customers in a given period.

In the example image below, the Calculated Balance Amt calculated column is included in the Value Field dropdown so that users can create categories based on it.

As Of Functionality

The As Of Functionality is available in the Accounts Receivable and Accounts Payable Modules; the templates that contain this functionality are prefixed with As Of in their name.

The As Of Functionality allows you to see what the Accounts Payable and Accounts Receivable records would have looked like for a specific date in time on both the domestic and foreign currencies of the transaction. These records are analyzed in real time from the respective data tables. The As Of Date can be specified and modified when running the inquiry without requiring the intervention of the IT Department to run batch processes for the required dates.

The As Of Templates have a special date filter called As Of Date; by changing this date, you can restrict the transactions (AP payments or AR receipts) to a specific date in time. In addition to this filter is a control that allows you to specify whether to Only include invoices open on As of Date.

If required, additional As Of values can also be added to the inquiry, providing further analysis as to the state of the transactions at the time of the Date As Of. The below example shows the As of Values that are available within the Visual Assist for the Amounts Filter:

Using the As Of Functionality with Aging

The combination of the two functionalities is quite powerful as the standard As Of value presents the values associated with the As Of Date, but the aging category can also show what the associated past/ future amounts would be for the allocated date ranges in conjunction with the As Of date.

Here is an example of a standard As Of Invoices report which has been aggregated by Customer Number:

In the Define Aging Category dialog, you can use an As Of value in the Value Field; use the drop-down menu to choose the appropriate Value Field:

Calculation for the Open Amount On As Of Date Calculation

The Open Amount On As Of Date measure shows the open amount of an invoice as it was at any date in the past. This information is not stored in ERP data so it must be calculated by examining the invoice details and payment history.

Note: The term “payment” is used to describe all transactions that cause the current open amount to be adjusted up or down. This could include cash receipts, spread adjustments or voiding entries. Generally all adjustment types are included in Hubble unless they are specifically excluded.

Invoice Details for JD Edwards System Users

Invoice details are stored in various tables depending on the version of JD Edwards and whether the Module is AP or AR. For World, the AR Ledger table is the F0311. In E1, this is the F03B11. For AP in World and E1, the AP Ledger table is the F0411.

These tables contain a Gross Amount field (AG) and Open Amount field (AP). The Gross Amount field stores the full original amount of the invoice. This value does not change, except in special circumstances (see voided invoices below).

The Open Amount field also initially stores the full invoice amount, however as payments come in, the value is updated. This way it always shows the amount that is currently outstanding.

By default, Hubble uses the GL Date (DGJ) to determine when the invoice is considered to have been opened. If the GL Date is after the As of Date and the Only include invoices open on As of Date checkbox is enabled, the invoice will be considered open.

The only exception to the GL Date is in AP, where there is an option that allows As Of to Calculate Opening date based on either the GL Date or the Invoice Date. This is set in Inquiry Options.

Payment Details for JD Edwards System Users

Payment Details in AR World

World AR stores both the payment information and invoice information in the Customer Ledger (F0311). Invoice records can be differentiated from payment records by examining the Document Number Matching (RPDOCM) or Document Type Matching (RPDCTM) fields. These will be blank or 0 on invoice records and populated on payment records.

Payment records are matched to the correct invoice record. The As Of Code reads the Gross Amount (RPAG) and Discount Taken (RPADSA) in combination with the GL Date (RPDGJ) to determine when a payment was made. The Gross Amount is the value of any payments; payments and adjustments that decrease the open amount have a negative value and adjustments that increase the open amount (for example voided payments) have a positive value.

Payment Details in AR Enterprise One

E1 AR uses a dedicated Receipt Detail table (F03B14) to store payment details. The As Of Code reads the Payment Amount (RZPAAP) and Discount Taken (RZADSA) from the Receipt Detail. These values are used in conjunction with the Date - Matching Check or Item (RZMDTJ) to determine when a payment was made. The As Of code also joins to the Customer Ledger table (F03B11).

Payment Details in AP

AP uses the same table structure in both World and E1. The structure is very similar to E1 AR in that it uses a dedicated Payment Detail table (F0414) to store payment details. Payment Detail has the same fields as its AR E1 equivalent, Receipt Detail (F03B14), with some exceptions.

The As Of Code reads the Payment Amount (RNPAAP) and Discount Taken (RNADSA) from the Payment Detail in conjunction with the Item Date (RNDMTJ) from the Payment Summary (F0413) table to determine when a payment was made.

Calculating Open Amount On As Of Date

The Open Amount On As Of Date is calculated by taking the invoice Open Amount (AP) and reversing out any payments or adjustments that occurred after the As Of Date.

There are two special cases where the calculation technique differs slightly:

  1. Unapplied cash
  2. Voided invoices
Unapplied Cash

Unapplied Cash, which exists only in AR, is a payment assigned to a particular customer, not to an invoice. Therefore in AR there are two live aged measures, Open Amount On As Of Date and Unapplied Amount On As Of Date. All invoices with a Document Type (RPDCT) of ‘RU’ are included in the unapplied total, and all other invoices are applied to the open total.

When an unapplied payment is received, a payment record and a dummy invoice record are created in the Customer Ledger table (F0311 – World) or Receipt Detail (F03B14 - E1). Each new unapplied payment generates a new invoice record, and every unapplied invoice has one payment record.

With an unapplied invoice, the open amount is initially set to the same value as the payment amount. In order to correctly calculate the Unapplied Amount On As Of Date, the initial payment that generates the invoice is disregarded.

In AP, payments are assigned to a specific vendor only.

Voided Invoices

Voided payments in the Payment table appear like a reverse payment record with a positive Payment Amount (PAAP).

Occasionally, entire invoices get voided in AR/AP on E1. When this happens, the As Of Code uses the Gross Amount from the voided invoice instead of the Open Amount.

When an invoice is voided, all previous payments for that invoice are voided. Void records are created in the Payment Details table (F03B14 for AR in E1, F0414 for AP), matching all previous payments. The invoice record (F03B11 for AR in E1 and F0411 for AP) now has an offsetting entry, which sets both the Open Amount and Gross Amount of the invoice back to $0.

As of Optimization Level Setting

For As Of Templates in both the AR and AP Modules, there is an As of Optimization Level option, set in Inquiry Options that can be used under certain circumstances to assist performance of As Of reports. You can choose between 3 settings:

  1. Auto (Recommended) - Select the best method from the next two options based on the filters set in the report. This option should give the best performance in the majority of cases.
  2. Optimize for shortest SQL - The underlying SQL for the As Of Functionality is reduced where possible (this is useful for older databases that have limited SQL length processing).
  3. Optimize to allow best use of indexes - Switches to a longer SQL setting, joining the underlying fact table with multiple sub-selects. Each sub-select will take into account the underlying filters present in the report. If these filters correspond with the indexes on the fact table, speed improvements should be seen.