Reporting Archives - SQLWorks

Getting Started with Query Builder

Query Builder:

Query builder is a powerful reporting tool that advanced users can use to interrogate the SQLWorks database, cross-reference data in new ways, and get insight from business analytics.

Working in steps across the screen, Query Builder guides the user across four main tabs including ‘Setup’, ‘Tables’, ‘Conditions’ and ‘Preview.’

Setup tab allows users to create new queries, and choose the base layer data for their query from their SQLWorks database. For convenience, users can save queries here, opt whether to share them with other users, and re-run their favourites to generate new results as system data is updated.

In the Tables tab, the user can drag in related tables to cross reference different data within the database, and build new comparative reports that don’t already exist by default by selecting one or more data columns from within those tables.

query builder gif

The Conditions tab is perhaps the most powerful – allowing the user to assemble or subdivide the existing data in ways to extract very specific report outputs. Three tools are available here – Filter By, Sort By, and Group By. Filter By options can be used flexibly to exclude irrelevant data an target subsets, while Sort By allows you to re-order everything based on a specific table column to highlight priority data first.

Group By allows users to aggregate data in powerful ways – for example totally values per company, automatically calculating averages into value fields, or totalling costs. Programming criteria are available to do this, including ‘Distinct’, ‘Count’, ‘Concatenate’, ‘Maximum’, ‘Minimum’, ‘Sum’ and ‘Average’ – with columns ordered or re-designated to structure the report as you wish.

query builder group by

In the Preview tab, users can preview the results of their query and export to a range of formats including Excel spreadsheet (.xlsx), Comma-separated value (.csv), text (.txt), PDF (.pdf), print formats, or even email the results direct to themselves.

We hope users will find Query Builder to be a potent weapon in their arsenal of reporting tools – giving you new ways to identify patterns, compare and contrast relative data, or summarise from a new perspective.

 

For more information, please see our full guide to using query builder, or contact our team today.

Introduction to Audit & Real Time ‘Live’ Reporting

Using SQLWorks Audit, accounting managers can generate key financial reports either ‘Live’ using real-time data, or from an enforced point in time.

This feature is available using the ‘Audit’ tool. When this tool runs, SQLWorks collates the most up-to-date financial data available from the accounts, to use for financial reports. Auditing can be performed immediately before the creation of a new report, by ticking the ‘Audit Matrix’ checkbox in the report options, to ensure the report shows ‘live’ data.

If the checkbox is left unticked, SQLWorks will report using figures from the last time the accounts were audited.

audit matrix

 

Auditing can also be run independently from the ‘Audit By Year’ option in the main SQLWorks Navbar, by right clicking on the financial year in the main list and clicking ‘Audit Year’. The adjacent column displays the date of the last successful audit. This can be used to manually designate an official Audited point in time, from which reports are run, until you are ready to re-audit.

‘Live’ data reporting is normally the preferred option, as it permits accounting managers to get an instant snapshot of the exact state of the business accounts at that moment in time.

audit live reporting

Reporting from the last audited point in time may be more useful in certain situations however. On systems with large numbers of transactions being entered, some businesses prefer to draw a line in the sand, and report consistently from this point in time, until ready to perform the next official reporting audit.

audit reporting

To learn more about SQLWorks financial reporting, contact our team today.

Introduction to EC VAT Reporting

SQLWorks allows users to accurately report on EC VAT, as part of their VAT Ledger.

In order to report on EC VAT, every sales and purchase account to be included must first have a country and VAT reporting setting saved against it.

For each customer or supplier, this can be set under the ‘Bank and VAT’ tab in the Sales Ledger/Purchase Ledger.

To set up the customer/supplier for EC VAT reporting, choose the correct European country from the ‘VAT Country’ (SL) or ‘Country Name’ (PL) fields, and select the correct setting under ‘VAT Setup’. ‘INC’ is for accounts which need VAT to be included, ‘Exempt’ for accounts with reportable VAT of zero value, and ‘NRT’ for accounts with non-reportable VAT.

Whether the selected country is part of the EC VAT reporting can be set in the SQLWorks countries list (found under ‘Countries’ in Address Editor.) Double click a country from the list to edit it from ‘UK’ to ‘EU’ or ‘World’ and click save. The EU category may also be used to designate countries not formerly part of the European Union, but part of the European Economic Area (e.g.: Norway.)

System Administrators may wish to force users to include a company VAT number when dealing with EEA customers/suppliers, for tax reporting purposes – this policy can be enforced via Preferences > Accounts Prefs > ‘Finance, Analysis, Dept & Tax’* Tab, by ticking the ‘EEC VAT requires VAT No in:’ option for either Purchase Ledger (PL), Sales Ledger (SL) or both. This will restrict users from saving any new transactions against sales/purchase accounts without a known and saved company VAT number.

Accounting admins may also choose which nominal codes need to include or exclude VAT by choosing a nominal code in the Nominal Ledger, clicking edit, and ticking the ‘Add VAT’ checkbox in the top right, before saving.

SQLWorks will collate EC VAT figures in the VAT Ledger beside UK VAT, in the ‘EC VAT’ columns (1 & 2), and VAT Ledger users can click the ‘EC VAT’ buttons at the top of each EC Vat column to view the transactions included in that section.

The VAT Ledger will also produce EC VAT totals in the ‘Total’s group’ section at the bottom of the window (3). These are then used in the Form 100 totals, allowing SQLWorks accounts users to complete an accurate VAT return.

 

For additional help or SQLWorks accounting expertise – contact our team today.

*This tab may be labelled ‘VAT & Dates’ in older versions of SQLWorks.