In this article, we will cover how to build a report to find your ten largest accounts based on direct bill commissions for the last fiscal year. This report can be helpful if you want to see who some of your most important customers might be so you can work on a VIP list. This can also help you find the total number of accounts that fall into specific direct bill commission brackets.


TABLE OF CONTENTS


Did you know? - You can click on the images in our articles to make them bigger.

Navigate to Commission Grouping


1. Hover over the Reports icon on the left navigation bar to open the Reports menu. 

2. Choose Commission under the Categories section. This will take you to the Commission Category Reports page. 

3. Choose the Commission Grouping card. This will take you to the Commission Grouping report.


Set Group Filter


First, let's adjust the Group By filter to group the report by Applicant (Customer Accounts).


1. Select "Producer" under Group By to open the dropdown.
2. Choose Applicant from the list. This will have the report group the data by Applicant also known as customer accounts.

Set Date Range Filters


Second, we'll set the date range filter to include only the last fiscal year.


1. Select "is in the last 30 days" under Commission Statement Date. This will open the filter.

2. Select "is in the last" to open the date type dropdown. This will let you customize the date filter.

3. Choose "is in range". This will let you choose a beginning date and an end date for the date range filter.





4. Select the first date box to open the date field. This is the date field that will determine the beginning of the date range filter.

5. Select the Calendar icon to open the Calendar date selector.

6. Using either the scroll bar or the Month/Year selector find the beginning of the last fiscal year.



7. Repeat steps 4-6 for the second date field which will determine the end of the date range filter.




8. Your date range filter should now encompass the last fiscal year. Now that we've adjusted all our filters we need to apply them to the report. To apply the changes, select the Update button. This will apply the filters and refresh the report. 

Sort the Columns


Finally, we need to sort the data we have so that the Total Commission column is in descending order. 


1. Using the side scroll bar on the bottom of the report, scroll to the side until you find the Total Commission column.

2. Hover over the Total Commission column header, and click on the Sort icon which is two vertical arrows facing each other. This will sort the column in descending order.




3. Using the side scroll bar, scroll back until you find the Applicant column header. Hover over the Applicant column header and select the Column Options icon which is three vertical dots. This will open the Column Options menu.

4. Choose Freeze. This will freeze the Applicant column so that when we scroll to the side to look for the Total Commission column again, we can still see the Applicant column.



This should provide you with a list of your accounts in order of most to least total commission for the last fiscal year. 

Aggregating Numbers


If you are looking to aggregate the data, you will need to complete this step in Excel. To download the report to Excel, please follow the steps below.


1. Select the Report Actions icon which is three vertical dots. 

2. Choose Download. This will open the Download window.

3. Change the Format dropdown to CSV. 

4. Select the Download button. This will download the report as a CSV to your computer where you can work on aggregating the data. 



Please Note - When downloading the data, the sorting functionality does not carry over to the CSV. You will want to Sort the Total Commission Column again once the data is in Excel. 

We hope this article was helpful in learning how to run a report to find your top accounts based on total direct bill commission. Please let us know if this article was helpful by answering the question below. Check out our related articles on the right-side of the page to learn more about EZLynx!