SunSystems Reporting with Q&A / Vision 6


Click here to see a preview and buy your copy now.

Also available directly from amazon.co.uk and amazon.com.

Email the author with any questions or comments.



An alternative to using Vision Q&A to report and inquire on your SunSystems data is to use tools such as ASP.NET that allow you to dynamically create a web page populated with the results of a database query.

Vision Q&A is designed to be a tool that can be used by a non-technical person.  This is great for finance personnel as it means that they can get to the information they need without requiring the help of specialist report-writers.

But the user-friendliness of Vision Q&A as a design tool does come at a cost.  It is not completely flexible, and there are some reports / inquiries that it simply cannot produce.  Also, each simultaneous user requires an additional licence, which can be pretty expensive just to ensure that regular finance department users have uninterrupted access.  The cost of pushing Vision Q&A use to people outside the finance team can be prohibitive - even though you may only want them to be running pre-defined reports, not designing them, each person logged-in at the same time is taking up a user licence.

However, SunSystems data is held on either SQL Server or Oracle, both database systems that allow reporting tools and other applications to access that data directly.  Such tools can therefore be used instead of the standard SunSystems functionality.  There are many commercially available tools as well as some freely available ones, any of which can be used to report over a compatible database such as SunSystems.

Another method is to use a web server to extract data from a database and present it in a web page.  This page can be generated dynamically according to criteria entered by a user.  An example of this is the interactive SunSystems supplier inquiry that can be run by clicking on the link.  The data is from the 'PK1' demonstration business unit supplied with SunSystems 5/6.

There are a few things to note about this example.  Firstly, it opens in a separate dialog box on top of this page which can be dragged around as required.  The field in the light-grey, where a user can enter either the supplier code or name, works in a similar way to the google 'auto-complete' feature.  All the demo business unit suppliers start with '81' so typing '81' begins listing the suppliers in an auto-complete box below the main search.  Further typing narrows down the list, from which the user can select the correct supplier either by clicking on it or using the up / down arrows on the keyboard.  Users can also search by name, so within the same box typing in 'brit' will list all suppliers starting with these characters (or having these characters anywhere within their name) such as British Airways etc.

This is possible using techniques that are available to web developers but are not possible within a standard Vision Q&A report (or certainly not replicable anywhere near as smoothly as this example).  Also, in this case the data is not being extracted from the database each time a query is run (although this is certainly possible), but from a file which is updated each day from the main database using a script run on a schedule.  This means that the load on the database is reduced as supplier inquiries do not need to query the main SunSystems database.  Of course this approach does have a trade-off in that the data you are looking at is only 'as-at' the time of the last update rather than 'live'.

This is only a simple example of what is possible (also, in the real world of course this would generally be running within a corporate intranet rather than on the world wide web for everyone to see!).  For example, the balance figure could link to a list of outstanding invoices, which could in-turn link to a scanned image of the underlying document.  I have used such an approach in the past to give purchase ledger clerks the ability to quickly pull up outstanding and paid items on an account in response to telephone queries.  I have also used this method to run queries on the live ledger table to allow project managers (non-finance staff) the ability to see a summarised statement of their projects income and expenditure, with the ability to drill-down to the individual expense items so they can monitor in real-time what is being charged to their projects and how much money they have available to spend.

In summary, the advantages of using an approach such as this are:

♦ Complete control over the look and feel of all screens.
♦ The ability to use techniques such as auto-complete to enhance the user experience and mean that minimal training is required as it matches what users are already familiar with when browsing the web.
♦ No additional licencing costs - accessing the data directly does not require additional Sun or Vision Q&A licences.
♦ Potential integration with other web-based systems you may have such as document imaging, workflow control etc.
♦ Reduced database load by only querying the database directly where up-to-the-minute information is genuinely required and running batch updates (either to another database table or a text file) at quiet times for other data.  Also, live database queries are likely to be more efficient as the developer will have access to the underlying SQL and can therefore optimise the way in which data is extracted.

So what's the catch?  Well the disadvantages are:

♦ Programming knowledge is required.  Unless you have in-house knowledge of the various tools required you will have to pay for programmers who are experienced in the tools you decide to use.
♦ Design changes are not straightforward.  Again, without in-house skills any changes are likely to require the same specialist knowledge.
♦ This method is only for reading data and can not replicate the Vision Q&A data send.  Writing data to SunSystems should only ever be done via the official interfaces (journal import / external file transfer, transfer desk, SSC etc.) and never by updating the database directly.

This approach is therefore not suitable for all needs, but if you would like to make up-to-date information dynamically available to users outside the finance department with no licencing costs and minimal training then its definitely worth considering.

Any thoughts or comments on this?  Do you think it might be the right approach for your organisation?  Please email me at andrew@zoanda.com.

Back to the home page