Calculations

Data Access Studio provides two calculation methods to transform data:

  1. Calculation Columns
  2. Global Variables

Calculation Columns

Calculation Columns give you the power to create information derived from your grid. Calculation Columns differ from Excel cell calculations in that Calculation Columns work for any number of rows. This row-independence means you can design reports that are more useful and re-usable on JDE data than is possible with Excel.

Quick Facts:

  • Each Calculation Column lets you transform your data visually.
  • Many calculations support Quick Calculation. With Quick Calculation, you can select multiple columns, right-click any column header in your selection, and select Quick Calculation > ... to complete the calculation you need. See Associated Descriptions for an example.
  • Once you add a calculation, you will see the results of the calculation in your grid immediately.
  • You can edit a calculation by double-clicking its column header. (Edit Calculations with the Editor)
  • You can edit all calculations. (Edit Calculations with the Editor)
  • You can delete a Calculation Column by right-clicking the column header and selecting: Delete Column.
  • You can filter Calculation Columns just like any other column. Note: If you have a slow-running query, Calculation Column filtering won't improve database query performance.
  • Data Access Studio re-calculates when you find new data or when it detects a change that forces a recalculation.
  • Data Access Studio automatically determines dependencies, calculation order, and circular dependencies.

Formula

The formula calculation is a special Calculation Column that allows you write an expression combining many different calculations.

Only Rollup If

Most calculations will have a parameter named Only Rollup If. You can edit this parameter to have any condition you need that specifies whether you want the calculation value to apply. For instance, let's say you had a calculation that calculates the total price. You could edit the Only Rollup If parameter to specify that you only want the total price to apply if the customer has paid on time. Once you set the condition, then the total price will only have a value if the condition is met.

Layout

Each calculation will be on its own page with a description of the calculation, remarks, an example and a layout of what information is needed for the calculation editor.

  1. Date
  2. Math
  3. Database
  4. System
  5. Text
  6. Trig
  7. Enterprise One / World

Global Variables

Global variables are calculated values that can use parameters (See: Prompt for Values), system values (See: SystemVariables), or constants to create derived information prior to running a report.

Primary uses:

  • Create filters which derive from input parameters to filter grid column data. For example, if you have an input date parameter, you could make a global variable to create a filter selecting the first day and last day of the month relative to that input date. Then you can use the global variable as the filter to a grid column so your report shows only the range for the first day to the last day of the month for your grid data.

  • Create static information to use in Calculation Columns (see below).

  • Create static information.