PensionPro Fetch

Follow

Fetch is PensionPro's data query tool. The Fetch querying tool acts as an open database where users can create customized queries of their PensionPro data. All queries created in Fetch can be saved, shared, and exported to multiple formats. This makes it easy to create customized reports on PensionPro data, run those reports regularly, and even use that data elsewhere when needed.

 

Tier Availability: Business

 

Article Contents

 

 

 

Accessing Fetch

 

Requires Security Rights: Access Query Tool

 

To access PensionPro Fetch, navigate to Power Tools > Fetch. Fetch will open in a new browser tab.

 

Fetch can alternately be accessed directly via URL at https://fetch.pensionpro.com. Accessing Fetch in this way will require signing in with one's PensionPro username and password.

 

 

 

 

 

Getting Started with Fetch Queries

 

When opened, Fetch displays a blank query; users begin choosing data and building their report. Personal and Shared Queries are just a click away, allowing users to retrieve existing queries made by themselves or others.

 

 

Create a New Query

 

To start from scratch with a blank query:

 

  1. Select Add New Query from the toolbar on the left. The corresponding window displays.
  2. Enter a Name and optional Description.
  3. Select the Repository. This determines if the query will be personal or shared with other users.
  4. Select Save.

 

Once saved, the query will be added to the chosen repository. The Overview card will be populated with the information entered during creation; the other cards will be blank and ready for use.

 

 

Open a Saved Query

 

Instead of creating a new query, previously-saved queries can be opened from one of the following repositories:

 

  • My Queries are only available to the current user.
  • Shared Queries are available to any user in the firm.
  • Library Queries are provided by PensionPro and available for all users.

 

To open a saved query:

 

  1. Select either My Queries, Shared Queries, or Library Queries from the toolbar on the left. The corresponding window displays.
  2. Select a query to open it.

 

Fetch will retrieve the query and apply any existing column selections, filters, and sorting that were present when the query was last saved.

 

 

Editing Query Details

 

The Overview card contains the query's name and description, as well as the repository where it's saved. This information can be edited at any time.

 

Select anywhere in the header to expand or collapse the Overview card.

 

 

Choosing Columns for the Query

 

The Column Selections card is where users can select the fields they want to appear in their query. This card contains the "tree" of fields that are available to be selected. After the query is run, each field selected here will be returned as one column of data.

 

A selected field will be added to the Ordering & Sorts card, and can be used for Filtering; refer to those topics for more information.

 

 

Selecting Fields

 

  1. In the Column Selections card, expand the field tree to reach the desired field.
  2. Select the checkbox to the left of the field name to include it in the query.

 

 

Null Fields

 

Expanded nodes will display a plus (+) or minus (-) icon to the right of the node name. This option determines how Fetch handles null data. Select this icon to toggle the behavior.

 

Put more simply, this option decides whether a parent field is included in the returned results if it doesn't have any children. To demonstrate, consider the following simple query to return a list of Plans and Projects:

 

fetch-null-fields.png

 

In some cases, a Plan may not have any Projects launched under it. The null toggle to the right of the Projects category (the child in this example) will determine how Fetch handles this situation:

 

  • When minus (-), Fetch will disregard an entry that does not have any child data. In this example, the Plan would be omitted from the results. This is the default setting.
  • When plus (+), Fetch will include an entry lacking child data, which returns a null value. Per above, the Plan would be included in the results, but the Project Name field would be blank.

 

 

 

 

 

Ordering Columns and Sorting Query Results

 

The Ordering & Sorts card is used to refine the output of their query by reordering columns, applying sorting, and choosing to hide columns from the result if needed.

 

Select anywhere in the header to expand or collapse the Ordering & Sorts card.

 

 

Column Ordering

 

To reorder a column, use the handle to the left of the field name to drag the field up or down in the list. This order will be applied to the query results.

 

 

Sorting

 

By default, query results are returned unsorted. However, sorting can be applied to one or more fields using the icons in the Sort type\Order column.

 

Selecting the icon once for a field applies ascending (alphabetical) sorting; selecting it again switches to descending (reverse) order. Select a third time to remove sorting from that field.

 

Sorting can be applied to multiple fields. When a field is sorted, its sort order is displayed next to the icon in the Sort type\Order column; this determines how Fetch processes the sorting. For example, a query might be sorted first by Plan name, then by Project period end. Fetch applies a sort order based on the order that sorting is enabled, but this can be changed by updating the order values.

 

 

Show Column

 

The Show Column switches for each field are used to show or hide the respective columns in the query results. Fetch will show all columns by default. Hiding a column can be useful in cases where a field is used to filter results, but its data isn't needed on the generated report. For example, if the query is filtered to only show 401(k) plans, the Plan Type column is likely not needed on the report (as it will be the same for every entry).

 

 

Filtering Query Results

 

The Filtering card is used to define the criteria that determines which results are returned. Applying filters can assist in showing only the data that's relevant.

 

Select anywhere in the header to expand or collapse the Filtering card.

 

 

Add Filters

 

When a filter is set, Fetch will only return the data that matches the criteria set by that filter. Multiple filters may be used to set multiple conditions; the data must match every condition to be included in the results.

 

To add a new filter:

 

  1. If needed, select at least one field from the Column Selections card to begin constructing the query.
  2. Select Add new rule to add a blank rule to the Filtering card.
  3. Choose the Column to filter on.
    • Fetch will only apply filters to fields enabled in Column Selections. If the desired field is not listed, it hasn't been added to the query yet.
  4. Select an Operator, such as Is equal to or Contains.
  5. Enter or select the desired value.

 

The filter will be applied the next time the query is run.

 

 

Add Filter Groups

 

Filter groups are used to provide an additional level of evaluation to query data, allowing for more complex filtering rules. A record must satisfy the conditions of each filter group before it is evaluated by any remaining filters. A filter group uses either AND or OR conditionals to determine if data must satisfy one criteria, or all of them:

 

  • When OR, a record must satisfy at least one filter in the group to be returned in the query results.
  • When AND, a record must satisfy all filters in the group to be returned in the query results.

 

To add a new filter group:

 

  1. Select Add new group to add a blank rule to the Filtering card.
    • The highlighted icon to the left of the Column field indicates this rule is part of a group.
  2. Set the switch directly above the grouping to either AND or OR, depending on how the data should be evaluated.
  3. Create the filter rule as normal.
  4. To add another rule to this group, use the Add/Remove icons to the left of the grouped rules.

 

Grouping can also be applied to ungrouped filters at any time:

 

  1. Select the checkbox to the left of the filter's Column field. Repeat this step for at least one more filter.
  2. Select the Grouping icon in the grid header, next to the Column header.

 

To ungroup filters, select the highlighted icon to the left of the filter group.

 

 

 

 

 

Query Actions

 

The title bar at the top-right contains options for executing a query, saving it to a repository, and more.

 

 

Execute a Query

 

When the query is ready, use the Execute Query icon to generate the report. Fetch opens the Results page to display the data.

 

If the query needs to be edited, use the Return option at the top-left to return to the query builder and make changes; then, re-run the query.

 

To export the results in spreadsheet format:

 

  1. Select the Export icon at the top-right. The corresponding window displays.
  2. Enter a File Name for the exported file.
  3. Choose an Extension. The results can be exported to xls, xlsx, or csv file formats.
  4. Select Export. The file will download to the user's device.

 

 

Save a Query

 

The current query settings—including column selections, sorting, and filters—can be saved at any time by Selecting the Save icon at the top-right. The saved query will use the Name, Description, and Repository as entered in the Overview card.

 

 

Duplicate a Query

 

Duplicating a query can be used to make a few changes to an existing report without losing the original; this is also a great way to create a new query based off of a template, rather than building it from scratch. To duplicate a query:

 

  1. Create a new query or open a saved query. Be sure to save any changes that were made, if necessary.
  2. Select More > Duplicate from the header at the top-right. The corresponding window displays.
  3. Enter a name for the new query.
  4. Select Duplicate. Fetch will open the new query for editing.

 

 

Delete a Query

 

A saved query can be deleted if it is no longer needed:

 

  1. Open a saved query.
  2. Select More > Delete from the header at the top-right. A confirmation window displays.
  3. Select Yes, delete it to confirm.

 

 

Clearing Query Data

 

Fetch includes options to quickly clear a query's column, filtering, or sorting preferences. This will return a card to its initial state without losing the entire query.

 

  1. Create a new query or open a saved query.
  2. Open the More menu in the header at the top-right.
  3. Select Clear columns, Clear filters, or Clear order & sorts.

 

The data in the corresponding card will be reset. Take note of the following:

 

  • Clearing the Column Selections card will also clear ordering/sorts and filters.
  • Clearing the Ordering & Sorts card will only clear the sort type and sort order. Column order and hidden columns will be unaffected.

 

 

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?
    Yes. To add a field as a filter without displaying it in the report, select it in the Column Selections card, then set its Show Column switch to Off in the Ordering & Sorts card. The field will remain available for selection as a filter.
     
  • Can I control who can edit a shared query or decide which shared queries a user can see?
    At this time, shared queries can be viewed and edited by all users. The only way to control access to a query is to save it as a personal query.
     
  • Can the Fetch queries be made to look more like the reports in PensionPro? 
    Fetch cannot apply formatting to queries at this time. However, query results can be exported to Excel and formatted as desired.
     
  • I cannot find To-Dos in Column Selections. Where are they located?
    To-Dos are not currently available for reporting in Fetch.
     
  • Why can't I see Task Item values for the Projects I am reporting on?
    The only way to view answers to Task Items in a report or within Fetch is if the Task Item is tagged as a Project Field. In this case, the Project Field should be added to the query, not the Task Item. For more information, refer to the article Project Fields.