Summary

The Query Manager is used to generate customized financial and managerial reports. Query Manager reports only display summary detail. You cannot create reports that display transaction level detail using the Query Manager reporting tool. If you would like to generate reports that display journal entry line item detail, use the Reporting Manager tool (GL> Reporting Tools> Reporting Manager).

Report templates are created using the Reporting Tools palette (GL> Reporting Tools> Query Manager), and then reports are generated from those templates using the Reports palette (GL> Reports> Query Manager).

This document describes how to create query manager report templates.

Step by Step

1     View the Query Manager templates.

• The Query Manager Selection window displays all of the query manager templates that have been created in the application.
• Open the Query Manager Selection window (GL> Reporting Tools> Query Manager).
• Each of the templates in the window can be used to create a query manager report using the Reports palette (GL> Reports> Query Manager).
• Highlight a query manager report and press DELETE or click the Delete icon to delete the report.
  • Once a report has been published, it cannot be deleted.
• Highlight a report and press ENTER or click the Modify icon   to open the selected report.
• Highlight a report and click the Copy Report icon   to copy the highlighted report. This will create a second instance of the copied report with a similar but unique name.
• Click the Create icon to create a new query manager template. This will open the Query Manager Maintenance window.

2     Enter the general information about the report.

• The General tab is used to enter general report information.
• The Report Name field is used to enter the name of the report. When the report is generated (GL> Reports> Query Manager), the report name will display on the Jobs Viewer window as the description of the job.
• The Report Header field is used to enter the report header name. The report header will display at the top of the printed version of the report when it is generated (GL> Reports> Query Manager). The report header is generally the title of the report.
• The Report Group and Report Sub Group fields are used to group reports together so that multiple reports can be printed in a single step. These are optional fields and should only be used if your organization utilizes the query manager grouping functionality.
  • Report groups and sub groups must be set up before query manager templates can be associated with them. Groups and sub groups are set up on the Query Manager Group Maintenance window (GL> Maintenance> Query Manager Groups).
  • Query manager report templates can be associated with a report group and a report sub group or with a report group only.
    • If a user selects a Report Group and a Report Sub Group when printing the report, all of the Query Manager reports associated with that Report Sub Group will be printed.
    • If a user selects a Report Group but does not select a Report Sub Group, all of the Query Manager reports associated with all of the Report Sub Groups in the selected Report Group will be printed.
  • A Query Manager report can only be associated with one Report Group and one Report Sub Group within that Report Group.
• The Orientation field is used to select how the report will print. This option can be changed when the report is generated (GL> Reports> Query Manager).
• Check the Include uncommitted journal entries toggle if uncommitted journal entries should be included on the report.
• Check the Suppress accounts without a balance or budget toggle to remove accounts with no activity, a zero budget and no beginning balance from the report.
• Check the Include inactive accounts toggle if you would like to include inactive GL accounts on the report.
  • GL accounts are set up as inactive using the Chart of Accounts Maintenance window (GL> Maintenance> Chart of Accounts> General tab> Account is active toggle).
• Check the Financial statement presentation toggle if you want both the revenue and expenses to display as positive numbers.
  • When this toggle is checked, you should also check the Financial statement presentation toggle on the Sort tab of the sorts you would like to apply this to.
• Check the Repeat headers after page break toggle to include a new instance of the first page header on each new page.
• The Locked toggle is only enabled for Admin users. When this toggle is checked, all other users will be prevented from changing any of the saved report settings.

3     Select the information to include on the report.

• The Columns tab is used to select the column that will display on the report.
• The Available Columns section displays the columns you can add to the report.
• The Selected Columns section displays the columns that are on the report. For example, if the Account Number field is in the Selected Columns section, the Account Number will display on the report (0001-0000-0001).
• Add columns to the report by moving them from the Available Columns to the Selected Columns section. Double click on a field in the Available Columns section to add the field to the report, or double click on a field in the Selected Columns section to remove fields from the report.
• The order that the fields display in the Selected Columns section determines the order in which they will display on the report. The fields at the top of the Selected Columns section will display at the far left of the report.
  • Highlight a field in the Selected Columns section and click the Up or Down icons to change the position of the field.
• Click the Add Columns icon drop-down menu to the add columns to the report based on a pre-programmed report.
  • The selection in this menu option only determines the columns that will display on the report. This is not the same as the pre-programmed reports that are part of the Reporting Manager feature. For example, you can select Revenue versus Expense in the drop-down menu, but the report will still display asset, liability and fund balance accounts unless you remove them from the report using the Filters tab.
  • You can modify the columns on the pre-programmed reports once they have been added to the template. For example, you can select the Budget Analysis version of the report and then remove the Estimated Actual and Full Time Equivalencies columns if you do not use either of those features.
    • Estimated Actuals are added to GL accounts using the Edit Estimated Actuals feature (GL> New Budget Information> Edit Estimated Actuals).
    • Full-Time Equivalencies are added to GL accounts using the Edit New Year Budgets feature (GL> New Budget Information> Edit New Year Budgets).
  • Select Trial Balance if you would like the report to display a beginning and ending balance, as well as the debits and credits on the GL account.
  • Select Revenue versus Expense if you would like the report to compare the budget and activity of the GL accounts on the report.
  • Select Expense versus Budget if you would like the report to display the budget amount, activity during a selected date range, variance and encumbered amount.
    • Select Revenue Analysis if you would like the report to display the budget, activity during the selected date range and ending balance.
    • Select Income Statement if you would like the report to display the period amount, budget amount, variances and YTD budget amounts.
    • Select Budget Analysis if you would like to include the yearly budget information on the report. This version will display the prior three years of the budget, the prior year adopted budget and the next year’s requested, proposed, approved and adopted budget amounts.
  • Select Balance Sheet if you would like the report to display the ending balance.
• The One Year Prior End Balance, Two Year Prior End Balance and Three Year Prior End Balance columns are used in bi-annual budgeting.
  • Use these fields during the second year of a bi-annual budgeting period so that the current period values reflect either a single year or a combination of current and previous years.
• The way the system calculates the Ending Balance, Per Range Amount and Period Amount based on the fiscal period range specified on the Filters tab can be confusing.
  • Ending Balance = Beginning Balance + YTD activity from period 1 to the period specified in the Fiscal Period To field.
  • Per Range Amount = activity from the period specified in the Fiscal Period From to the period specified in the Fiscal Period To fields.
  • Period Amount = activity for only the period specified in the Fiscal Period To field.
    • For example:

• Once the columns have been added to the report, you can customize how they appear.
  • Highlight a field in the Selected Columns section and then modify the value in the Column Name, Column Width and Column Style fields to customize how the fields will display.
  • The Column Label field is used to customize the column name of a field on the report. The value entered in this field will override the field label that displays in the Selected Columns section. For example, if you add the End Bal column to the report, the “End Bal” will display at the top of the column on the report. Enter “Ending Balance” in the Column Label field if you would like that to display at the top of the column instead of “End Bal.”
  • The Column Width field is used to customize the width of the column selected in the Selected Column section.
  • The Column Style field is used to select how the values in the column will display. All of the values in the column will display in the style selected in the field, not just the column heading.
    • Select Truncate if you would like to remove the decimals from the dollar values in the column.

4     Select how you would like to sort and group the information on the report.

• The Sort tab is used to select how the information on the report will be grouped and sorted. This is a required step because if you do not include sort criteria on the report, the report will not display any information.
• The Available Sorts section displays the fields that can be used to sort and group the report. Move fields from the Available Sorts section to the Selected Sorts section to add the sort to the report. You can add up to seven sorts to the report.
  • Double click on a field to move it to the Selected Sorts section.
  • The following examples assume your GL account format has three sections: fund, department and account. If your GL account format has more than three sections, the examples will still apply, but you will have to interpret the examples to fit your own GL account format.
  • If you would like to generate a report that displays a single line item for each fund in the chart of accounts, move only the Fund field from the Available Sorts section to the Selected Sorts section. By default, the report will look like the following. You can change how the report will display using the Sort Properties section.

• If you would like to generate a report that displays a single line item for each department (section two of the GL account format), and then total those departments by fund, first add the Fund field and then add the Department field to the Selected Sorts section.  By default, the report will look like the following. You can change how the report will display using the Sort Properties section.

• If you would like to include GL account level detail on the report, add the Full Account field or Account field to the Select Sorts section.
  • The difference between the Full Account and Account field is that the Full Account field will display the entire GL account on the report (fund, department, and account). The Account field only displays the account section of the GL chart of accounts format. It will not include the fund and department.

• The first several fields that display in the Available Sorts section will vary depending on the format of your general ledger accounts.
  • If you are using three general ledger account sections, then the window will display the three sections of the general ledger accounts in the Available Sorts section. The title of the sort is determined by the name of the GL account section set up in the Miscellaneous Field Labels window (SS> Utilities> Miscellaneous Field Labels> GL Chart table).
• The Acct Type field will sort the report by general ledger account types.
  • GL account types are set up using the Account Type Maintenance window (GL> Maintenance> Account Types).
  • Account Types are attached to general ledger accounts using the Chart of Accounts Maintenance window (GL> Maintenance> Chart of Accounts> General tab> Account Type field).
  • The account type will also determine the ALFRE designation of a GL account because the ALFRE designation is attached to the GL account type (GL> Maintenance> Account Type> ALFRE drop-down menu).
• The Account Section Type, Department Section Type, and Fund Section Type fields allow you to sort the report by account, department, or fund section type.
  • The title of the section types is user defined and set up using the Miscellaneous Field Labels window (SS> Utilities> Miscellaneous Field Labels> GL Chart table> Account 1, Account 2, Account 3, etc. fields).
  • Section types are created and maintained using the Section Type Maintenance window (GL> Maintenance> Section Type).
  • The specific fund, department or account section types that will be included on the report will be selected when you run the report (GL> Reports> Section Type section). If you would always like to include specific section types on the report, add those section types to the Filters tab. By default, the section types selected on the Filters tab will be selected when the report is run on the Reports tab.
  • For example, if you have a few section types that include multiple funds and you would like the report to sort by those section types, add the Fund Section Type field to the Selected Sorts section. Then select the fund section type you would like to include on the report on the Filters tab.
• The Period Budgeting field will function even if you are not using period budgeting. The current budget amount will be divided by 12 to determine a monthly amount and then multiplied by the number of periods included in the report to determine the amount that will display on the report.
• The order that the sorts are added to the Selected Sorts section determines the order in which the sorts will be applied. Use the Up   and Down   arrow icon to change the order of the sorts on the report.
• The Sort Properties section is used to select how each sort will display on the report. Highlight a sort in the Selected Sorts section and then change the toggle values in the Sort Properties to customize how the sort will display.
  • Check the Print Header toggle if you would like the sort to display above the grouping. For example, if you sort the report only by fund, the report will look like the following:

• Notice that when you only check the Print Header toggle, the amounts associated with the sort will not be included on the report (the sample report above will not include the fund totals). Checking the Print Header toggle will only add the sort title and sort description to the report. It will not add any sort amounts. If you would like to add sort amounts, make sure to check the Print Footer toggle in conjunction with the Print Header toggle.
• For example, if you would like the fund number to display above a list of GL accounts, but you do not want the report to total by fund, check the Print Header toggle on the Fund sort and then add the Full Account or Account field to the Selected Sorts section.

• Check the Print Footer toggle if you would like the sort to display below the header and include a sort total. By default this toggle will be checked. For example, if you sort the report by fund, the report will look like the following:

• Check the Double Space toggle if you would like to add a blank line after each sort.
• Check the Page Break toggle if you would like to add a page break after each sort. For example, if you are sorting the report by fund and full account, you can check this toggle so that each new fund will display at the top of a new page.
• Check the Financial Statement Presentation toggle if you want to flip the sign of the revenue accounts at the selected sort level. This will display the revenue accounts as a positive number. If you are using this option, make sure the Financial Statement Presentation toggle in the General tab is also checked.
  • If you are filtering the report by ALFRE designation, you do not have to check the Financial Statement Presentation toggle. The report will automatically flip the signs of the revenue accounts and display them as positive numbers.
• Check the Bold toggle if the sort should display in bold. For example, if you only checked the Print Header toggle, you could check this toggle to display the sort in bold.
• Check the Italic toggle is the sort should display in italics.

• The Cross Tab Sort drop-down menu is used to add a cross tab sort to the report. This feature allows you to create a pivot table or matrix report.
  • When a cross tab sort is selected, the data in the report will be organized both horizontally and vertically.
  • If you create a cross tab report, make sure you do not have many columns selected on the Columns tab. If you select too many columns, the report may time out on your server.
• The Underline toggle is used to add an underline to the sort.
• The Total Underline toggle will only be enabled when the Print Totals toggle in the Sort Properties section is checked.

5     Select the fields you would like to total in the report.

• The Totals tab is where you select which amounts you want to total on the report. The totals selected on the Totals tab do not replace the totals that were set up on the Sorts tab. The totals set up on the Totals tab are in addition to the totals defined on the Sorts tab.
• The Available Totals section displays the totals that can be added to the report. The Selected Totals section displays totals that have been added to the report.
• Double click on a total to move a total from one section to the other.
• Configure the underline, bold and italics options.

6     Select the default filter on the report.

• The Filters tab is used to add default filters to the report. When the report is generated (GL> Reports> Query Manager), the information entered on this tab will populate on the window. Users will be able to modify the filters or run it as it was created.
  • You are not required to specify any default filters on the template. When a user generates a report using this template, the system validation will ensure that the required filters are specified.
• The Account From and Account To fields are used to filter the GL accounts that are included on the report by a range of accounts.
• The Date Type drop-down menu is used to select the type of date you would like to use to filter the transactions included on the report.
  • Select Date if you would like to filter the transactions that display on the report by journal entry date.
  • Select Fiscal if you would like to filter the transactions that display on the report by fiscal period and fiscal year.
  • The selection in this drop-down menu will determine which date fields are enabled on the Filters tab. For example, if you select Date, the Date From and Date To fields are enabled to enter a journal entry date range. These fields are not enabled if you select Fiscal.
• The System section is used to filter the journal entries included on the report by the module that was used to create them. For example, check the PR toggle if only Payroll module journal entries should be included on the report.
  • You can view the system that was used to generate a journal entry using the Display feature (GL> Display> Journal Entries> System column).
• The ALFRE section is used to filter the GL accounts that are included on the report by the ALFRE designation attached to the GL account.
  • An ALFRE designation is attached to a GL account using an account type.
    • An ALFRE designation is attached to an account type using the Account Type Maintenance window (GL> Maintenance> Account Type> ALFRE field).
    • An account type is attached to a GL account using the Chart of Accounts Maintenance window (GL> Maintenance> Chart of Accounts> General tab> Account Type field).
• The Section Types section is used to filter the report by section types. Section types are collections of sections and are created using the Section Type Maintenance window (GL> Maintenance> Section Type).
  • If the current report has been added to a Query Manager Report Group or Report Sub Group, the Section Types selected here cannot be overridden on the Query Manager Report window.

7     Save the report.

• Click the Save icon   when complete to save the Query Manager template. Once the template has been saved, you can use it to generate a report from the Reports palette (GL> Reports> Query Manager).