UNDERSTANDING WHICH QUERY FIELDS TO USE

1. UNDERSTANDING WHICH QUERY FIELDS TO USE Certain fields in the FMIS database have been indexed to improve the timeliness of executed queries. Each query within a query grid should contain a specific value (e.g., fund code 192X) for at least one indexed field. Non-query grids have also been programmed to utilize indexes. Indexes for the tables in the FMIS database can be found by selecting ‘Database,’ then choose ‘Indexes.’ Important Note: The more query fields which are used to define a search, the longer the query will take to execute. If a user knows which Pegasys document number for which to look, it is recommendable to use that number to identify related transactions. Queries returning large datasets of more than 10,000 rows will give users the option to ‘Halt’ the query and present what has been returned so far, to prevent ‘runaway’ queries. U.S. General Services Administration The Financial Management Information System QUERIES QUICK REFERENCE CARD This Quick Reference Card is designed to provide users of FMIS with an understanding of how to use the available queries and Wild Cards to assist in data gathering, sorting by ascending and descending order, and how to identify when to use which query fields. KEY TAKE-AWAYS: To understand the differences between Query Grids and Non-Query Grids, how and when to use Wild Cards and operators, and data sorting to better manage query results Last Updated: March 2011 • FMIS RESOURCES • FMIS User Guide • FMIS Quick Reference Cards: • System Access and Log-In • Saving and Printing Data • VAT Data Collection Process • Queries • Frequently Used Queries • For additional support with FMIS, please contact the OCFO Service Desk at ocfoservicedesk@gsa.gov

2. QUERIES AVAILABLE • FMIS contains two basic types of queries: • Query gridsresemble spreadsheets (rows and columns of data in cells), in which searches can be performed for any value of any data element. • Non-query grids prompt the user to input selection criteria, and return data in various report formats. • QUERY GRIDS • All query grids in FMIS operate the same way. Each query grid is comprised of columns and rows where users can enter searching criteria – multiple lines of selection criteria are possible. • Query grids make use of logical operators to create a custom query to fit a user’s specific search criteria parameters. • Enter search criteria under the appropriate column for each accounting dimension. Criteria entered on the same line will be linked and operate as an ‘AND statement’; criteria entered on subsequent lines will operate as an ‘OR statement.’ • Important Note:When using wild cards in any FMIS query grids, the operator LIKE is required to precede the criteria and wild card operator. Also, the more wild cards used in a query, the slower the execution of the query will be. Once all the desired criteria have been added to a query grid, users should click ‘Retrieve’ and the resulting dataset will be displayed. NON-QUERY GRIDS One of the Non-Query Grids is the ‘VAT Category Lists.’ With this query, FMIS users can select transactions without knowing the general ledgers. Users will need to enter expense criteria then pick one of the categories of reports (Assets, Income, Expense, Obligations, Delivered Order, PY Recoveries, Cash or Commitments). Users can run the query with book month (defaulted value is current month) or users can put in the VAT range for the period of interest. If a user wants to run it for the whole fiscal year, only enter the Book Month, ACTG Period and VAT(s) range fields blank and select the required FY at the bottom of the screen. To find the VAT number for each fiscal year, users should go to ‘VAT’ and ‘List of VAT Numbers’ from the main menu. SEARCH WILD CARDS Wild Cards allow FMIS users to execute queries that search for data which matches certain patterns or contain certain characters. Two Wild Card formats are supported by the FMIS application: % (percent sign):Any number of characters, of any value will be returned. (e.g., a search for ‘LIKE 123%’ would return transactions with ‘1234,’ ‘1239,’ ‘123847’ or ‘123043932’ in the appropriate accounting dimension.) _ (underscore):One character, of any value will be returned. (e.g., a search for ‘LIKE 123_’ would return transactions with ‘1234,’ ‘1238,’ or ‘1230’ in the appropriate accounting dimension. SORTING RETURNED DATA To sort data returned from an executed query, click the ‘Sort’ button. Click and drag the necessary data elements in the report from the list on the left to the empty space on the right. The default sort order is ascending order. Remove the checkmark underneath “Ascending” to change the sort order to “Descending.” Multiple sorts are also possible. For example, to sort by the accounting dimension ‘cost element’ within the accounting dimension ‘object class,’ drag and drop ‘obj_class_cd,’ then drag and drop ‘cost_elem_cd’ underneath ‘obj_class_cd.’ When rows are not sorted, they will be displayed in the same order they were created in the database by Pegasys Document Number and PDN line number (latest records being listed last). Table 1: FMIS-supported Operators Figure 1: Query Grid Figure 2: Non-Query Grid Figure 3: Data Sort Screen