Data Extraction Example

In this example, you will create a workbook, set up a data extraction, and format and use the data extraction to show the top 5 customers for a specific year (using a Sage X3 template). Finally, you'll create a bar chart to visualize the results.

Create and set up your workbook

1. On the Workbooks tab, right-click and select New Workbook from the context menu.
2. Enter Year in cell A1 and 2017 in cell B1.
3. Enter Month in cell A2 and 4 in cell B2.

Set up the data extraction

1. Select an empty cell.
2. On the Analysis tab, select Data Extraction Wizard.
3. From the Data Model dropdown menu, select Invoice Analysis.
4. Name the data extraction SalesReport.
5. On the Fields tab, add the following fields to the data extraction (use the search bar if needed):
   • Customer Code
   • Customer Name
   • Ledger Currency Amount (SUM)
   • Ledger Currency Cost (SUM)
   • Ledger Currency Margin Amt (SUM)
6. Select Create. The output appears on Sheet1. The header is visible, the total row is hidden.
   The result is grouped by Customer Code and Customer Name. Ledger Currency Amount, Ledger Currency Cost, and Ledger Currency Margin Amount are aggregated using SUM.
7. Select any cell in the extraction table, then re-open the Data Extraction Wizard.
8. In the Filters tab, click Add Filter, then configure the following:
   • Year – Select cell B1.
   • Month – Select cell B2.
9. Select Update to apply the filters. Then change the Month value in cell B2 to 2018.
   The data extraction refreshes automatically.

Format and use the data extraction

1. Select the data 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 choose Table Columns to the Right.
4. Rename the new column to Margin Percentage.
5. In the first row, enter the formula: Ledger Currency Cost/Ledger Currency Amount.
6. Format the Margin Percentage column as a percentage:
   Select the first cell in the column. On the Home tab, in the Numbers section, select Percentage from the dropdown menu.
7. Bold the first data value in both the Customer Code and Customer Name columns.
8. Format the following columns as Accounting.
   On the Home tab, in the Numbers section, select Accounting from the dropdown menu.
   • Ledger Currency Amount
   • Ledger Currency Cost
   • Ledger Currency Margin Amt
9. With the table still selected, open the Analysis tab and select Refresh Selection under Refresh.
10. Still with the table selected, select Data Extraction Wizard.
11. In the Options section, set Top X to 5, then select Update.
    The top 5 customers for the selected year and month appear.

Turn your results into a bar chart

1. Select the Customer Name and Ledger Currency Amount columns.
2. On the Insert tab, and select Charts.
3. In the Insert Chart dialog, choose Bar, and format the chart as needed.
   Now you have a visual representation based on your data extraction. The chart will update automatically when you change the filter.