Tree filters - a way for end users to select the data

Intro to Tree Filters
Creating a new Tree Filter
Filter the data based on the selected Tree filter items
Create multi-level (hierarchical) Tree Filters
Filter Settings
Using Tree Filters in the Advanced setup

Intro to Tree Filters


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:
  • Tree Filters are "data-driven" and generated from the data in SQL Server.

  • Tree Filters are created by entering a small SQL query that generates the tree structure from the data in the database.

  • To apply the filter selection to your data, you will need to add a row filter in the SQL Spreads Designer.

  • When a new selection is done in the Tree Filter, the data in the sheet will be reloaded.

Creating a new Tree Filter


To create a new Tree Filter:

  1. In the SQL Spreads tab in Excel, open the Advanced Setup menu and click the Tree/Database Import Filter button.



  2. The Manage Import Filters dialog will be shown. Click New to bring up the Edit Import Filter dialog.


  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. 


  4. Click OK to go back to the Mange 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.


     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.

Filter the data based on the selected Tree filter items


To use the selection in the Tree Filter to filter the data that is loaded from SQL Server, you will have to add a new filter in the SQL Spreads Designer. 

Follow these steps to use the Tree Filter in the SQL Spreads Designer:
  1. Create your filter using the Advanced Setup > Database Import Filter selection
      

  2. Your filter items will be shown in the left side panel in Excel.

  3. To add the Tree Filter to the SQL Spreads Designer, first select one item in each of your filters. 
    In the example below you must select both a Region item and a YearMonth item:


  4. Open the SQL Spreads Designer and select your table. When the table is loaded go to the Filters tab and create a new Custom SQL filter:


  5. In the text box at the bottom, add a filter query in the same format as a WHERE clause in a standard SQL query.

    For each Tree Filter there will be two parameters available to use in your query. Each filter's parameters are named with the filter name plus “_Code” and “_Text”, eg @Company_Code and @Company_Text. 

    In the example below only the table rows will be loaded where the table columns RegionID and YearMonthKey match the selected items in the two filters Region and YearMonth:
NOTE: Before adding the filter in the SQL Spreads Designer, first selecting one item in each of your filters as mentioned in step 3 above.

Create multi-level(hierarchical) Tree Filters


SQL Spreads can create a multi-level Tree Filters with up to five levels.

The user always selects the item at the lowest level, like the City in the example below:

To add a new level in a Tree Filter add a new column in the filter SQL query and name it with “AS Group1” as in the example below:
SELECT
   city.[CityName] AS [Text],
   city.[Id] AS Code,  
   country.[Name] AS Group1,  -- Show country
  'true' Group1Expanded    -- Always expand the countries
FROM
  [dbo].[Cities] AS city
INNER JOIN
  [dbo].[Countries] as country ON city.CountryCode = country.CountryCode  

You can define up to five levels by naming columns to Group1 to Group5:
Group1
      Group2  
            Group3 
                 Group4  
                        Group5
                               Text, Code

Collapse or expand multi-level Tree Filters


You can control if the group should be collapsed or expanded by default by adding a column named “AS GroupXExpanded” to the result set with a value of either 'true' or 'false'. 

The column should be named “AS GroupXExpanded” where X is the number of the group to expand/collapse eg:
'true' AS Group1Expanded to expand the level Group1.

Note: Please note that you must name the columns using AS, eg “ AS Group1” or “ AS Group1Expanded”, otherwise SQL Spreads will not detect the filter group.

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 Expand Filter at Startup at the bottom of the dialog.

Filter Ordering
When using multiple Filters you can order the filters in the filter pane by changing the order of the Filters in the Manage Import Filters dialog. To change the order of a filter, select the filter and click the Move Up or Move Down button.

Using Tree Filters in the Advanced setup

Using the selected filter values as parameters in a Database Import


When a user selects a new item in the filter tree-view, SQL Spreads will re-run the Database Import queries and do a refresh of the data in Excel.

By using the selected filter values as
parameters in the Database Import SQL queries, the imported data in the spreadsheet will reflect the selected filter items.

A Filter Item is used as a parameter in a SQL query in the format:
@FilterName_Text or 
@FilterName_Code

The @FilterName_Text parameter will hold the selecteds Text column in the Import Filter query, and the @FilterName_Code parameter will hold the selected item's Code column in the Import Filter query.    

To quickly insert the selected filter value into the Database Import SQL, the filters are available in a Drop Down list in the top-right corner of the Database Import dialog.
Select a value in the drop-down List and then click the Insert Parameter button to insert the selected filter item as a parameter in the SQL Query.

Insert the selected Tree Filter items into new rows

If you are using the Advanced Setup you can setup your Database Mapping so the values from the selected filters are inserted into new rows when writing data back to SQL Server.

You can include multiple key values from different filters into the Code field by separate them using a comma. The comma separated keys will then show up in the Database Mapping drop-down list as Part1, Part2 etc. which contains the values in the comma separated Code value:

Feedback and Knowledge Base