IDEA Tech Tip: Using Filters in the Print Report Reader
A: The Filter option in the Print Report Reader is definitely an underused feature. It does exactly what it sounds like: it allows you to filter your file on certain items, or if it is a numeric field you can filter it based on a range. Let’s use the Travel_report.prn file that can be found in the Tutorial folder to demonstrate how this works.
I have brought the file into the Report Reader and I have created a layer.
The Filters can be found as the last item in the Field Details.
As you can see, there are two options: the first allows you to include or exclude certain items, and the other allows you to select a range.
I will use the Town field to illustrate how to use the include/exclude filter. Here is an example of some of the cities that are available in this field:
Now suppose we are only really interested in Dallas and New York for our import. I would make sure that the Airlines Field Details is selected and under Filters click the ellipsis (…).
This will bring up a dialog to either include records with the following data or exclude records. In this case I want to include Dallas and New York, so next to the Add button I would first add DALLAS (it must be an exact match).
Then I click OK. I would do the same thing with New York and click Add.
Once I have listed all the items that I am interested in, I would click OK. Now I would only see items for New York and Dallas.
To make any corrections, I just load the dialog for that field, select the item I want to remove, and click on Delete or Add Additional Items.
Now using this same example, I can exclude Miami. First I will remove Dallas and New York using the Delete button. I then click on the Exclude Record with the Following Data item and enter MIAMI.
Once done I can click on Add and then OK. Now your list will have all items except for Miami.
Next, I can add on a range filter, which only works for amount fields. So above we have an Amount field. Let’s now only look at items between $400 and $1,000. To do this, make sure the Amount field is selected and then click on the Range ellipsis.
This brings up a dialog where you can enter a from/to range. The drop-down next to the From and To hold either a >, >= for the From and an <, <= for the To. For this example we can enter 400 for the From and 1,000 for the To. I have also selected the >= and the <= so the range will include 400 and 1000.
I then select OK, and now the items selected only include amounts between 400 and 1000.
To clear the range, just open the Range Filter dialog and select the Delete button.
– Brian Element, CPA, CIDA, CISE, CFE – IDEA Certified Instructor and Financial Advisor at Public Works and Government Services Canada
For more IDEA tech tips or to ask your own IDEA questions, visit IDEAScripting.com.
About Alain Soublière:
Alain Soublière has many years of experience working with computer audit software. He worked in a senior management role as the IDEA Product Manager for many years before becoming Director of Product strategy for CaseWare Analytics and more recently the Chief Product Strategist.
Connect: Alain Soublière