Many SunSystems users consider various Business Intelligence (BI) tools when they come up against some of the limitations of Vision / Q&A. However, a powerful tool that is often overlooked is one that many Sun users will already have access to - SQL Server Reporting Services (SSRS).
One of the major changes between versions 5 and 6 of Sun is that the reporting tool used changed from Actuate to SSRS. However, the vast majority of Sun 4 and 5 users have Microsoft SQL Server as their database and therefore may find they too have all the tools they need to create and use SSRS reports (more on this later).
Sun 6 users will have something called the report designer as part of the standard set of Sun menu options. This is primarily used for creating and editing documents that Sun produces as part of a process - e.g. remittance advices, customer statements, invoices etc. It is also where you can edit standard Sun reports such as account listings, journal listings etc. (although due to the reports that are supplied with Sun not always being of a particularly useable format most users end up re-creating these in Vision / Q&A instead so they are not widely used). However, this tool can also be used to create financial statements and BI reports such as in the example below:
In this example, each account line has an expand control next to it. Clicking on this allows you to see the details of the individual transactions that make up the total.
Just to re-iterate, this is all possible with tools that come with Sun 6 straight 'out-of-the-box'. However, these tools do come with some limitations on what data you can access and how it can be presented. To really unleash the power of SSRS (as well as use it in Sun 4 and 5), you can create and edit reports using the SQL Server Data Tools.
In order to use this you will need the reporting services included as part of your SQL server installation. Sun 6 users will have this, other versions may or may not (its a tickbox when you do the SQL Server installation). If not, it should be possible to add this (or ask your IT department to do so) to the SQL Server features. You will also need the SQL Server Data Tools in order to edit the reports. With SQL Server 2008 and 2012 these are included in the installation media, so again its just a tick box option when the software is installed. With 2014 and 2016 you have to download it from Microsoft, however there is no additional cost.
As an example of what's possible, I created the dashboard report below using the PK1 demonstration company that comes with SunSystems. All reports are viewable in a standard web browser.
By clicking on either the monthly profit figures on the right, or on the relevant data point on the graph I can go to a detailed profit and loss statement with expandable lines to view transactional detail. In the example below I have expanded line 14000 - Revenue Manuals.
From here, I can click on any of the journal numbers to view the full double entry of that transaction.
Similarly, going back to the main dashboard I can click on any of the debtor lines on the debtors bar graph to see details of that organisation and the invoices outstanding.
You can potentially link to other systems, so in the above report you could link to a document imaging system so clicking on the invoice number could take you to a scanned image of the invoice or other backup documents.
This is just a small sample of what is possible - SSRS is a hugely powerful reporting and BI tool which for many Sun users is available but not being exploited. As well as BI reports, it can produce transactional type reports that are not easily available (or even possible) using standard SunSystems reports, such as retrospective aged reports.
In summary, the key advantages are:
- Reports are fast and can cope will with large amounts of data as report writers have access to the underlying databases and can therefore query them in the most efficient way.
- Easy access for users direct in a web-browser (including many mobile devices) with the ability to drill-down with a single click.
- You can include data from other sources in your reports, such as CRM or Asset Management systems.
- A range of output options are available as standard, including PDF, CSV, Excel and Word.
- Usually there will be no cost involved beyond what you are already paying for SQL Server and no issues of licencing per user.
- Sun 6 users are likely to have all the relevant tools already installed - other version of Sun can usually be used with minor additions to the SQL server features.
A little more technical knowledge is required to create SSRS reports than for Q&A, however that is true for all BI tools, and with SSRS being a popular tool that expertise is not hard to find. I would say a reasonably technical finance person can be trained to at least be able to tweak existing reports should it become necessary.
If you are coming up against the limitations of Q&A I would strongly suggest you consider SSRS as a potential answer to your BI reporting needs. If you think it may be of interest to you and would like to discuss it further, please do contact me on andrew@zoanda.com.