Skip to main content

Duplicator Example

Example
Scenario

This walkthrough covers the full process of setting up a Duplicator: creating a workbook with a data extraction, configuring the Duplicator to generate one sheet per sales representative, and refining the results with filters.

By the end, you'll have a workbook with a separate sheet for each sales representative who sold at least 1,000 products in 2018, each showing their invoices with product, date, amount, and customer details.

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 with two sheets. The first sheet holds the report layout and a reference cell for the sales representative. The second sheet holds a year filter.

  1. On the Workbooks tab, right click and select New Workbook.
  2. On the first sheet (Sheet1), enter Sales Rep in cell A1 and AO251 in cell B1.
  3. Add a second sheet called Filter. Enter Year in cell A1 and 2018 in cell B1.

Step 2: Configure the data extraction​

  1. On Sheet1, select an empty cell below the Sales Rep row.
  2. On the Analysis tab, select Data Extraction Wizard.
  3. From the Data Model dropdown, select Invoice Analysis.
  4. Name the extraction ExtractionSalesRep.
  5. On the Fields tab, add the following fields:
    • Customer Code
    • Customer Name
    • Product Code
    • Date
    • Document Number
    • Quantity Sold (Sum)
    • Ledger Currency Amount (Sum)
    • Ledger Currency Cost (Sum)
    • Ledger Currency Margin Amt (Sum)
  6. On the Filters tab, select Add Filter and configure two filters:
    • Sales Rep 1 Code: select cell B1 on Sheet1
    • Year: select cell B1 on the Filter sheet
  7. Enable Refresh On Open so the data refreshes each time you open the workbook.
  8. Select Create. The table appears on Sheet1, filtered to sales representative AO251 for the year 2018.

Step 3: Generate one sheet per sales representative​

  1. Select cell B1 on Sheet1 (the cell containing AO251).
  2. On the Analysis tab, select Duplicator.
  3. From the Data Model dropdown, select Invoice Analysis.
  4. In the Field dropdown, search for and select Sales Rep Code.
  5. Confirm the cell reference is Sheet1!$B$1.
  6. Select Preview Values to view all available sales representatives. You should see 24 values.
  7. Enable Refresh On Open to refresh the duplicated sheets each time you open the workbook.
  8. Select Save. The workbook now contains one sheet per sales representative, each showing their invoices for 2018.

Step 4: Refine results with filters​

The Duplicator currently generates a sheet for every sales representative, including those with low activity. Adding a filter limits the sheets to only the representatives that meet your criteria.

  1. Select cell B1 on Sheet1.
  2. On the Analysis tab, select Duplicator. Your existing settings are still visible.
  3. Select Add Filter and configure:
    • Quantity Sold greater than 1000
  4. Select Preview Values. You should now see 6 values, meaning only representatives who sold at least 1,000 products in 2018 are included.
  5. Select Save. The workbook now contains 6 sheets. Sheets for representatives who didn't meet the threshold were removed automatically.
Result

You now have a workbook with 6 sheets, one for each sales representative who sold at least 1,000 products in 2018. Each sheet contains the same data extraction layout (product, date, amount, customer details) filtered to that representative's data. Changing the year on the Filter sheet and refreshing updates all sheets automatically.