Fetch is PensionPro's data query tool. All queries created in Fetch can be saved, shared, and exported to multiple formats. The File Menu allows easy access to your saved queries, the saving of new queries and to the program options.
Tier Availability: Premium, Business
Security Rights Required: Access Query Tool
Using the File Menu
The File Menu provides multiple functions in allowing users to change Fetch Preferences, create new queries, open, save, share and delete queries. The File Menu can be found at the top left-hand corner of the Fetch application.
New Query - This quickly opens a new query without clearing the checkboxes from the previous search.
Open Query - Loads a saved query into the Query Builder Screen. To open a saved query, select the File button in the upper left-hand corner. Then click, Open Query.
- Personal: Queries that are displayed for the user's personal use. No other Fetch users will be able to see or use these saved queries.
- Shared: Queries that are available to the entire firm. All users of Fetch can access and use these saved queries.
- Delete: Permanently removes a query from the system. Take caution as once it is deleted, it CANNOT be retrieved.
- Open: Loads the selected query into the Query Builder.
- Cancel: Closes the Load Query Screen. Any queries that were deleted remain deleted.
Save Query - Saves the current query settings as a new query or replaces an existing one. Select File > Save Query.
Personal Open Query - Keeps the query private for the users personal use. No other Fetch users will be able to see or use these saved queries. Select File > Open Query > Type: Personal.
Shared Query - Shares the query with the entire firm. All Fetch users can access and use these saved queries. Select File > Open Query > Type: Shared.
- Replace: Completely replaces the selected query with the current one. Take caution as once it is replaced, it CANNOT be retrieved.
- Create: Creates a new saved query with a new name and description.
- Description: Use this field to explain what the query does. This is especially convenient for Shared queries so other users will know what it is intended for.
- Save: Save the query using the chosen options. If everything else is OK, click the button. Changes saved to shared queries are global.
- Cancel: Abort the save process and close the Save Query Screen.
Options - Loads the Options screen. There are options available to control how the program responds to events in Fetch
- Save my username: The default to this option is set to "Checked." It will store the username that was entered at login so that it does not need to be re-entered when logging into Fetch again.
- Automatically open the message list when a new message arrives: The default to this option is set to "Checked." New messages for changes to the Queries built will automatically be displayed at the bottom of the screen as the user works in the Query Builder. Uncheck it to turn this option off.
- Automatically open data files after exporting: The default to this option is set to "Checked." This option notifies Fetch if the user would like to automatically open an exported file after it's saved. Uncheck it to turn this option off.
Exit - By clicking on the Exit menu option, the application will be closed. It will prompt the user to save the changes to any open query before closing, preventing any loss of work from occurring.
The Fields section contains the “tree” of fields that are available to be selected. The fields selected will be returned in the Result tab as columns of data. Users can select field by checking the box to the left of the field name. Selected fields
Selecting Field Order
The Selected Field Order allows the user to determine how the report will appear once it is executed.
Once Fields have been selected, the order of this list determines what order the fields (columns) will be returned in the results. Use the blue up and down arrows located to the right of the section to change the order. Unchecking the box to the left of a field name will remove the field from the query.
Sorting determines the order of this list of resulting records displayed on the Result Tab.
To sort the results by a field
- Highlight the field in the Fields section by clicking on the name, NOT the checkbox.
- Click the Sort button - The field will be displayed in the Sorting section.
- The sort order will default to Ascending. Click on the dropdown located to the right of the Field name added to change the sort order.
In the sorting list, there is an option of ascending (1 to 9, A to Z) or descending (9 to 1, Z to A) for each item.
Filters allow users to refine the number of records displayed.
Some fields, when filtered, will display with a green plus or minus next to them. Like the plus and minus option for nodes, this determines whether blank records will be returned in the data set for that field.
- "And/Or" Level Control - Green Plus: Adds a filter to the current "And/Or" level.
- Red X - Removes a filter. If clicked on in the level control it will remove all filters in that level.
- Filters - Filters are broken up into sections that allow "And/Or" logic to be applied. This enables the user to query their data in an extremely precise manner.
- Field Selector - Determines what field the filter will be applied against.
- Operator Selector - The operator that will be applied to the field chosen in the Field Selector.
- Branch - Creates a new level for the filter.
- Value Field - The value to use for the filter.
- Clear Button - Removes all filters.
Note: When attempting to retrieve specific data, Data Filters will be necessary to further filter and streamline the results. For example, to find a list of only active plans that have yet to be completed, the user could use a Plan Status data filter that is equal to Active. Then an additional data filter, Completed On with an is equal to NULL field, which would then generate active plans that have yet to be completed.
Add a Filter for a field without including the field in the report
- In the Fields window, click on a field name to highlight it (Do not check the field box).
- Click the Filter button to the right of the Fields window.
- The Filter addition will appear in the Messages window at the bottom of Fetch.
- Users can click Delete or Delete all to remove added filters from the Messages window.
- The User can now select that field as a filter in the Filters window without including a column for that field in the results.
Please refer to our screenshot for further understanding of how Data Filters can be used to further streamline data.
Viewing the Results
The Results Screen displays the results of an executed query. Records are returned in batches of 100 and sorted based on the “Sorting” section of the Query Builder Screen. The 'Export' button can be used export the records to a file.
The data displayed within the results screen mirrors the order selected within the Selected Field Order section of the Query Builder. All sorting and ordering of the data will need to be done on the Query Builder screen so that it can be saved as part of the query. The exported file can be saved as a .xls, .csv, or .xlxs file.
Fetch currently does not allow for crosstab or pivot table reporting. Users should export their results to excel and create their crosstab/pivot table format there.
Frequently Asked Questions
- Is it possible to filter on a field selection, but hide the field in the report so it doesn't display in the results? To add a field as a filter without displaying it in the report, click on the field text (do not check the box) and click the Filter button to the right of the Fields window. The user can then select that field in the Filters window as a selection.
- Can I grant access for some users to view shared queries, but not others? Currently, any user that has access to Fetch can view and edit shared queries. The only way to restrict access to saved query is to save it as a Personal Query.
- Can I control who has rights to edit a shared query? Currently, any user that has access to Fetch can edit a shared query. There isn't a separate right to control which users can edit a shared query.
- Can the Fetch queries be made to look more like the standard reports in PensionPro? Currently Fetch does not display the color format, date generated and title as seen in the standard PensionPro reports. Users can export the Fetch results to excel and format the report as desired.