List Calculation

A List is like a mini spreadsheet where all formulas are referenced by name (as opposed to Excel where formulas are usually referenced by cell position e.g. A47). Named formulas are an advantage when you need to modify your spreadsheet formulas. For instance, if you have a label named [Sales], the label means the same thing regardless of where it appears in the List.

A list can be anything: a list of accounts, business units, ledger types, customers, etc. Data Access Studio lets you define the lists you need and assign the list a label for quick reference. For instance, you can define your list of P&L account and label the list as "P&L Standard". You can then define cell and row calculations over any label in your list. For instance, you can sum up several items or take a percent of one label to another.

To create a List

You can setup a List with the Calculation Editor or Quick Report Wizards. In the Calculation editor, select the List calculation and click Edit List. Once created, you will almost always Group by the list column. You can group by more than one list. A list may be grouped at any level as well.

A List consists of the following:

  1. Label - unique text that identifies the item. IMPORTANT:Labels need to be unique for the thing they represent. If you need duplicate labels, you can append as many spaces after your label to make a unique name for it. In your formulas, the spaces will be represented as an underscore so: SALES and SALES_ will be different labels in your formulas (the first has no spaces after it the second has one space).
  2. Criteria
    • (optional). If present, the criteria says what values to roll up for the label. For instance, if the criteria is Obj Acct 1* that means any row where Obj Acct starts with 1 will be rolled up into the total amount for the label.

  3. Default Expression (optional). The expression evaluates slightly differently based on two cases.

    • Case 1. Label has criteria. In this case the expression operates on the elements that make up the label. Use the Apply Operation command to easily apply operations to multiple rows of the list.

    • Case 2. Label has no criteria. This is the row totals case. In this case the expression defines what the label is. e.g. [Bank 1] + [Bank 2] + [Bank 3] is the formula for the label Bank Total.

  4. Additional Expression (optional). This is an extra set of formulas that can use any of the symbols in the list or default expression. Use this to create secondary calculations such as percent of row values.
  5. Override Style (optional with Automatic Presentation only)

To

Do this

Add a new row

Click Insert Row Before or Insert Row After. The editor will create a new row above/below the row you have highlighted. Alternatively, you may enter text in the new empty row at the bottom in the Description column.

Define how the label is rolled up

Click the box under the Pattern column and click the ... visual assist. See Define Criteria for the List Item for instructions on defining the "pattern".

Define a row calculation

Click the box under the Row Calculation column. See Define row calculations for the List item for further instructions.

Delete list items

Hold CTRL key down as you left-click multiple rows. Then click the Delete Row button.

Copy and Paste

Hold CTRL key down as you left-click multiple rows. Then click the Copy button. Highlight the row where you would like to paste the rows. Click Paste.

Move multiple list items

Hold CTRL key down as you left-click multiple rows. Highlight the row you want to move. Then click the "Drag and drop grip bar" and drag row above or below current position. Release mouse button to drop to new position. Grid will automatically scroll as you drag near the top or bottom.

Save your list

Click File | Save List. See Save a List and Share a list

Load a previously saved list

Click File | Load List.

Delete a previously save list

Click File | Load List. See Work with Your Lists.

Export your list to XML

To export your list to XML, click File | Export. Exporting to XML can be useful if you want to do mass search and replace or other options in an XML editor.

Import your list from XML

Once you have modified an XML export, you can import it back using File | Import.

Create a secondary expression

Click Add Column. This creates a new expression column. Create formulas in this column that combine symbols from the list and default expressions. See Define secondary expressions.

Delete secondary expression

Highlight a secondary expression column. Click Delete Column.

Apply an expression to a grid column

Back on your report grid, right-click the Column Header. Select Apply this list expression -> and select a valid list expression.

Load a pre-defined list

Click the Import from JDE tab. Select the list that you would like to import.