When you create reports using ARW, you may simply pull data on all records. For example, all sales for all customers. However, it may be more useful to filter that data based on specific criteria, such as all sales greater than zero, or all customers with a status of active.
- Create a simple ARW report as described in Sample ARW: Simple customer YTD Sales Report.
- From Available Fields, click and drag the specific data on which you want to filter the results.
- To complete the expression, assign the Operator and Value to that criteria.
- Choose an Operator, such as Equal to, from the drop down list.
- Then click the magnifying glass and select the value or type a Value into that cell. For example,
- For more information, see More about operators
- If you are choosing more than one criterion, then you must select a Connector. The connector expresses the relationship between the two criteria, most commonly AND or OR. Remember that AND means both conditions must be met for a record to be returned, and is therefore more exclusive; OR means either condition may be met, and therefore will return more records.
For more information, see More about connectors
- You can now add more expressions to build out your criteria. Note that when you add a field to the Criteria list, you can drop it anywhere, making it first or last in the evaluation of the expression. The order of the criteria could have a significant impact on the evaluation of the expression. After you drop the criterion, you will not be able to move its placement. If you find you need to evaluate it earlier or later in the expression, you must delete and re-add it.
Tip on Dates: In addition to specifying a set date, such as 03/01/2019, dates offer flexible options for values that are relative, including Today, Yesterday, Tomorrow. Previous Business Day, Next Business Day, This Week, and Last Week. You can also specify data from the current month by manually inputting <THIS MONTH> into the value field. These relative dates are very useful for scheduled reports. Note that you can also specify a fixed number of days, such as the last 45 days or the next 30 days, by typing <LAST 45 DAYS> or <NEXT 30 DAYS>. You can use any number, such as <LAST 365 DAYS> for the last year. Other date options include: <PRIOR TO # DAYS AGO> <PERIOD YEAR> <LAST # PERIODS>
Tip on Date Ranges: To add a date range, add the date criteria, such as Order Date, twice, once for the beginning date and once for the end. For example, to return all January invoices, choose
Order Date Greater Than or Equal To 01/01/19
Order Date Less Than or Equal To 01/31/19.Tip on Comparative Data: To compare two criteria, such as Last Year Sales and This Year Sales, add the field Last Year Sales. and then choose your comparison operator, such as Greater Then. Finally, choose the data you want to compare to the first, such as YTD Sales, and drag that to the Value cell.
Last Year sales (YTD) Greater Than YTD Sales
Tip on blank or Null values: You can filter on blank or null values, for example, Sales Orders with no Ship Date. Starting in the OH Primary File, se
Ship Date - Equal To with a value of <null>
Ship Date - Equal To with a value of ""
*A set of double quotes with nothing inside will always be interpreted as blank. - If you want to exclude one of the filter criteria rows you selected, select the Exclude check box. The expression will be removed from the results calculation. Note that Exclude does not function as NOT, rather, it ignores the statement altogether.
- If you want to remove an expression, click the X button at the end of the row. This selection removes the expression permanently from the report.
- When you have finished adding expressions, you should validate the results: click Save, and then click Run Report. Review the report to make sure that the filter criteria is working as you expected. If not, try different combinations of connectors and positions of criteria in the list until you get just the results you want.
See also