In order to further illustrate the use of the report filter features, the following is a step-by-step list of instructions on how to create a report filter. In this example, we will create a filter that will provide a list of all Aspire Technologies, Inc. products ordered in 2021.
From the Filter tab of the Report Properties window we will begin creating the report by creating the filter expression. To begin building your filter expression, select the [Field] button.
From the Add Filter Item window, you will set the parameters for this filter item:
First, select the table of the field that you would like to filter. Your choices from the drop down box are DocumentHeaders and Document Items.
The DocumentHeaders table includes fields that store information such as whether the document is a quote, invoice, order, the date of sale, the sales rep, etc. Anything specific to the document, but none of the line item information.
TheDocumentItems table includes fields that store information about all the items in the all the documents. This includes information such as product description, unit price, manufacturer, vendor, part numbers etc. Anything specific to the line items on the documents, but not the document.
Since we want to find all the Aspire Technologies, Inc. products ordered in 2021, let’s start with the “Aspire Technologies, Inc.” criteria.
Information about line items is stored in the DocumentItems table. Since we want to filter for line items in orders that have a manufacturer of “Aspire Technologies, Inc.”, we will select the DocumentItems table.
Next, we will select the Manufacturer field from the Field drop-down.
Then we will set the operator. The operator allows us to specify the relation between the field and the value that we are looking for. We want to find all line items whose Manufacturer value equals “Aspire Technologies, Inc.”, so we will select the select the Equal To operator from the drop down list.
Next, we want to enter the value that we are searching for. The Manufacturer value in the manufacturer field that we are looking for is Aspire Technologies, Inc. So, type Aspire Technologies, Inc. in the Value field.
Click on OK. The first parameter will now be listed on the filter tab under field names:
Here is a summary of this filter item. In our example, we are running a filter to see what Aspire Technologies, Inc. products have been ordered in 2021. Products sold, including Aspire Technologies, Inc. products, are stored in the DocumentItems table. We only want to find the records in the DocumentItems table that have Aspire Technologies, Inc. listed as the manufacturer. Therefore the logic for the filter is “Search the DOCUMENTITEMS (table) where the MANUFACTURER (field) is EQUAL TO (operator) Aspire Technologies, Inc. (value).
Between each filter parameter, there must be a logic phrase that identifies how we want to group these filter parameters together. In our example, we want to find records that have a manufacturer of Aspire Technologies, Inc. AND that were part of an order AND that were sold in 2021. So, for the filter items that will be in our filter expression, we will use the AND keyword to connect them together.
Click on the [AND] button for the connecting operator of AND. Your filter expressions should now look like the screenshot above.
Next click on the Add Filter Item button. Now we want to create the filter item that filters only orders.
Information about documents that is not line item based is stored in the DocumentHeaders table. Since we want to filter for documents that are orders instead of quotes or invoices, we will select the DocumentHeaders table:
Next, we will select the DocType field from the Field drop-down. The DocType field is where the document type is stored. This field will have a value of QUOTE, ORDER, or INVOICE. Note that for the DocType field, it is important that you use all capital letters for the value.
We will then set the operator. The operator allows us to specify the relation between the field and the value that we are looking for. We want to find all the documents where the DocType value equals “ORDER”, so we will select the Equal To operator from the drop down list.
Next we want to select the Value. The DocType that we are searching for is ORDER. Type ORDER in the Value box.
Click on OK. The DocType parameter will now be listed on the filter tab under field names:
Here is a summary of this filter item. The document type field is in the DocumentHeaders table. Therefore the logic is “Search the DOCUMENTHEADERS (table); where the DOCTYPE(document type) (field); is EQUAL TO (operator); ORDER(value).
The filter expression for the report that you have customized so far is shown in the screenshot above.
To filter by a date range such as a particular year, we need to create a filter item for both the beginning of the year and the end of the year.
Click on the [AND] button to add the connecting operator, as explained in an earlier step.
Next click on the Field button. We want to create the filter item that filters for the beginning of 2021.
Information about documents that is not line item based is stored in the DocumentHeaders table. Since we want to filter for documents that were created in 2021, we will select the DocumentHeaders table:
Next, we will select the DocDate field from the Field drop-down. The DocDate field is where the document date is stored.
Then we will set the operator. The operator allows us to specify the relation between the field and the value that we are looking for. We want to find all the documents where the DocDate value is greater than or equal to 1/1/2021, so we will select the Greater or Equal operator from the drop down list.
Next we want to select the Value. The DocDate value we are searching for is 1/1/2021, so type 1/1/2021 in the Value box.
|
Click on [OK}. This third parameter will now be listed on the filter tab under field names:
Here is a summary of this filter item. The logic is “Search the DOCUMENTHEADERS (table); where the DOCDATE(document date) (field); is GREATER OR EQUAL (operator); 01/01/2021(value).
We now need to add a filter item for the end date. Without specify this, the filter will return all orders from 2021 or later.
Click on the AND button to add the connecting operator, as explained in an earlier step.
Next click on the Field button. We want to create a filter item that filters for the end of 2021, so that the report will pull all the orders for 2021 and only 2021.
Information about documents that is not line item based is stored in the DocumentHeaders table, so we will again select the DocumentHeaders table:
Next, we will again select the DocDate field from the Field drop-down.
Then we will set the operator. The operator allows us to specify the relation between the field and the value that we are looking for. We want to find all the documents where the DocDate value is less than or equal to 12/31/2021, so we will select the Less or Equal operator from the drop down list.
Next we want to select the Value. The DocDate value we are searching for is 12/31/2021, so type 12/31/2021 in the Value box.
|
Click on [OK}. This fourth and final parameter will now be listed on the filter tab under field names:
The completed filter is now displayed in the screenshot above. For our example, we are running a filter to see what Aspire Technologies, Inc. products have been ordered in 2021.
Therefore, the complete logic is “Search the DOCUMENTITEMS (table) where the MANUFACTURER (field) is EQUAL TO (operator) Aspire Technologies, Inc. (value); AND (relation); Search the DOCUMENTHEADERS (table); where the DOCTYPE (field); is EQUAL TO (operator); ORDER (value); AND (relation); Search the DOCUMENTHEADERS (table); where the DOCDATE (field); is GREATER OR EQUAL (operator); 01/01/2021 (value); AND (relation); Search the DOCUMENTITEMS (table); where the DOCDATE (field); is LESS OR EQUAL (operator); 12/31/2021 (value).”
Once the filter items have been added to the list, they can be edited or deleted by selecting the item and clicking on the appropriate button.
Now that you have created the filter for the report, all you need to do is select a layout from the layout tab and the report is ready to be run. The default report layout file to use when creating a documents database report that includes both DocumentHeader and DocumentItems records, like our example above, is named itemhead.fpc. There are several other default layouts available for selection, all of which can be edited, and you can also create new layouts. Both creating new layouts and editing existing layouts are covered under Printing and Customizing Document Layouts.