Tax blog for accountants

News and views from our experts.


Using Query Manager in Personal Tax to report on client information

written by Digita Support, 13 May 2016

By Chris Cullen, Tax Support Specialist.

Introduction

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.

Query Manager

Using existing queries

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.

Creating new queries

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. 

Limitations and alternatives

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.

twitterlinkedinFacebookgoogle_plusmail

Add a comment
please enter your name
please enter your email address
please enter your website address
please enter your comments

This page can be viewed at https://www.digita.com/pro/blog/index.php/query-manager/

Copyright © Thomson Reuters, 2017