Lightspeed O-Series is a sophisticated and comprehensive cloud-based platform. For many, the built-in reports and analysis that Lightspeed O-Series offers is enough for our day-to-day needs. However, if you need something a little more customized, you need to aggregate your data over multiple Lightspeed O-Series accounts, or you need to cross-reference your Lightspeed O-Series data with other cloud services, then you’re going to have to build it yourself. And what better way than using the tool you’re already familiar with - Microsoft Excel.
In this article, I’ll take you through the steps to connect Excel to your Lightspeed O-Series data, and get started customizing the reports you need.
Connect to Lightspeed O-Series
If you don’t already have a SyncHub account, you can grab a free trial here - go on, I’ll wait - it only takes a minute and you can cancel as soon as you’ve finished this tutorial if you like.
Once connected, you'll find data from all these endpoints streaming down from Lightspeed O-Series, such as this example from the Order table:
| CreatedAt | SaleNumber | Total | Paid | SiteID | StaffMemberID | CustomerID |
|---|---|---|---|---|---|---|
| 2024-12-12 00:00:00 | 1339-09 | 13.40 | 13.40 | 34 | 14 | 564 |
| 2024-05-23 00:00:00 | 1344-01 | 50.30 | 50.30 | 55 | 5 | 51 |
| 2024-12-30 00:00:00 | OFFSITE | 3.99 | 3.99 | 12 | 2 | 12455 |
After connecting, visit your SyncHub Dashboard and grab your new database credentials - you'll need them below.
Reading your Lightspeed O-Series data from Microsoft Excel
We now just need to tell Excel where to find your data. Select a new SQL Server connection from the Data ribbon:
When prompted, enter the Server and Database that SyncHub provided in the first step above:
Next - and this is the part that trips most people up - switch to the Database credentials tab (highlighted by the arrow below), then enter the username and password provided to you by SyncHub in the step above:
Don’t worry about which level to apply the settings to - database or server - it does not matter to SyncHub.
Troubleshooting
This is the point where you are most likely to run into issues - your login won’t be accepted. After triple-checking that you have used the correct server/database/username/password, the most common resolutions we see are:
- try opening Excel in Administrator mode
- if you are in an office, perhaps you have some firewall restrictions?
All going well, Excel will now present you with the list of tables from Lightspeed O-Series, so simply select the data that you’d like to report on. Excel keeps things really simple by adding each “table” from SyncHub as a new tab.
Keeping your data up-to-date
SyncHub updates it’s staged data from Lightspeed O-Series in near-realtime, but unfortunately Excel does not have a similar feature. But not to worry - the solution isn’t too onerous. Every time you want to refresh your data, simply click the (appropriately named) Refresh button in your Ribbon:
Beyond Lightspeed O-Series
Lightspeed O-Series-specific reports are essential, but the true power of SyncHub comes when you augment your Lightspeed O-Series data with additional information:
- SyncHub allows you to pull in data from multiple Lightspeed O-Series accounts, and compare/report/aggregate from within the same report. See this blog article for details on aggregating your Lightspeed O-Series data.
- Most businesses use multiple cloud platforms. SyncHub provides connectors to a wide range of popular cloud platforms. Imagine the insights you could gather by consolidating this information into a single dashboard (or see this case study for real-world examples).
So what are you waiting for? Grab a free trial of SyncHub here and see what you can do. In ten minutes from now, you could be reporting against your Lightspeed O-Series data and taking your first steps towards a data-driven business.