Using DWQuery Guide

Many long-time UCI employees will be familiar with this new interface in DWQuery. Below is a guide on the functionality of DWQuery. For more details on how to use the KFS-GL Query, see the DWQuery KFS GL Guide.

Accessing DWQuery
DWQuery Action Buttons
DWQuery Tabs
DWQuery Lists
DWQuery Results Page

Accessing DWQuery

DWQuery is accessible on ZotPortal in the Finances/KFS Tab. Locate the KFS Decision Support portlet, expand the DWQuery section, and click the DWQuery - KFS General Ledger link.

ZotPortal Finances/KFS tab showing the KFS Decision Support portlet with the DWQuery section expanded and the DWQuery - KFS General Ledger link visible — The KFS Decision Support portlet on ZotPortal's Finances/KFS tab with the DWQuery section expanded.

DWQuery Action Buttons

At the top of each DWQuery, there is a list of Action buttons that allow you to load, save, view and run your query. There are also buttons to select columns on a tab to be returned in your results, set your column order, reset your query, and aggregate and distinct options for your query.

DWQuery action button toolbar showing Load, Save, View Query, Select/Deselect, Set Sort Order, Reset, Summarize, Distinct, and Run buttons — The full set of DWQuery action buttons displayed at the top of the query interface.

Load, Save, and View

DWQuery toolbar showing the Load button with dropdown arrow, Save button, and View Query button — The Load, Save, and View Query buttons used to manage saved queries.

The Load button opens a window that allows you to load, delete, or view previously saved queries. Next to the Load button is a dropdown menu of the most recently saved queries for quick access.

Dropdown menu next to the Load button displaying a list of recently saved DWQuery queries — The quick-access dropdown showing recently saved queries.

Users can enter their own UCInetID and then select the Get Queries button to view queries that they have saved.
Users can enter someone else’s UCInetID to view queries saved by others.

DWQuery Load window with a UCInetID input field and Get Queries button for retrieving saved queries by user — Enter a UCInetID and click Get Queries to retrieve saved queries for that user.

Highlighting a query and selecting the Load Query button will load the previously saved query.
Highlighting a query and selecting Delete Query button will delete the query. The query will no longer be saved.

DWQuery Load window showing a list of saved queries with Load Query and Delete Query action buttons — Select a saved query from the list, then use Load Query or Delete Query to open or remove it.

Highlighting a query and selecting the View Description button will display the query description.

DWQuery window displaying a saved query's description text after the View Description button is selected — The View Description option displays the description associated with the highlighted saved query.

The Save button will open a window that allows you to save your current query. Enter your UCInetID, name the query, add a description and select the Save button.

DWQuery Save window with input fields for UCInetID, query name, and query description, along with a Save button — The Save window for storing a query with a name and description under your UCInetID.

The View Query button will open a window that displays all the attributes you have selected.

DWQuery View Query window listing all the attributes currently selected for the active query — The View Query screen summarizes all attributes selected in the current query.

Select/Deselect

This button selects or deselects all the attributes on the current screen. Marking a checkbox next to an attribute indicates that you want that column to appear in your output.

When the Select/Deselect button is clicked once, all checkboxes in the open tab will become checked. Clicking on the button again will deselect all the checkboxes for that tab.

DWQuery tab with all attribute checkboxes checked after clicking the Select/Deselect button once — All attribute checkboxes checked after the first click of Select/Deselect.

Set Sort Order

The Set Sort Order button allows you to choose which columns appear in your results and the order that they appear in.

Select the attribute that you want to appear first and then select the add selected items button

Set Sort Order window showing the Current Sort list on the left and the Add Selected Items button used to move attributes to the New Sort list — Use Add Selected Items to move attributes from the Current Sort list to the New Sort list.

Set Sort Order window with an account attribute moved into the New Sort field — An account attribute added to the New Sort field in the Set Sort Order window.

The Add All Items button will add all of the attributes in the Current Sort field to the New Sort field
The Add Selected Items button will add the attribute(s) that you select from the Current Sort filed to the New Sort field.
Hold the Shift key down to select multiple adjacent attributes at once.

Sort order attribute list with multiple adjacent items highlighted using the Shift key — Multiple adjacent attributes selected using the Shift key in the sort list.

Hold the CTRL key down to select multiple nonadjacent attributes at once.

Sort order attribute list with multiple nonadjacent items highlighted using the Ctrl key — Multiple nonadjacent attributes selected using the Ctrl key in the sort list.

The Remove Selected items will remove the attribute(s) that you select from the New Sort field and add them back to the Current Sort field.

Set Sort Order window with the Remove Selected Items button highlighted, returning selected attributes from the New Sort field to the Current Sort list — Remove Selected Items moves chosen attributes from the New Sort field back to Current Sort.

The Remove All Items button will remove all attributes in the New Sort filed and add them to the Current Sort field.

Set Sort Order window with the Remove All Items button, clearing all attributes from the New Sort field and returning them to Current Sort — Remove All Items clears the New Sort field and returns all attributes to Current Sort.

The Reset button will move attributes from the New Sort list back to the Current Sort list.
Once you have put the attributes that you want in your query select the OK button
The Cancel button will close the Set Sort order box. The list order of the Current Sort box will not change when the cancel button is selected. Any custom sorting will be saved until you exit ANTquery or select the Set to Default Order button.
The Set to Default will set the current sort back to its original default order.

Reset

The Reset button will reset all the attributes on the current screen.

Selecting the dropdown menu and then selecting the Reset All button reset all of the attributes on all screens

DWQuery Reset dropdown expanded to show the Reset All option for clearing attribute selections across all query screens — The Reset All option in the dropdown clears attribute selections on every screen.

Summarize

Summarize box allows you to display summarized results rather than full detail. For example, you may want to display the total amount for specific General Ledger transactions for a KFS account rather than all transaction details for that KFS Account. To summarize data, you must first select an attribute or attributes to summarize on (such as organization code, account number, and object code) by clicking the checkbox next to the attribute name. You must then unselect all other fields, except the amounts that you want to see summarized. To unselect a field, you will have to uncheck the respective attribute.

Information on how to use the Summarize feature is available on our Advanced Queries page (make Advanced Queries a link to the Advanced Query page

DWQuery Summarize checkbox used to display aggregated totals instead of individual transaction details — The Summarize option enables aggregated results rather than full transaction detail.

Distinct

By checking the Distinct checkbox, all duplicate rows will be eliminated from your query output. For example, if you are querying Travel Reimbursement information by Rollup Organization code, then the information would appear multiple times in the output. Checking “Distinct” eliminates this.

Run

Selecting the Run button will allow you to view your query results in a formatted layout.

DWQuery Run button with a dropdown arrow for selecting run or export options — The Run button and its dropdown menu for running the query or downloading results.

DWQuery results page showing formatted query output displayed in a tabular layout with column headers and data rows — Query results displayed in a formatted layout after selecting Run.

Selecting the drop down menu will allow you to download an editable excel spreadsheet containing your results.

DWQuery Run dropdown menu expanded to show the option to download results as an editable Excel spreadsheet — The Run dropdown provides an option to export query results to an editable Excel file.

DWQuery Tabs

Below the Action Buttons, there are a list of tabs where users can filter and build their query. For more specific details on the KFS GL Query, see the DWQuery KFS-GL Guide.

DWQuery interface showing the row of tabs below the action buttons, including Campus Hierarchy, Full Accounting Unit, and Ledger Detail — The tab navigation area below the action buttons for filtering and building queries.

DWQuery Lists

The Campus Hierarchy, Full Accounting Unit, and Ledger Detail tabs all have fields with a list button next to it. The list buttons will provide a list of attribute to choose from.

Example:

If you want to see a list of all Level 03 Organization Codes (9***) you can select the List button next to the KFS Org Rollup Level 03 Code field.

DWQuery Campus Hierarchy tab with the List button highlighted next to the KFS Org Rollup Level 03 Code field — Click the List button next to KFS Org Rollup Level 03 Code to view available organization codes.

Query Selection Screen showing a list of Level 03 Organization Codes beginning with 9 and available for selection — The Query Selection Screen displaying available Level 03 Organization Codes.

Save and Close

The buttons at the top of the Query Selection Screen allow users to choose attributes from the list and navigate to other lists.

Users can check the box of the attribute(s) to be pulled into their report. After the appropriate boxes are checked the Save and Close button will save checked items and close the Query Selection Screen.

Query Selection Screen header showing attribute checkboxes with the Save and Close button at the top — Check the desired attributes and click Save and Close to apply selections and return to the query.

Actions

The Actions Drop Down allows users to choose or clear multiple checkboxes at once.

Selecting “All” will check all of the boxes in the Query Selection Screen.
Selecting “Between” will check all boxes that are between two boxes that have been checked by the user. In this example the user has checked 9002 and 9005. When the Between option is selected 9003 and 9004 will also be checked.

Query Selection Screen with organization codes 9002 and 9005 checked before selecting the Between option — Two organization codes, 9002 and 9005, checked in preparation for using the Between option.

Query Selection Screen showing organization codes 9002 through 9005 all checked after selecting the Between option — After selecting Between, codes 9003 and 9004 are automatically checked between the two selected values.

Selecting “Clear” will unselect all checkboxes in the Query Selection Screen.

List Navigator

The drop down menu to the right of the Actions drop down menu allows users to move from one list to another without having to exit the Query Selection Screen. In this example we are going from the KFS Org Rollup Level 03 Code list to the KFS Account list.

In the account list, only Accounts associated with the KFS Org Rollup Level 03 Codes selected in the previous list will be shown.

KFS Account list in the Query Selection Screen showing only accounts associated with the previously selected Level 03 Organization Codes — The account list filtered to show only accounts linked to the selected organization codes.

Some lists will require that you select specific attributes. For example, the KFS Account list button will require you to enter a KFS Organization Code, a KFS Control Account, or other attributes that will help identify the group of accounts that you’re looking for.

If you need to view all accounts in a specific Organization Code, enter the KFS Organization Code and then select the KFS Account list button.

DWQuery Full Accounting Unit tab with a KFS Organization Code entered in the field and the KFS Account list button ready to be selected — Enter a KFS Organization Code before clicking the KFS Account list button to filter the account list.

Query Selection Screen showing a filtered list of KFS accounts matching the entered Organization Code — The KFS Account list filtered by the entered Organization Code.

Results Page

Report Search Fields

When a report is ran, each column will have search fields that can help find specific transactions.

In this example the word “phone” is entered into the Transaction Description to find all TELEPHONE USAGE transactions.

DWQuery results page with the word 'phone' entered in the Transaction Description search field, filtering results to display TELEPHONE USAGE transactions — Filtering results by entering a search term in the Transaction Description column.

Multiple search fields can be used at once to find specific TELEPHONE USAGE transactions.