Pipeline

This report was designed to give insight into the current and historical pipeline for your company. My hope is that this report will act as a starting point to build out a Pipeline report that works best for your companies work flow.

Download
Report Demo

Dataset

Before opening the template, please ensure that you have prepared either an Excel worksheet, a Microsoft SQL (MSSQL) Custom View, or a PostgreSQL Custom View to serve as the dataset, depending on the template you intend to use. Verify that the fields are named precisely as listed in the 'Field Name' column below. If Excel is your dataset, confirm that the sheet containing the relevant data is named Data.

Field Information

All these fields must be part of the dataset, if one of the fields listed is not something that is currently in your LOS then it is alright to use the same data point for the column. Example, if you don't have a field for your Branch name, then it would be ok to use a field like Branch Cost Center as the Branch data.

Field Name	Field Description
Application Date	The date that a loan is considered an application
Branch	Field used for the name of the Branch
Clear To Close	The date the loan was clear to close
Closer	Closer's name
Decisioned Date	The date that the loan was decisioned
Extension Amount	The lock extension amount for the loan
Extension Percentage	The lock extension percentage amount for the loan
File Started Date	The date that the loan file was started
GUID	Unique identifier on a per loan bases
Loan Number	The loans loan number
Loan Officer	Loan Officer's name
Loan Purpose	The purpose of the loan such as Purchase, Refi, etc...
Loan Type	The type of loan such as Conventional, FHA, etc...
Lock Date	The date that the loan was locked
Processor	Processor's name
Property Type	The property type of the loan such as Primary, Investor, etc...
Sent to Processing	The date the loan was sent to processing
Submitted to Underwriting	The date the loan was submitted to underwriting
Total Loan Amount	The full loan amount
Underwriter	Underwriter's name

These are the only required fields for the template to work correctly. If you want to expand your report to include other data points then you can add those fields in as long as they are not named the same as one of the columns above.

Note

This dataset uses many of the same filed as the other templates. It is possible to point the other templates at the same dataset to minimize the number of reports that need to be exported out of the LOS.

Using a template

Using a Excel dataset

Once you have your Excel file ready to go with the correct column headers and sheet name open the template_scorecards.pbit and you will be prompted for the file path to your Excel file. This file path must be the exact full path to the file. Example is if I have my Excel file saved to my desktop as LOS_Data.xlsx then the path I would type in is C:\Users\Jody\Desktop\LOS_Data.xlsx

file_path

Once the file path is typed in click load, your data will be imported and the report will open. From here you can customize the report by swapping out fields in visuals or possible changing measures or custom fields to better suit your companies work flow.

Using a Microsoft SQL dataset

Once you have your MSSQL custom view built out with the correct column names, open the template_pipeline_sql.pbit and you will be prompted for 3 pieces of information:

SQL_Server: This will be the IP address or name of the SQL server that houses the database with your LOS data.
SQL_DB: This is the name the database that holds the custom view we will be using for the template.
Custom_View: This is the name of the custom view that contains the data needed for the template.

template_pipeline_sql_params

Note

If you need to add a port number to the end of your SQL server address, simply add a colon to the end of your server IP or name followed by the port number, example would be: MY-SQL-SERVER:1234, where :1234 is the port number.

Once this information is loaded, you may be prompted for a user name and password for the SQL server, please work with your SQL server administrator to get the correct credentials. Once everything is connected up the report will open allowing you to customize the report to your companies needs.

Using a PostgreSQL dataset

Once you have your PostgreSQL custom view built out with the correct column names, open the template_pipeline_postgresql.pbit and you will be prompted for 4 pieces of information:

PostgreSQL_Server: This will be the IP address or name of the PostgreSQL server that houses the database with your LOS data.
PostgreSQL_DB: This is the name the database that holds the schema and data we will be using for the template.
PostgesSQL_Schema: This is the name of the Schema that holds the custom views and data that will be used for the tempalte.
Custom_View: This is the name of the custom view that contains the data needed for the template.

template_pipeline_postgresql_params

Note

If you need to add a port number to the end of your PostgreSQL server address, simply add a colon to the end of your server IP or name followed by the port number, example would be: MY-POSTGRESQL-SERVER:1234, where :1234 is the port number.

Once this information is loaded, you may be prompted for a user name and password for the PostgreSQL server, please work with your server administrator to get the correct credentials. Once everything is connected up the report will open allowing you to customize the report to your companies needs.

Report Pages

Pages on this report are intended to provide insight into the current and historical pipeline for your branch and users. Pages in this report contain measures that provide insight into certain stages of the loan process. While your company may not have all these stages, it is my hope that this report can help you get started in building report that does follow your loan process by providing examples of how data is modeled and presented for use.

Started

This page is designed to provide information on files that have Start date in the date range selected. It also provides a look at same number of months back, as well as the same period last year. For example if the selected date range was from 1/1/2022 to 3/31/2022, then the Month Look Back date range would be from 10/1/2021 to 12/31/2021 and the Year Look Back would be 1/1/2021 to 3/31/2021. On the Loan Officer table the arrows can be used to tell if the Selected Range numbers are greater (green arrow), lesser (red arrow), or equal to (yellow bar) the average of the Month Look Back and Year Look Back numbers.

Note

I have also included the measures as part of the pbit file for each page for Selected Range vs Month Look Back and Selected Range vs Year Look Back if those numbers make more sense for your company to use.

The Loan Properties bar chart is designed to have the y axis driven off of the Loan Properties slicer to the left of it. For this page we can slice the properties for started loans by Loan Purpose, Loan Type and Property Type.

Applications

This page is designed to provide information on files that have Application date in the date range selected. It also provides a look at same number of months back, as well as the same period last year. For example if the selected date range was from 1/1/2022 to 3/31/2022, then the Month Look Back date range would be from 10/1/2021 to 12/31/2021 and the Year Look Back would be 1/1/2021 to 3/31/2021. On the Loan Officer table the arrows can be used to tell if the Selected Range numbers are greater (green arrow), lesser (red arrow), or equal to (yellow bar) the average of the Month Look Back and Year Look Back numbers.

The Loan Properties bar chart is designed to have the y axis driven off of the Loan Properties slicer to the left of it. For this page we can slice the properties for loans with an Application date by Loan Purpose, Loan Type and Property Type.

Locked

This page is designed to provide information on files that have Lock date in the date range selected. It also provides a look at same number of months back, as well as the same period last year. For example if the selected date range was from 1/1/2022 to 3/31/2022, then the Month Look Back date range would be from 10/1/2021 to 12/31/2021 and the Year Look Back would be 1/1/2021 to 3/31/2021. On the Loan Officer table the arrows can be used to tell if the Selected Range numbers are greater (green arrow), lesser (red arrow), or equal to (yellow bar) the average of the Month Look Back and Year Look Back numbers.

The Loan Properties bar chart is designed to have the y axis driven off of the Loan Properties slicer to the left of it. For this page we can slice the properties for loans with an Lock date by Loan Purpose, Loan Type, Property Type, and Lock Extension.

Processing

This page is designed to provide information on files that have Sent to Processing date in the date range selected. It also provides a look at same number of months back, as well as the same period last year. For example if the selected date range was from 1/1/2022 to 3/31/2022, then the Month Look Back date range would be from 10/1/2021 to 12/31/2021 and the Year Look Back would be 1/1/2021 to 3/31/2021. On the Processor table the arrows can be used to tell if the Selected Range numbers are greater (green arrow), lesser (red arrow), or equal to (yellow bar) the average of the Month Look Back and Year Look Back numbers.

The Loan Properties bar chart is designed to have the y axis driven off of the Loan Properties slicer to the left of it. For this page we can slice the properties for loans with an Sent to Processing date by Loan Purpose, Loan Type and Property Type.

Underwriting

This page is designed to provide information on files that have Submitted to Underwriting date in the date range selected. It also provides a look at same number of months back, as well as the same period last year. For example if the selected date range was from 1/1/2022 to 3/31/2022, then the Month Look Back date range would be from 10/1/2021 to 12/31/2021 and the Year Look Back would be 1/1/2021 to 3/31/2021. On the Underwriter table the arrows can be used to tell if the Selected Range numbers are greater (green arrow), lesser (red arrow), or equal to (yellow bar) the average of the Month Look Back and Year Look Back numbers.

The Loan Properties bar chart is designed to have the y axis driven off of the Loan Properties slicer to the left of it. For this page we can slice the properties for loans with an Submitted to Underwriting date by Loan Purpose, Loan Type and Property Type.

Closing

This page is designed to provide information on files that have Clear To Close date in the date range selected. It also provides a look at same number of months back, as well as the same period last year. For example if the selected date range was from 1/1/2022 to 3/31/2022, then the Month Look Back date range would be from 10/1/2021 to 12/31/2021 and the Year Look Back would be 1/1/2021 to 3/31/2021. On the Closer table the arrows can be used to tell if the Selected Range numbers are greater (green arrow), lesser (red arrow), or equal to (yellow bar) the average of the Month Look Back and Year Look Back numbers.

The Loan Properties bar chart is designed to have the y axis driven off of the Loan Properties slicer to the left of it. For this page we can slice the properties for loans with an Clear To Close date by Loan Purpose, Loan Type and Property Type.

Download

Report Demo

Click Here to open the report in a new page.