GL> Reporting Tools> Query Manager

Query Manager Templates

 

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.

 

Related Links

 

Click here for information on generating a Query Manager report once the template has been created.

Click here for an overview of Reporting Manager Tools.

 

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).
  • Select a query manager report and click DELETE to delete the report.
    • Once a report has been published, it cannot be deleted.
  • Select a query manager report and click EDIT or double-click the report to open the selected report.
  • Select a report and click COPY to copy the selected report. This will create a second instance of the copied report with a similar but unique name.
  • Click the Create Query button 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). Click here for information on Query Manager Group Maintenance.
    • 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 how you would like to sort and group the information on the report. 

 

  • The Sorts 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.
    • Select a field in the Available section can click the right arrow ˃ to move it to the Selected section. Select a field in the Selected section and click the left arrow ˂ to remove from the report.
    • 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.

      •  

       

      Column

      Column

      Column

      Fund 1

      XXX

      XXX

      XXX

      Fund 2

      XXX

      XXX

      XXX

 

    • 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.

      •  

       

      Column

      Column

      Column

      Dept 1

      XXX

      XXX

      XXX

      Dept 2

      XXX

      XXX

      XXX

      Fund 1

      XXX

      XXX

      XXX

      Dept 1

      XXX

      XXX

      XXX

      Dept 2

      XXX

      XXX

      XXX

      Fund 2

      XXX

      XXX

      XXX

 

    • 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.
  • 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).Click here for information on miscellaneous field labels.
    • Section types are created and maintained using the Section Type Maintenance window (GL> Maintenance> Section Type). Click here for information on section type maintenance.
    • 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 arrow ˄ and down arrow ˅ 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:

      •  

       

      Description

      Amounts

      Fund 1

      XXX

      		  

      Fund 2

      XXX

      		  

      • 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:

      •  

       

      Column

      Column

      Fund 1

      XXX

      XXX

      Fund 2

      XXX

      XXX

 

    • 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.
    • Check the Suppress Zero Filled toggle to prevent zero filled accounts from displaying on the report.
    • Check the Print Totals toggle to include a totals line on the report.
  • 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 field 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.

 

4     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. Select a field in the Available columns section and click the right arrow ˃ to add the field to the report. Select a field in the Selected columns section and click the left arrow ˂ 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.
    • Select a field in the Selected columns section and click the up arrow ˄ or down arrow ˅ to change the position of the field.
  • 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 Label, 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.

 

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.
  • Use the right arrow ˃ and left arrow ˂ to move totals between the Available and Selected section.
  • Use the up arrow ˄ and down arrow ˅ to order the totals in the Selected section.
  • Configure the underline, bold, and italics options for each total in the Selected section.

 

6     Select the default filters 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 Budget Group field is used to filter the accounts included in the report by the Budget Group those accounts are assigned to.
  • 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). Click here for information on section type maintenance.

 

7     Save the report.