Query Manager

Query Manager (QM)

Query Manager is a SunSystems facility that enables you to extract selected data from the system. You can then view this data or pass it to another system function for further processing.

For example, Query Manager is used to:

• extract and display selected transactions, to provide data queries such as Account Inquiry.

• extract and display static data and reference information, such as account codes and names.

• extract selected transactions and pass them to a printing function, for example to produce account statements, picking lists, sales invoice, or dispatch notes.

Other features of Query Manager include the ability to:

• group the query results in a summarized format, with subtotals. Multiple levels of nested groups are possible. These can then easily be expanded back to the most detailed level.

• resequence the results.

• drill to related data under certain circumstances.

• carry out further visual selection (known as 'tagging') before launching an associated function.

• export the extracted data to MS Excel or to an HTML file

32.1.1. Running Query Definitions

To run a query definition that has already been defined, select Shortcut QM and the following window will be displayed:

Select the Context and the Query Definition that you wish to run e.g. Financial Ledger Inquiry

You can select a context (please see list below for options) or you can select all.

If you select a specific context the list of query definitions available to you will be those associated with the context you selected. For example, if you select Sales Order, only sales order query definitions will be available to you in the Query Definition drop down. If you select all contexts then all query definitions will be listed in the query definition drop down list.

Below is a list of Contexts that are available:

All

Drills for Financials Financial Ledger Inquiry Financial Match Process

Financials Print Account Documents Fixed Assets

SDM Chart of Accounts

Query Definition

A query definition is a pre-defined inquiry that has:

• Selection Criteria

• Output results form

• Ability to run report or process for selected data

In this example we have selected the query definition Financials Ledger Inquiry – Generic.

Options for a particular query definition will be determined by how it has been configured. Query Definitions are created and amended using Data Access Manager (DAM) which is covered later in this session.

Selection criteria

Selection criteria is user definable and specified by the person who configures the query.

In this example values for Accounting Period, Transaction Date, Entry Date and Due Dates ranges were available as default values but can be over-ridden by the user at run time.

The query is executed by entering the required data selections and clicking OK.

Output results Screen

Columns (and the order in which they appear) and the ability to filter and sub total amounts is user definable and specified by the person who configures the query.

Re-ordering Columns

To re-order columns click and hold on the column heading and drag to the position where you wish the column to be.

Sorting Data

If you wish to sort the data in a different order to the default then click the column heading that you wish to sort by.

The sorted column will then be identified with an upward pointing arrow to mark as sorted in ascending order. If you wish to sort the column in descending order then click the column heading again and the data will be sorted in descending order.

If you wish to sort by more than one column then select the first column and holding the shift key down select the next column you wish to sort by.

Filtering

To add a filter to your data select any column with a drop down option and select the value that you wish to filter by.

In the above example Journal Number 83 has been selected. This selection then

Ad Hoc Filtering within a query

You can also use the blue filter row to filter your data. If you enter the data value you are looking for into the appropriate field in the filter row the system will return matching values. In this example we searched on Base Amount -175.29.

If you wish to remove all the filters from your data then click the icon and the data will be returned to its original form.

Tagging Transactions

You can select transactions for matching, printing or further processing by ticking the selection box for each required transaction. If your records are grouped then select the group checkbox will tag all records in that group.

The following icons can also be used for tagging purposes:

Untag Selected Rows Tag Selected Rows Tag All

Hide Tagged Rows

The above diagram shows records that have been tagged.

Grouping

Column headings can be dragged into the group area to group by the required values

Where grouping is in place and totalling has been invoked for value columns each group will be subtotalled.

Grouping can make the selection of data for tagging simpler. All of the data in a group can be selected by ticking the tag box at the group level which will result in all the transactions within that group being tagged.

Once grouping has been applied you can use the Expand and Collapse Icons to expand and collapse your groups.

Groups Expanded

Groups collapsed

If you wish to remove all grouping from your data then click the remove all defined grouping Icon .

Drills

There are several drills supplied with SunSystems 5.3.1. Values listed and underlined have an associated drill.

In the example below the drill has been selected on account number 11000

You can continue to make drill selections on Journal Number or Transaction Description.

In subsequent versions you will be able to define your own drills. At the 5.3.1 release only pre-built drills are available.

Exporting results to Excel

Once your data is extracted you may at any stage export this information to an Excel spreadsheet.

Only the information displayed in the grid at that time will be exported to Excel. If you have filtered your data, only the filtered information will be extracted.

To export the data from the grid to Excel

Select the export grid as an Excel spreadsheet Icon The following window will then be displayed:

Select an area where you wish to save your spreadsheet.

Enter a file name and then click save

Your data will then be exported to Excel:

Exporting the grid in HTML format

If you wish to export the data in the grid to an HTML format then you can follow the same procedure as above but click the Export the grid to HTML format Icon .

Enter a location for the file, enter the filename and save.

Actions

Actions are the reports and processes that can be run from within a query.

Systems Union have supplied all the standard actions you need (that are currently supported). Actions may be associated with one or more context.

If your context has actions associated with it then you will see an available actions drop down list at the bottom of your grid:

In this example there are two actions available for you to choose.

Tag the records you wish to process, select the relevant action and then choose go.

The system will then automatically call the process for this action and you can complete the action as normal.