Microsoft Power BI Syspro® Template App On-Premises Connection

Connect Foresight SA's Profit and Loss Analytics template app to your on-premises SQL database. Power BI template apps from Microsoft AppSource are built around cloud data sources, so if your Syspro® SQL Server is on premises or hosted, the connection runs through the Microsoft on-premises data gateway instead.

This guide covers the whole process, from installing the app out of AppSource to a scheduled daily refresh running against your own data. The same pattern applies to any Power BI template app that needs a gateway.

Watch the full walkthrough

The video below covers every step in real time, including the details that are easy to miss: skipping the connection test on the licence connection, and why the app creates five connections you can safely ignore.

Jump to a section

Before you start

You'll need three things in place:

  • A Microsoft on-premises data gateway installed and showing as online. We recommend the standard gateway — it supports scheduled refresh, and it can be shared across users and reports. The personal mode gateway will also work, but it is tied to a single user account.
  • A Power BI Pro licence, and permission to create workspaces
  • Access details for your Syspro® SQL Server — either a SQL Server login or a Windows domain account

Check the gateway first. If it isn't online, open Windows Services and confirm the two gateway services are running before going any further — everything downstream depends on it.

Step 1: Install the app from AppSource

Find the app on Microsoft AppSource by searching for "Foresight SA" or "Syspro", then click Get it now. You can also install directly inside the Power BI Service: go to Apps → Get app → Template apps and search there.

Once installed, the app opens with demo data loaded. You'll see a Connect your data banner at the top of the report.

Don't click "Connect your data" for an on-premises setup. That path assumes a cloud-reachable database. For on-premises SQL Server, you configure the semantic model's parameters and gateway connections directly instead — which is what the rest of this guide covers.

Step 2: Set the dataset parameters

Go to your workspace, find the semantic model, and open Settings (or Scheduled refresh — both land in the same place). Expand the Parameters section and complete each one.

Parameter What to enter
01 — Fiscal year start The starting calendar month of your financial year
02 — SQL Server name Your on-premises SQL Server name or address
03 — SQL database Your Syspro® database / company name
04 — UDM file source type Leave exactly as supplied
05 — UDM SharePoint site URL Your SharePoint or OneDrive for Business site URL
06 — UDM file location The folder path portion of the file's URL
07 — UDM file name Leave exactly as supplied
08 — Licence key Your licence key, if purchased through our website

The UDM (User Defined Mapping) file is the Excel workbook that drives your P&L account groupings, sort order and subtotals. To get its URL, open the file in the SharePoint browser view and copy the complete address from there.

Click Apply when all eight parameters are filled in.

Step 3: Create the three gateway connections

Still in Settings, expand Gateway and cloud connections, find your gateway, and click the arrow to view its data sources. You'll see three data sources that need connections.

You'll also see about five connections that already exist. Power BI creates these automatically at install time from the app's default parameter values. Ignore them — leave them alone and create your own named connections instead. Adding a number to the end of each name (for example Licence Valid Connection 1) keeps them clearly separate.

Connection 1 — Licence validation

Click Add to gateway. Give it a unique name, then set:

  • Authentication method: Anonymous
  • Skip test connection: ticked — this one is required, the connection will fail to create without it
  • Privacy level: Organizational

Connection 2 — Syspro® SQL Server

The server name is already filled in from your parameters. Add your database name, then choose your authentication method:

  • Basic — for a SQL Server login (username and password)
  • Windows — for a domain account. The username must be prefixed with the domain: DOMAIN\username.

Set privacy level to Organizational and create it.

Connection 3 — SharePoint UDM file

The last connection points at the UDM workbook in SharePoint or OneDrive. Use OAuth2 authentication and sign in with your organisational credentials, then set privacy level to Organizational.

Step 4: Map the connections and apply

With all three created, go back to Gateway and cloud connections and map each data source to the connection you just made — the web URL to the licence connection, the SQL Server to your SQL connection, and SharePoint to the UDM connection. Then click Apply.

You can ignore the Cloud connections section entirely. Because SQL Server is routed through the gateway, Power BI wants the remaining sources handled the same way.

Step 5: Refresh and schedule

Run a manual refresh first. The initial one typically takes one to five minutes depending on how much history you're loading.

Once it succeeds, open Scheduled refresh, set your time zone, and configure the schedule — daily or weekly, with as many refresh times as you need. Turn on failure notifications so refresh problems reach you rather than sitting silently. Notifications go to the semantic model owner by default, and you can add other addresses.

See Microsoft's documentation on configuring scheduled refresh for more detail on refresh limits and behaviour.

Step 6: Update the app

Final step: click Update app in the workspace to apply and share the changes. The "you are viewing sample data" banner disappears, and the report now runs entirely on your own Syspro® data.

Report loads but the visuals are blank? Check the period slicer. If it's holding a saved selection that no longer matches your data's date range, the pages render empty even though the refresh succeeded and the data is there. Clearing the slicer fixes it.

Get the app

Useful Microsoft documentation

Syspro® is a registered trademark of Syspro Software Ltd. Foresight SA is an independent company and is not affiliated with, endorsed by, or sponsored by Syspro Software Ltd.

Back to blog

Leave a comment

Please note, comments need to be approved before they are published.