It is recommended that the user understands the basics of report filtering as a prerequisite for this advanced article. This article will go into more detailed concepts in regards to filtering logic to create complex and highly specific filters in the reporting area. Please remember that you can send a support ticket at https://ventures.happyfox.com/new/ if you have additional questions.

**KEY CONCEPT**:   "OR" Statements and Filter group layers (nested statements)

In our KB article regarding the basics of report filtering, all of the examples shown are considered single layer filters. That is, each filter has only one set of criteria to choose from. The default setting for the Filters area is to use "AND" statements to join up multiple single layer filters to create an increasingly narrow set of results. However, there will be some times where you wish to use conditional filtering based on specific types of data. These types of statements can be like this: "I want the report to show if X conditions applies or if Y condition applies to the project." Instead of having all conditions apply in basic filtering, now you are asking the report to look at two different criteria and show if either one applies or if both apply. These are considered "OR" statements. These can be used just like the AND statements that the reports default to but when you combine both AND and OR statements, you add additional layers of filtering and exponentially increase the complexity of the filter. These filters can be very powerful to pull specific types of data. 

To understand how mixing AND and OR statements can increase the filter complexity, we can use an example of a request someone may have.

SCENARIO: A portfolio manager would like to audit their Servicing records in regards to tracking Insurance for all Funded loans. An issue was found during routine servicing and the manager would like to audit the portfolio to make sure  that all Insurance records have an assigned Insurance Company and an Insurance Agent. Only records that are missing an Agent or both an Agent and a Company should appear on the report. In addition, all assigned Agents must have an email address for sending correspondence.

Before you begin putting your filters together, we would recommend that the request be broken down into as many parts as possible so that each can be addressed within reports. So here is what we know so far about the request:

• This request comes during the Servicing phase and deals with the Insurance Provided By section of Tracking > Insurance. This means the report should only show loans that are in the Funded status.

• This request wants to show all Insurance records where both the Company and Agent are blank.

• This request also wants to show all Insurance records where there is a Company assigned but no Agent has been assigned.

• If there is an Agent assigned, that Agent MUST have an email address associated with them.

• The system does not allow for the Agent to be assigned if the Company is left blank so we do not need to filter for this.

How to Build this filter using filter groups

The reason we know that we will need to create a filter group for this report is because the request has at least two different scenarios regarding the assigned Insurance Provided fields.

• The first is if there is no Company or Agent assigned; 

• The second is if there is a Company assigned but no Agent has been assigned;  and

• Finally, the third situation is when both an Company and Agent have been assigned but the Agent does not have an email address associated with it.

By default, the Reports area is set up to use only AND statements and so each line of filters means that all things must apply instead of the situational criteria we are currently looking to add. These are going to be OR statements which will be placed in a filter group so that each criteria is looked at separately. If we add them in as AND statements, your report would show nothing as there would be no possible way for an insurance tracking item to have all three of the criteria at the same time.

1. The first Filter that we need is the easiest filter. We are only looking at Funded loans. You can use either a Loan Status Parameter to achieve this or you can manually add this into Filters. The choice is yours.

   

   
   How to read this filter: The Loan Status must be "Funded".

2. Next we have to create a Filter group to account for all of the scenarios regarding Company and Agents. To do this, you will need to use the group conditions icon. When you add the group, notice that the heading of the group says "any of the following apply". This is how you know that you are using OR statements versus AND statements.

   

   
   How to read this filter: Any condition in this filter group may apply and should show up on this report.

3. The first situation is when both the Company and Agent are blank. Remember that in Ventures, you cannot assign an Agent if the Company field is blank so this first statement is relatively easy. The first condition in this group is that the Company is missing (null).

   

   
   How to read this filter: Any insurance tracking record where the Insurance Company is blank should show up on the report.

4. Each Filter Group acts like it's own Filter tab and will have it's own unique buttons for adding more conditions or even adding another level of grouping within this Filter group. So we need to create another filter group inside our first filter group and have to click on the correct button to create it. Putting a group of filters inside another group is generally called "nesting". Our next condition is a set of two criteria which means we will need to add yet another Filter group so we need to click on the group icon to add another group.

   

   
   How to read this filter: I want to create another filter group inside the first one since I know the next condition requires two different criteria.

5. Notice how this third layer of filtering has switched back to asking that "all of the following apply". You can click on the words "all" or "any" if you wish to change this to fit the condition you are looking at. So let's go back and remember what this next situation is. This is the situation where a Company has been assigned but the Agent has not been assigned. This means that both of these have to apply and we will need to add two conditions to this group.

   

   
   How to read this filter: Any insurance tracking record where the Insurance Company has been assigned (is not null) but no Agent has been assigned (is null) should appear on this report.

6. Now we need to add the third situation that could happen. This is the case when both the Company and Agent have been assigned but there is no email address associated with the Agent. So this group will have 3 conditions that apply to it. It is very important that you continue to keep track of which layer you are adding this next group to. You are not creating a 4th layer of filtering but adding another third layer group.

   

   
    

   

   
   How to read this filter: Any insurance tracking record where both the Insurance Company and Agent are assigned (is not null) but where the Agent doesn't have an email address (is null) should show up on the report.

7. Your completed Filter should now look like the image below. This filter has three layers of conditions and uses Filter groups to achieve this. There are two conditions at the top layer of the filter for the Loan Status and for the Filter group (A). This Filter group (A), is split into 3 different conditions in a second layer of filters: Insurance Company, Filter group (B) and Filter group (C). Filter groups B and C are further split into the third layer of filtering.

   

   
   How to read the fully completed filter:
   In this report, show me the projects and insurance records where:

   1. The loan is in the Funded status
      AND also look at the Tracking Insurance area to check if ANY of the following occur on any Insurance records:

   2. The Insurance Company has not been assigned
      OR

   3. The Insurance Company has been assigned but no Agent was assigned
      OR

   4. The Insurance Company and Agent have been assigned but the Agent does not have an email address.

As you can see, what appears to be a "simple" request may require a very complex filter because of all of the conditions that apply. We encourage you to try your best with creating filters as that is a great way to learn the system but remember that you can reach out to us for assistance with report filters that you are struggling to get working.