Custom Reports for LightSpeed POS for Mac

OTR logoWhen none of the built in reports that come with LightSpeed do what you need, you may consider having a custom report built using Xsilva’s OTR Reporting Tool. OTR stands for ‘On-Target Reports’ which is a third-party SQL reporting tool that Xsilva has licensed and rebranded but – and this is important to know – they did not make it themselves.

In the early days of LightSpeed, a report tool was needed to mine data out of the LightSpeed database. With no time to create one from scratch, the developers of LightSpeed looked for an ‘off-the-shelf’ solution that would work with the code system and database that they used. They found OTR as a relatively quick and efficient solution despite it’s short comings and one of the features that they included was the ability to use the OTR Reporting Tool to add custom reports to LightSpeed. Once the custom report is added to your LightSpeed, they show up in the Reporting window just like the other reports and can be run on your data whenever you wish.

‘What do you need?’ and ‘Why do you need it?’

When considering a custom report, start with a discussion with yourself to determine the requirements of the report. Be sure to ask ‘why’ questions as often people will ask for reports that shouldn’t be asked for…. in which case, I’m referring to improper processes… ie: using LightSpeed incorrectly. Chances are, if you did things slightly differently within LightSpeed, you wouldn’t need the report at all. This is where my consulting comes in so be ready to book a session, if needed. During the session, I’ll help you nail down these requirements and possibly offer alternative solutions.

The ‘Mock Up’

Next, mock up the report in Excel. If you use your own terms, instead of standard LightSpeed terms, be sure to note this. ie: SKU = Product Code. We want to use your terms whenever possible but I need to make sure that I truly understand what it is that you’re asking for. For example, your ‘SKU’ number might be the manufacturer’s style number, and not the matrix-generated product code… in which case, that should be made clear.

The ‘Assessment’

Then, send the mock up to me. I’ll tell you if it can be done within LightSpeed using the OTR reporting tool… or if we’ll have to use the API. Either way, I’ll give you an estimate of time and cost.

How much will it cost?

There is no charge for the initial assessment, but a typical OTR report costs about $200 CDN. If an OTR report is not possible, a solution using the new API will be discussed. The costs involved in this may be substantially more, although, I’m working on a project that may bring that more in line with the cost of an OTR report.

Can it be done with OTR?

How can you tell if a report can be done in OTR? If you take a look at the existing reports within LightSpeed, you’ll see something in common with all of them. They are all just lists of information.

Some of the lists are ‘grouped’… this is usually signified by the word ‘by’… such as in Sales by Product… which lists all invoice line items and groups them by product.

Some of the lists are ‘summarized’… this is usually signified by the word ‘Summary’… such as in Sales Summary by Product… which hides the invoice line items and only shows one line per product. They are the same report, only one hides the details to show only a summary of the data.

Calculations are done within each line and not on the totals at the bottom. For example, a profit calculation is done on each line by deducting cost from sell. The cost, sell and profit columns may be summed at the bottom and each line could have a margin percentage on it, but you would not be able to calculate a grand total margin percentage on the summed totals at the bottom.

The other big limitation of OTR is that you can only ask one question per report. Such as “Show me the sales for today.” You could never ask, “Show me the sales for today AND yesterday and display them side by side.” … or… “Show me my sales by product AND also list my current stock for each product.”

Hope this helps. Send me your requirements and I’ll try my best to give you some useful feedback.