--- title: "Create a Tree Filter for SQL Data" slug: "tree-filters-a-way-for-end-users-to-select-the-data" description: "Build data-driven Tree Filters so end users can choose which SQL Server rows load into Excel. Available in SQL Spreads Pro and above." updated: 2026-11-11T11:08:24Z published: 2026-11-11T11:08:24Z canonical: "sqlspreads.com/tree-filters-a-way-for-end-users-to-select-the-data" --- > ## Documentation Index > Fetch the complete documentation index at: https://sqlspreads.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # Create a Tree filter ## Intro to Tree Filters --- > ![SQLSpreadsProLogo.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/SQLSpreadsProLogo.png) Tree Filters are available in **SQL Spreads Pro** and above --- Tree Filters in SQL Spreads makes it possible for end users to select to import a filtered selection of the rows from the table in SQL Server. ![SQL Spreads Forecast Tree Filters displayed](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/SQL%20Spreads%207.6.3%20Forecast%20Tree%20Filters%20displayed.png) - Tree Filters are "data-driven" and generated from the data in SQL Server by entering a small SQL query that generates the tree structure from the data in the database. - Tree Filters are created per Excel workbook. Only the relevant Tree Filters for the selected worksheet will be displayed. - To apply the filter selection to your data, you will need to add a Designer filter in the SQL Spreads Designer. - When a user makes a new selection in the Tree Filter, only the data in that sheet will be reloaded. All of the other sheets will remain unchanged. ### Video - Tree Filters: How End-users can filter data loaded from SQL Server [![Watch video: The power of Tree Filters, how end users can filter SQL Server data in Excel](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Tree%20Filters%20-%20a%20way%20for%20end%20users%20to%20filter%20the%20data%20loaded%20from%20SQL%20Server%20-%20with%20play%20icon%20-%20600px%20wide%281%29.png)](https://www.youtube.com/watch?v=HjxlJuqDRb8?rel=0) ## Creating a new Tree Filter To create a new Tree Filter: 1. In the SQL Spreads tab in Excel, open Design mode and click the **Setup Tree Filters** button: ![SQL Spreads Click on Setup Tree Filters button](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/SQL%20Spreads%207.6.3%20Click%20on%20Setup%20Tree%20Filters%20button.png) 2. The **Setup Tree Filters** dialog will be shown. Click **New** to bring up the **Edit Import Filter** dialog. ![edit import filter.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/edit%20import%20filter.png) 3. Enter your filter SQL query for generating the filter items. You can test run your query by clicking the **Execute SQL** button. The Tree Filter SQL query must return two columns; **Text** and **Code** where Text is the filter items shown to the user and the Code can be used as a key value when filtering the rows to load from the database. ![Edit Tree Filter.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Edit%20Tree%20Filter.png) 4. Click **OK** to go back to the **Manage Filter** dialog and then click **OK** again. 5. The Filter Selection Panel will be shown and the items in your new filter will be loaded. Each separate filter will be shown as a top node in the Filter Selection Panel. If you create several filters, the user has to select one item in each filter to import the data. ![Created Tree Filter.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Created%20Tree%20Filter.png) [![Watch video: The power of Tree Filters, how end users can filter SQL Server data in Excel](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/WatchVideoButtonWhite%202%20-%20Red.svg)](https://www.youtube.com/watch?v=HjxlJuqDRb8&start=35) --- ### Filter the data based on the selection in the Tree Filter To filter the data loaded from SQL Server you will have to add a Designer filter in SQL Spreads. This filter limit the number of loaded rowsbased on the selection in the Tree Filter. 1. Create your Tree filter using the steps above. Your filter items will be shown in the left side panel in Excel. ![2filters.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/2filters.png) 2. Go to the **Filters** tab in the SQL Spreads Designer: ![image.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/image%2874%29.png) 3. Create a new **Custom SQL filter**: ![CustomSQLFilter.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/CustomSQLFilter.png) 4. In the **Custom SQL Filter** field at the bottom, add a filter query in the same format as a WHERE clause in a standard SQL query. To get the parameters containing the selected values in the Tree filter, click the **Quick Add** link... ![image.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/image%28185%29.png) 5. ... and select the Tree Filter parameter to use: ![image.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/image%28154%29.png) 6. Test your filter by selecting one of the Tree Filter selections on the left side panel and make sure that your specifically filtered data is being loaded correctly. [![Watch video: The power of Tree Filters, how end users can filter SQL Server data in Excel](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/WatchVideoButtonWhite%202%20-%20Red.svg)](https://www.youtube.com/watch?v=HjxlJuqDRb8&start=98) **All Tree Filters will be visible when the SQL Spreads Designer is open**. The active Tree Filters for that particular sheet will be enabled while the rest are grayed out. This is useful for the Designer when setting up the document. Once the Designer is closed, only the active Tree Filters for that particular sheet will be visible. A Data Editor does not have access to the SQL Spreads Designer. Therefore, they will only ever see the active Tree Filters for the specific sheet that they have selected. #### Naming of the Tree Filter parameters For each Tree Filter there will be two parameters available to use in your filter query; the Text shown in the Tree filter and a Code parameter that can be used to store a key value that is not visible to the user. To quickly insert a Tree Filter parameter or a Cell parameter, click the **Quick Add** button above the filter text area: ![image.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/image%28151%29.png) Each filter's parameters are named with the **Filter Name** plus “_Code” and “_Text”, eg for a Tree filter named **Region** the parameters will be: ``` @Region_Code @Region_Text ``` ![image.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/image%28251%29.png) --- ### Filter Settings #### Expand at Startup To automatically expand the values in a filter when the filter is loaded, click the **Settings** button, then the **Setup Filters** button. Click **New** or **Edit** and then check the **Expanded Filter at Startup** at the bottom of the dialog. ![Expand Filter At Start.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Expand%20Filter%20At%20Start.png) #### Filter Ordering When using multiple Filters you can order the filters in the filter pane by changing the order of the Filters in the **Setup Tree Filters** dialog. To change the order of a filter, select the filter and click the **Move Up** or **Move Down** button. ![Filter Ordering.png](https://cdn.document360.io/ad9d65b3-9cdd-4b28-8cb8-86cb149873bc/Images/Documentation/Filter%20Ordering.png)