Skip to main content

Data Extraction Example

Example
Scenario

This walkthrough covers the full process of creating a data extraction from scratch: setting up the workbook, configuring the extraction with filters, formatting the results, and building a chart.

By the end, you'll have a formatted table showing the top 5 customers for a selected year and month, with a bar chart that updates automatically when you change the filters.

What you need

This example uses the Invoice Analysis data model from a Sage X3 template. Make sure the template is installed and accessible in your environment before starting.

Step 1: Prepare the workbook​

Start by creating a new workbook and setting up two cells that will act as dynamic filter values. These cells will be referenced as filters in the data extraction, so changing them will update the results automatically.

  1. On the Workbooks tab, right click and select New Workbook.
  2. In cell A1, enter Year. In cell B1, enter 2017.
  3. In cell A2, enter Month. In cell B2, enter 4.

Step 2: Configure the data extraction​

  1. Select an empty cell below your filter values.
  2. On the Analysis tab, select Data Extraction Wizard.
  3. From the Data Model dropdown, select Invoice Analysis.
  4. Name the extraction SalesReport.
  5. On the Fields tab, add the following fields:
    • Customer Code
    • Customer Name
    • Ledger Currency Amount (Sum)
    • Ledger Currency Cost (Sum)
    • Ledger Currency Margin Amt (Sum)
  6. Select Create. The table appears on your sheet, grouped by Customer Code and Customer Name with the three measures summed.

Step 3: Add filters​

  1. Select any cell in the extraction table, then reopen the Data Extraction Wizard.
  2. On the Filters tab, select Add Filter and configure two filters:
    • Year: select cell B1 (Sheet!$B$1)
    • Month: select cell B2 (Sheet!$B$2)
  3. Select Update to apply the filters.
  4. Change the Year value in cell B1 to 2018. The extraction refreshes automatically with the new data.

Step 4: Format the results​

  1. Select the extraction table, then open the Table Design tab from the ribbon.
  2. Choose a formatting style for the table.
  3. Right click the last column, select Insert, then select Table Columns to the Right.
  4. Rename the new column to Margin Percentage.
  5. In the first row of the new column, enter the formula: Ledger Currency Cost / Ledger Currency Amount.
  6. Format the Margin Percentage column as a percentage from the Numbers section on the Home tab.
  7. Format the following columns as Accounting from the same Numbers section:
    • Ledger Currency Amount
    • Ledger Currency Cost
    • Ledger Currency Margin Amt
  8. Bold the first data value in both the Customer Code and Customer Name columns. This formatting is applied to the entire column on refresh.

Step 5: Limit to top 5 customers​

  1. With the table selected, open the Analysis tab and select Refresh Selection under Refresh.
  2. Select Data Extraction Wizard again.
  3. In the Options section, set Top X to 5.
  4. Select Update. The table now shows only the top 5 customers for the selected year and month.

Step 6: Build a bar chart​

  1. Select the Customer Name and Ledger Currency Amount columns.
  2. On the Insert tab, select Charts.
  3. In the Insert Chart dialog, select Bar and format the chart as needed.

The chart is linked to the extraction data. When you change the year or month filter values, both the table and the chart update automatically.


Result

You now have a formatted table showing the top 5 customers by ledger currency amount for the selected year and month, with a margin percentage column and accounting formatting applied. The bar chart next to it updates automatically when you change the filter values in cells B1 and B2.