By Chris Cullen, Tax Support Specialist.
Whether it’s for tax planning purposes or internal housekeeping it’s always useful to be able to report on clients meeting specific criteria (over a certain age, specific income types or thresholds, etc.); this can be done in Personal Tax using the Query Manager.
Queries can be set up in the Query Manager by going to Tools | Query Manager,
Within the Query Manager there are a large number of pre-populated queries in the Samples (Read Only) folder, which are split between two different types – client specific queries (clients over a certain age, clients with refunds, inactive clients, etc) or schedule queries (clients with income from property, capital gains, or dividend income, for example).
Feel free to browse through the sample queries provided for one that meets your needs.
If you find a query you’d like to run against your client list, right-click on it and select ‘New Query Based on Current Selection’, then click OK to save a copy of the query to the temporary or user folder (you may wish to type a new name for the query in the top left of the screen first).
You can then run the query against your database by going to Reports | Client Details… | set the radial in the bottom left to Use Query: | click Select and choose the query you’ve just created from the Select Query window. You may want to tweak some of the other report details such as sort order or display type, then click OK to run the report.
If the sample queries aren’t quite what you need, you can create new queries of your own
In the Query Manager highlight the Temporary (Work In Progress) folder, then click New | Query… this brings up a basic query. The Group column is used to choose which schedules to report on, and the Instance column can be used to select a subset of the group. Depending on the Group and Instance selected, you may be given the option to select a Condition (which may be a further subset of the Instance, or an operator such as ‘greater than’, ‘equal to’ or ‘less than’) and a Value.
The ‘More…’ button in the bottom-right allows you to add specific user assignments to the query, or apply the query only to a specific tax year (rather than all tax years).
You can switch the query from Basic to Advanced by clicking on the Advanced… button. However, this option should only be used if you’re proficient in SQL (the database language all Digita software uses) or if you’ve been provided with the text to use by Digita – as incorrect formatting of the query may crash the software when the query is run.
If you’d like a more complex query, you may wish to have our Professional Services team write the query for you – your Account Manager would be able to discuss the process and costs associated with custom queries.
While the Query Manager can be a powerful tool for identifying groups of clients with similar needs, the report is limited to a set layout of column headings (Client Code, Client Name, Tax Reference and Telephone number) which cannot be changed.
Therefore, while you could create and run a report for clients with total UK dividends over a certain amount in the latest tax year, it wouldn’t be possible to have the query show the actual total dividends they received. If you wish to extract specific figures for income types, schedule breakdowns, etc., you may wish to look at our Data Mining Tool. Please contact your Account Manager for further information or a demonstration of the Data Mining Tool.
This page can be viewed at https://www.digita.com/pro/blog/index.php/query-manager/
Copyright © Thomson Reuters, 2017