Creating a new Tree Filter
Filter the data based on the selected Tree filter items
Create multi-level (hierarchical) Tree Filters
Using Tree Filters in the Advanced setup
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.
To create a new Tree Filter:
- In the SQL Spreads tab in Excel, open the Advanced Setup menu and click the Tree/Database Import Filter button.
- The Manage Import Filters dialog will be shown. Click New to bring up the Edit Import Filter dialog.
- 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.
- Click OK to go back to the Mange Filter dialog and then click OK again
- 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.
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.
- Create your filter using the Advanced Setup > Database Import Filter selection
- Your filter items will be shown in the left side panel in Excel.
- 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:
- 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:
- 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.
The user always selects the item at the lowest level, like the City in the example below:
city.[CityName] AS [Text],
city.[Id] AS Code,
country.[Name] AS Group1, -- Show country
'true' Group1Expanded -- Always expand the countries
[dbo].[Cities] AS city
[dbo].[Countries] as country ON city.CountryCode = country.CountryCode
Collapse or expand multi-level Tree Filters
'true' AS Group1Expandedto 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.
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.
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.
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:
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.