Simple Report Export
Reason
The goal of this Encompass standalone application was to provide an easy way to get started using the report templates provided on this site. I wanted create and easy to setup and use program that would create a Excel, with a few changes to some configuration files this application will create an Excel file with Encompass data ready to be used in any report.
Note
Always make sure to review and test any application you download before using in a production environment. BitBucket links for source code for this application will be provided later in this write up.
Source Code and Downloads
Source Code
A ReadMe file with full dependencies can be found using the below BitBucket link below:
To download a zip copy of the source code to review or compile please use the link below:
Setup
Make sure that you compile a copy of this application before trying to run it as there are configuration files that must be edited before the application will work properly.
Dependencies
To compile a copy of this application you will need a computer that is running the Encompass SDK. Included in the download folder is a "support dlls" folder which contains the ConfigManger dll that is used to encrypt the appSettings part of the application config file, so that no username or passwords a stored in plain text. For the other dependencies that are not Encompass or ConfigManger please use Nuget Manager to download the rest of the packages needed to compile.
Note
Links for each Nuget package can be found in the ReadMe file located on BitBucket using the link above.
Configuration Files
Once the application is compiled there are two configuration files that must be edit before it will work properly. The Simple_Report_Export.exe.Config file (you may need to show file extension on your computer to find this file) has the appSettings and OutputSettings sections which are used to tell the application information it will need to generate the report. The report_settings.json file provides the fields from Encompass to pull as part of the data, along with the information on how build the report criteria.
Simple_Report_Export.exe.Config
This file contains important information needed for the application to login to Encompass, as well settings for where to output the generating the excel file. Example of what this file will look like is:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="OutputSettings" type="System.Configuration.NameValueSectionHandler" />
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
<runtime>
<NetFx40_LegacySecurityPolicy enabled="true"/>
</runtime>
<appSettings>
<add key="encServer" value="https://BE123456.ea.elliemae.net/Encompass$BE123456"/>
<add key="encUsername" value="Encompass Username"/>
<add key="encPassword" value="Encaompss Password"/>
</appSettings>
<OutputSettings>
<add key="Path" value ="C:\Report_Data\" />
<add key="FileName" value ="LOS_Data.xlsx" />
</OutputSettings>
</configuration>
appSettings
This section of the configuration file handles who and where this application will log into. On first run the application will encrypt this section of the configuration file so that no the Encompass information used to create an Encompass session is saved as plain text.
Note
When first running the application it may appear as if the application didn't do anything, this is when the encryption is happening. Running the application again with a encrypted configuration file will result in the application running completely if all other setting are correct.
OutputSettings
This section of the configuration file handles where and what the exported Excel file will be. The Path is the full folder path to where the Excel file should be saved. You must include the tailing "\" to make sure that the correct folder is used.
The FileName path is what the Excel file will be called on export. The ".xlsx" extension must included for the file to save correctly.
report_settings.json
This file contains the information needed on what fields to use when pulling data from Encompass, along with information on how to build the report criteria. The two section in this file are the Fields section and the QuerySettings section.
Fields Section
The fields json object allows you to provide a description which will be used later as the column title in Excel, along with the ID of the field in Encompass that you wish to pull into your report. Example of the Fields section is:
{
"Fields": [
{
"ID": "364",
"Desc": "Loan Number"
},
{
"ID": "2",
"Desc": "Total Loan Amount"
},
{
"ID": "GUID",
"Desc": "GUID"
},
{
"ID": "LOANFOLDER",
"Desc": "Loan Folder"
}
]
}
Note
Please note that any fields you use must be part of the Encompass Reporting Data Base for the Excel file to generate.
Tip
The default description in the report_settings.json that is available on BitBucket match the ones needed for the Power BI templates on this site. Once you have updated the ID field to what makes sense from your system, the exported file will work for all current templates.
QuerySettings Section
Settings in this section define what query criterion will be used when the Excel document is generated. There are two properties that must be in this section, those being the StartDate and the ExcludedFolders.
The StartDate can be a empty string (to use an empty string please input "" for the value of StartDate), if you with so pull all loans from the loan origination system. When the StartDate property is set (must be in the format "01/01/2018") all loans with a File Started Milestone date on or after that date will be pulled into the Excel file.
Tip
Limiting the amount of data pulled into this Excel fill will make the export happen quicker. This will also limit the data you have to work with in Power Bi when imported, make sure to set the StartDate to a comfortable number years that works for what your reporting needs are.
The ExcludedFolders list must have at least have one folder listed in it. Loans in the folders listed will not be pulled into the Excel file.
And example of the QuerySettings section is:
"QuerySettings": {
"StartDate": "01/01/2018",
"ExcludedFolders": [
"(Trash)",
"Testing Training",
"(Archive)"
]
}
Tip
Not all data in Encompass is useful in reports and it is easier just to not pull in that data instead of trying to model around it in Power BI. If you have a Testing and Training folder, it is a pretty good bet that loans in that folder will not be needed for reports and is safe to exclude from being exported to Excel.
Example report_settings.json
Below is a example of a full report_settings.json file:
{
"Fields": [
{
"ID": "GUID",
"Desc": "GUID"
},
{
"ID": "364",
"Desc": "Loan Number"
},
{
"ID": "LOANFOLDER",
"Desc": "Loan Folder"
}
],
"QuerySettings": {
"StartDate": "01/01/2018",
"ExcludedFolders": [
"(Trash)",
"Testing_Training",
"(Archive)"
]
}
}
First Run
Once the application has been compiled and the configuration files have been updated, double clicking on the Simple_Report.Export.exe. On first run it will encrypt the appSettings section of the Simple_Report_Export.exe.Config file. Any run after that will use the encrypted appSettings to create a connection with Encompass and try and generate an Excel file with the fields proved based on the criteria in the report_settings.json file.
Troubleshooting
In the same directory as where the Simple_Report_Export.exe application is, a simple_report_export.log file will be generated. This log file will include information each time the application is run allow for you to see if any errors occurred and troubleshoot as needed.
Need Help
If any help is needed please feel free to reach out to me at jody@simstech.dev and I will try to assist in any way I can. Thank you!