1138
Q: There are too many records in my report that don't apply to the report. How can I show only the records that apply?
Q: There are duplicate records in my report. Is there a way to filter the list to only show relevant records?
The Filters tab of the Reporting area can be an intimidating menu when you are first learning the basics of reports. This article will highlight the basic types of filters available for different types of report fields and provide some examples of how to set up basic filters.
Why do duplicate records happen?
The most common issue that users encounter is that their custom report shows more records than expected or duplicate records. In actuality, there aren't duplicate records as each line has a unique set of data but this is often not what the user was intending to do. The main cause for this issue is the use of Report fields that are designed to pull more than one record per loan. For instance, the Report data group "Loan Entities" is configured to allow users to report on ALL Entities within the Ventures project. So if your project has 3 Entities, expect that the data from this group will show up three times, once for each Entity. Combined with other fields that do not show this behavior, it may appear that the report is duplicating the same loan 3 times when in fact each line is representative of each Entity.
When you combine multiple data groups with this behavior, you get multiplicative duplicates. For example, if your report uses both Loan Entities and Loan Collateral you may get even more records than you expect because both data groups are configured to look at all Entities and all Collateral. So if your loan has 3 Entities and 2 Collateral records, your report will show 6 total records (3 x 2 = 6). When building your report, it is important to choose the correct field from the correct data group and you will find that Ventures will split out specific cases for items like Entities (ex: "Loan Entity Primary OC") and Collateral (ex: "Loan Collateral Project Property").
However, there will be cases where the user must use these types of report fields for their report and minimize the number of duplicates that appear. This is where the use of Report Parameters and Filters will be needed.
Parameters
Reports have two types of filters. The first is the "Parameters" area of the Details tab. These are commonly used filters that the Ventures team have created for users to allow them to filter reports within the Results tab. Clicking in a blank area of the Parameter field will bring up a list of common data that is filtered in reports. Before attempting to create a custom filter, check to see if there is already a Parameter available in Reports.
Custom Filters
If there are no parameters for the type of filtering you wish to do or the parameter does not provide you with enough specificity, then you may need to manually create a filter using the Filters tab.
- Click on [Add new condition] to first choose the field you will use for filtering.
- Select the condition to be used for this field.
- Select or enter in the value that the condition will be compared to.
There are four main types of data that can be filtered on the Filters tab of Reports and each has a different set of conditions that can be used.
Date Fields
- Report fields that are based on Dates have filters based on the date you want to use. In the image below, you can see a sample of the type of conditions available. Most of these are straightforward in their meaning while some are not as obvious.
- EXAMPLE DATE FILTER #1: In the image below, you can see how to use one of these date filters. This filter is set to show all records where the Funded Date comes before a certain date defined by the user.
How to read this filter: Show all records where the Funded Date is before 10/1/2019.
- EXAMPLE DATE FILTER #2: In this filter, the same Funded Date filter is now configured to use two dates and filter only those records that fall within this range. The condition used for this is "custom period".
How to read this filter: Show any record from a loan where the Funded Date is between 10/1/2018 and 10/1/2019.
- EXAMPLE DATE FILTER #3: In this example, the filter uses the concept of a "null" data field to determine which records show up.
How to read this filter: Only show the records if the Funded Date has NOT been entered in the project.
**KEY CONCEPT**: Using "null" values. In Reports, "null" means is the data there or is it not? "Is Null" means that the data is not present or no one has entered in the data while "Is not Null" means there is data present or someone has entered in that data. The use of the null conditions applies to ALL fields that can be filtered and is available as a condition for every field.
Logical Data Fields
- Logical Data fields are those that are YES/NO types of data within Ventures. This can be in the form of a radial selection like the Eligibility Questionnaire or it can be a checkbox selection like the Public Policy section of Eligibility. These fields have 4 options to choose from.
- EXAMPLE LOGICAL FILTER: In this filter, we are looking at the Public Policy for "Promise Zones" and seeing if it is checked in the Eligibility area.
How to read this filter: Show only the records for projects where the Promise Zone box has been checked in the Public Policy area of a 7a loan.
Amount or Numerical Fields
- Amount fields are numerical data and similar to the Dates, the conditions are based on the amount value that is entered in the project.
- EXAMPLE AMOUNT FILTER : This filter takes a look at the Project Total Cost Amount in the Financing area and only includes records in the report if it exceeds a certain amount.
How to read this filter: Include only the records where the Project Total Cost Amount is greater than $1,000,000.00.
Text or Drop-down Selection Fields
- The last type of data encompasses most fields that are in Ventures. These fields require the user to input text or it requires the user to select from a list of options available. These fields have many of the same conditions as the Amount Fields with a few differences.
- EXAMPLE FILTER #1: This filter looks at the Business Entity Type of the Primary OC and only wants those that are marked as Corporations.
How to read this filter: Include only the projects where the Primary OC is marked as "Corporation" under Business Entity Type.
- EXAMPLE FILTER #2: This filter is similar to the first example but instead uses a list of options to include multiple Business Entity Types.
How to read this filter: Include all the projects where the Primary OC is marked as either a "Corporation" or a "Partnership".
- EXAMPLE FILTER #3: This filter uses text as the search criteria. In this example, we are looking at the Notes comments and looking for a key word, "liquidation".
How to read this filter: Show every Note that has the word "liquidation" somewhere in the Notes comment.
As you begin to add additional filters, please be aware that every filter whittles down the list of records that matches all your criteria. You may end up using too many filters and the report will return no records. A general rule of thumb is to start with the broadest filter first and work your way down to the most specific filter last. An example of a broad filter would be showing all loans marked as Funded or all loans that are in the 7a Loan Type. These return many records. Then you can start using more specific fields to narrow that list down further such as only looking at loans where the Loan Purpose is "Working Capital".
EXAMPLE
How to read this filter: Show all records where the Funded Date is before 10/1/2019 AND the Loan Purpose is "Working Capital". For this report, BOTH conditions must apply to show up on this report.
Test your report every time you add a new filter to see if the most recent filter you added is one too many and the report shows no more records. If so, remove or disable some filters to check which one was one too many. You may need to use more advanced filtering techniques for more complexes searches.
