Adding filters & Lists to your reports

  • Filters are applied to tables and functions. In this article we refer to tables, but the same applies to functions.
  • Filters can operate at the local level (from a singular data table) and global levels (across multiple tables with the same field) in the Excel Add-in.
  • Filters can be based on one or more fields.

Unlock the secrets of Datarails! Dive into our Online University and transform your learning journey today!

 

The Difference Between Local and Global Filters

Filter Description
Local Contains one or more fields from a single table or function.
Global Contains one or more fields from one or more tables.
A global filter will filter other related fields.
Example: A global filter on the 'Region' field set to 'North', will filter the 'City' field, showing cities in the northern region.

Use Case

If you have your financial reports such as a income statement with each department included. If you want to see each department, you can duplicate the report multiple times to different tabs OR with Filters, you can consolidate all the departments together in one tab and then add a filter for a specific department. This is a more streamlined workflow. 

Types

You can apply both local and global filters.

Filter Description
Local Can filter a table (or function). Uses one or more fields from a single table.
Global Can filter a table (or function). Uses one or more fields from one or more tables. The global filter will filter related fields, as in the example of a global filter on 'Region' that filters related cities from the 'Cities' field (or vice-versa).

Adding a local Filters

  1. Make sure you are logged into the Excel Add-in.
  2. Check also that the workbook includes a Datarails function or an activated Datarails table.
  3. From the Datarails ribbon, select Filters > Manager Filters. The Filter dialog box opens.
  4. Click Add, another dialog box opens.
    2022-01-28_10h06_52.png

    Table: Select the table.
    Scope: Select Table or Function.
    Name: Name the filter.
    Address: Enter the cell address where you want to place the filter.

  5. Click OK. The Filter name appears in the Datarails ribbon.

How to add a global Filter

  1. Make sure you are logged into the Excel Add-in.
  2. Check also that the workbook includes a Datarails function or an activated Datarails table.
  3. From the Datarails ribbon, select Filters > Global Filters.
  4. Click Add.
    2022-01-28_10h10_18.png

  5. Complete the dialog box:
    • Name: Name the filter.
    • Field Type: Select the correct field type from the dropdown. If the type is not correct for the selected field, the filter will not work, i.e., a date type for a date field.
    • Tables / Fields: Use the search box to help you locate the table. Then select one or more fields to create the global filter. Drag them to the Fields section.
    • At the bottom of the dialog box, click Add. The message 'Filter Saved' appears on the right.
  6. In the Filters pane, click the green check box next to the Filter Name on the left.
    • The filter is added to the worksheet and the Filter Name is purple. It is also added to the DataRails ribbon.
  7. Place the filter in a location that's convenient for you. The Filter name also appears in the DataRails ribbon.
  8. To refine the filter, click the Filter name in the DataRails ribbon. A dialog box opens.

2022-01-28_10h14_47.png

    • Refine the filter as needed.
    • By default, nulls are excluded. If you want to include them, check the box.
    • Notice that the Add to Global Filters check box is already selected.

9. When you have the required selection, click OK. The data will now be filtered.

Lists

Lists are drawn directly from a field within a Datarails table. A list is a dropdown similar to an Excel data validation list. A list is useful when you want to reference a specific selection in a function in your workbook or only want to update a specific selection with an individual tab whereas a filter would affect the entire workbook. 

A Datarails List is different from a Datarails Filter because:

  • a list doesn't filter
  • you can only select a single option from the list

Adding a list

  1. From the Datarails ribbon, select Filters > Manage Lists.
  2. To create a list, click Add. A dialog box opens.
  3. Select the data table that has the field for the List.
    • Select the field.
    • Name the List.
    • In the Address box, enter the cell reference for the list.
  4. Click OK. The List appears in the Datarails ribbon.

If the list exists, you can select the list from the Datarails ribbon Filter > Lists.



© Datarails Ltd. All rights reserved.

Updated

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.