Profit and Loss Analysis — Installation Guide
Installation Guide &
Connect to Your Data
Step-by-step instructions for installing the Power BI Template App from Microsoft AppSource and connecting it to your Syspro® data.
Step-by-Step Installation Guide
This guide covers the Out of the Box installation using an Azure SQL Server database and UDM.xlsx hosted on SharePoint or OneDrive — no gateway or VNET required.
Find the App on Microsoft AppSource
The Profit and Loss Analysis app is available as a Power BI Template App on Microsoft AppSource.
- Go to Microsoft AppSource or open Power BI Service (app.powerbi.com) → Apps → Get apps
- Search for "Profit and Loss Analysis for Syspro"
- Click Get it now and follow the prompts to install it into your Power BI workspace
Open the App and Start Connecting Your Data
After installation, the app opens with sample data. You will see a banner at the top of the report:
Click "Connect your data" to open the setup wizard.
Fill in Your Parameters
The setup wizard will display a parameters screen. Fill in all 8 required fields before clicking Next.
| # | Parameter | What to enter |
|---|---|---|
| 01 | Which Month Does the Fiscal/Fin Year Start | Enter the month number your financial year begins. 1 = January 3 = March 7 = July |
| 02 | Syspro SQL Server Name | Your SQL Server address. Azure SQL example: myserver.database.windows.net |
| 03 | SysproSQL Database | The name of your Syspro® database on the SQL Server. Example: SysproLive |
| 04 | UDM File Source Type | Select SharePoint or OneDrive for the Out of the Box option. See Section 2 for other options. |
| 05 | UDM SharePoint Site URL | The root URL of your OneDrive or SharePoint site — not the file URL. OneDrive example: https://company-my.sharepoint.com/personal/firstname_company_com Team site example: https://company.sharepoint.com/sites/Finance To find it: open OneDrive in your browser and copy the URL up to and including /personal/username |
| 06 | UDM File Location | The full URL to your UDM.xlsx file in OneDrive. To find it: open OneDrive → click UDM.xlsx to open it in the browser → copy the URL from the address bar. It will end in /UDM.xlsx?web=1 |
| 07 | UDM File Name | Must be exactly UDM.xlsx |
| 08 | License Key | Your licence key from your Foresight SA purchase confirmation email. |
Once all fields are filled in, click Next.
Connection 1 of 3 — Licence Validation
The first connection screen validates your licence key. No login is required.
| Setting | Value |
|---|---|
| Authentication method | Anonymous |
| Privacy level | Organizational |
Click Sign in and continue.
Connection 2 of 3 — Syspro® SQL Database
The second screen connects to your Syspro® SQL Server database. Use the SQL login credentials provided by your database administrator.
Choose the authentication method that matches your SQL Server setup:
🔑 Option A — Basic (SQL Login) Most Common
Use a dedicated SQL Server username and password. This works for Azure SQL Database and on-premises SQL Server (when using a gateway). Recommended for most Syspro® installations.
| Setting | Value |
|---|---|
| Authentication method | Basic |
| User name | Your SQL Server login username (e.g. powerbi_readonly) |
| Password | Your SQL Server login password |
| Privacy level | Organizational |
🪟 Option B — Windows Authentication
Uses a Windows / Active Directory account to authenticate. This is common in on-premises environments where SQL Server is joined to a Windows domain. Requires a Microsoft on-premises data gateway — see Section 2, Option 2A.
| Setting | Value |
|---|---|
| Authentication method | Windows |
| User name | Domain username (e.g. DOMAIN\username) |
| Password | Windows account password |
| Privacy level | Organizational |
☁️ Option C — OAuth2 / Microsoft Entra ID (Azure AD)
Use your Microsoft 365 or Azure AD account to authenticate directly to Azure SQL Database. No SQL login required — your Azure AD account must be granted access to the database by your administrator. Does not require a gateway for Azure SQL.
| Setting | Value |
|---|---|
| Authentication method | OAuth2 |
| Sign in | Sign in with your Microsoft 365 / Azure AD account |
| Privacy level | Organizational |
Click Sign in and continue once your credentials are entered.
Connection 3 of 3 — SharePoint / OneDrive (UDM File)
The third screen authenticates access to your UDM.xlsx file on SharePoint or OneDrive. Sign in with your Microsoft 365 account.
| Setting | Value |
|---|---|
| Authentication method | OAuth2 |
| Privacy level | Organizational |
Click Sign in and connect. You will be prompted to sign in with your Microsoft 365 account. Use the account that has access to the UDM.xlsx file.
Your Data Environment Options
The Profit and Loss Analysis app connects to two data sources: your Syspro® SQL Server database and a UDM.xlsx mapping file. The connection method depends on where these are hosted in your environment.
Azure SQL + SharePoint / OneDrive ✅ No Gateway Required
This is the Out of the Box configuration. Both data sources are cloud-hosted and connect directly to Power BI Service without any additional infrastructure.
- Syspro® database hosted on Azure SQL Database (Microsoft cloud)
- UDM.xlsx stored on SharePoint Online or OneDrive for Business
On-Premises or Hosted SQL + Local / Google Docs UDM ⚠️
If your Syspro® SQL Server is hosted on-premises, in a private data centre, or on a hosted/managed server — or if your UDM file is stored on a local network drive or Google Drive — you will need one of the following solutions to enable scheduled refresh in Power BI Service.
Option 2A — Microsoft On-Premises Data Gateway
🔌 What it is
The on-premises data gateway is a software bridge installed on a server in your network that allows Power BI Service to securely access data sources that are not publicly accessible on the internet — such as on-premises SQL Server instances or local file servers.
How it works
The gateway software is installed on a Windows server (or PC) within your network. Power BI Service communicates with the gateway via an outbound connection — no inbound firewall ports need to be opened. The gateway then accesses your local SQL Server or file share on behalf of Power BI.
- Requires a Windows server or PC that is always on when scheduled refresh runs
- Configured and managed in Power BI Service → Settings → Manage gateways
- Supports SQL Server, file shares, and many other local data sources
Option 2B — Microsoft VNET Data Gateway
☁️ What it is
The VNET (Virtual Network) data gateway is a fully managed, cloud-based alternative to the on-premises gateway. Instead of installing software on a local server, Microsoft manages the gateway infrastructure inside an Azure Virtual Network that is peered with your private network.
How it works
Your Azure subscription hosts the VNET gateway. It connects to your private data sources (SQL Server, file shares) via private network peering or private endpoints — without requiring a locally installed agent or always-on server.
- Requires an Azure subscription and a VNET connected to your data sources
- Requires Power BI Premium or Premium Per User licence
- No local server installation required — fully managed by Microsoft
- Better for organisations already using Azure networking
SQL Server Hosted on Amazon Web Services (AWS)
SQL Server instances hosted on AWS RDS or AWS EC2 can connect to Power BI Service, but require additional network configuration to allow access from Power BI's cloud infrastructure.
Connection options for AWS-hosted SQL Server
- Public endpoint (simplest): If your AWS RDS/EC2 instance has a publicly accessible endpoint and your firewall/security group allows inbound connections from Power BI Service IP ranges, a direct connection is possible. You will still need to add Power BI IP addresses to your AWS security group.
- On-premises data gateway: Install the Microsoft gateway on an EC2 instance within the same VPC as your RDS. This allows Power BI Service to connect via the gateway without exposing your database publicly.
- AWS PrivateLink + VNET: Advanced configuration linking AWS PrivateLink to an Azure VNET, enabling the VNET gateway to access RDS privately. Requires both AWS and Azure networking expertise.
Need Help with Your Installation?
Our team is available to assist with installation, data connections, and UDM setup.
We also offer custom BI development and consulting services.