Overview
You can filter your Reports to see just the data that you are interested in. Some Reports are affected by System Filters. You can also add filters to Reports themselves. They can be simple with just one or two filters, or they can be complex with several filters and/or groups of filters. Date ranges can be given relative filters. For example, you could have a relative filter on the Order Date to get a Report of all Orders in the placed in the last week. Relative filters work well with Scheduled Reports. That way, you can automatically be delivered a weekly Report of all Orders placed in the last week.
Getting Started
Login to the Administrative Console of your database and click on the Reports link. Then, click on the Edit Fields icon on a Report.
You will see a Filter section. Many Reports already have a Filter setup, such as the Orders Report. That Report has a filter on the Order Placed Date. The range on the filter is BETWEEN. There are two fields to select and use a calendar to choose the first date and the last date of the filter. Below, I am filtering out all Orders that were not placed between October 1st and October 31st. I then clicked the "Save and Run" button to go to the Run Report screen.
The date range values that were selected on the previous screen are on the Run Screen. I can simply click the Run or Download buttons to get a filtered Report.
"Ask Me" Filters
If a filter has the "Ask Me" box checked, then the filter can be adjusted on the Run screen. Below, I changed them to return all Orders placed in September.
Relative Filters
Date filters can be relative. As opposed to having a fixed range, like in the examples above, it will be a rolling range. Return to the Filters section of the Edit Fields screen. Check the Relative box. You can now input relative values. In the example below, I am asking for all Orders placed between 7 days and 0 days relative to when the Report is run. This will return all of the Orders placed in the last week.
System Filters
System Filters effect all Reports that have a Region, Category, or Report Group (Season) field. The Products Report is an example of this. Click the Edit Fields icon on the Products Report. Open the System Filters menu, select a Region, Category, and/or Season, and click the Apply button.
System Filters and Report Filters
If you go to add Report Filters after adding System Filters, the values that you can select for Report Filters are constrained by the System Filters. For example, I have a System Filter of the Spokane Region. When I go to add a Region Report filter, I can only choose the Spokane Region, or any Region below it.
Also, when I go to add a Product ID Filter, the list of Products that I can filter on are constrained by the System Filter.
System Filters and Report Data
Adding a System Filter can also filter-out data from Reports. For example, if I have a Roster Report and a System Filter of the Spokane Region, and if I run the Report, then I will only get the Roster of Products that are in or below the Spokane Region.
Multiple Report Filters
You can filter on a single field or on multiple fields. Some Reports are pre-seeded with multiple filters. An example of this is this the Roster Report. It has a Product ID filter and an "Is Active" filter.
Running the Report witouth making any changes to the filters will return the Rosters of all active Products. Running the Report with a value in the Product ID field will return the Roster of just that Product, as long as the Product is Active. This is because the filters are set to AND instead of OR.
Multiple Filters with AND Matching
Filters can do AND matching or they can do OR matching. If it is set to AND, then both filters must have matches.
Below I have a Roster Report with a Product Start Date filter of greater than 11/20/2017. It also has an "Is Active" that is set to True. Since the filters are set to AND, then the Product must start after 11/20/2017 AND the Product must be Active for their Rosters to be part of the Report.
Multiple Filters with OR Matching
If Filters are set to OR matching, only one must have a match.
Below I have a Orders Report with an Order Placed Date filter of between 7 and 0 days ago. The Report also has a filter of Order Unpaid Balance equals "True." The filters are set to OR matching, so this Report will contain Orders placed in the last week OR Order with an unpaid balance.
Filter Groups
Filters can be grouped together for more advanced needs. The same principles of OR matching and AND matching are used. Within each group there can be OR or AND matching, and between the groups there can be OR or AND matching.
Below I have an Orders Report with two filter groups. The first group has filters targeting Orders placed in the last week AND that have an unpaid balance. The second filter group has filters targeting Orders placed in the last week AND that have a special requirement. The two groups have an OR match. The end result is that the Report will contain all Orders placed within the last week that have an Unpaid Balance OR a special requirement.
One of the benefits from Filter Groups is being able to use the same filter multiple times in a single report. This is seen above with the Order Placed Date filter in both groups.
Please note that filter groups are complicated and they must be carefully constructed. Feel free to tinker and test, and if you have any questions, please do not hesitate to contact us.
