By the end of this session, you will be able to extract Data using Reference File Fill, use the Filter window, add data items to the Output window, resequence and amend items in the Output window, apply an autoformat and save a Setup and Workbook file.
Discuss in forum
Overview

Reference File Fill creates a list of Reference File information, which can be used either as a standalone report or as the starting point for a report, which contains transaction data. Use Reference File Fill to extract SunSystems data from a range of data into an Excel worksheet. For example, you can generate a list of item records within a range of I1 codes where the Item Supplier has a ranking of 1 and the supplier code is within a range of accounts.
There are no formulas as it is not a direct link; therefore the function must be rerun if data in your SunSystems database changes.
In this topic you will create a list of asset records sorted by “Asset Category”, which is an analysis dimension attached to fixed asset records and Asset Name from the Assets Records table.
The data items that are output are Description from the F0 Asset Category table and Asset Code, Asset Name, Base Gross Value and Base Net Value from the Asset Records table.
You will filter using all Asset Codes from the Asset Records table, where the Asset Status in the Asset record table is blank, and the Asset Category code is 100 or 400.

First of all you should choose a Business Unit. Click on the Assist button to do this or enter the business unit code manually.
Click on the Assist button to choose a table and double-click AR – Fixed Assets from the Table menu list:
Click here to see the screenshot

You can use the Filter window to apply filters and refine the data to be extracted. To apply a filter you can do one of the following:

Click here to see the screenshot
Double-click on <ALL> from the “Asset code list”. This will add <ALL> to the From and To fields on the right hand side of this dialog box. Click OK.
You will now select the Asset Status in the Selection window and drag it to the Filter Window. Click on Asset Status from the Selection List. Click and Drag the Data Item to the Filter window.
Click here to see the screenshot

Double-click on AR\Asset Status to specify Filters for the Data Item Asset Status. Double-click on A-Active:

The next field you need to include in the filter is ASSET CATEGORY, so scroll down and fine a table “ASSET CATEGORY” double-click on it select “Analysis Code” data item and drag it to Filter Window. In the Filter Window double-click this item and from the list window select 100.

Now we need to select the same data item “Analysis Code” drag it to the Filter Window, double-click it and choose a code – 400:

You can make non-contiguous filter selections by dragging the data item from the Select List window multiple times. This will allow you to select additional ranges.