Create a new Database Import Filter
Using tree-filters in the SQL Spreads Designer
Using the selected filter values as parameters in a database query
Create hierarchical(multi-level) Database Import Filters
Database Filters in SQL Spreads makes it possible to import a filtered selection of rows from a table in SQL Server. You can also use the import filter to give a user access to only a selection of the rows in a table.
- Database Filters are created by entering a SQL query that generates the tree structure seen in the image above.
- In the Filter Panel, the text in each top node (Region and YearMonth in the image above) is the name of each filter.
- When a Filter Item is selected, the import data will be reloaded and filtered using the selected Filter Value.
- Database Import Filters can only be used for manually created Database Imports.
NOTE: When exporting data from Excel to SQL Server, you can let SQL Spreads insert the value from a selected filter into a column in the database table.
To insert the value of the selected filter into a column in a table, select the filter name from the Drop Down List used to map your SQL Server table columns in the 'Edit Database Export' dialog.
To create a new Database Import Filter:
- In the SQL Spreads tab in Excel, open the Advanced Setup menu and click the Database Import Filter button.
- The Manage Import Filters will be shown, click New to bring up the Edit Import Filter dialog.
- Enter your filter SQL query for generating the filter items.
- 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.
Tree-filters can also be used to filter data in a setup created with 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 use the tree-filter in the SQL Spreads Designer, you will have to 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 either “_Code” and “_Text”, eg @Region_Code and @Region_Text.
In the example below only table rows where the table columns RegionID and YearMonthKey match the selected items in the two filters Region and YearMonth will be fetched from the database into Excel:
NOTE: Before adding the filter in the Designer, always start with selecting one item in each of your filters as mentioned in step 3.
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 selected's 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.
The user always selects the item at the lowest level, like the City in the example below:
You can control if the group should be collapsed or expanded by default by adding a column 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:
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.
country.[Name] AS Group1, -- Show country
'true' Group1Expanded , -- Always expand the countries
city.[CityName] AS [Text] , -- Show City name
CAST ( city.[Id] AS VARCHAR ) + ',' + country.CountryCode AS Code -- The Code has the format "CityId,CountryCode"
[dbo].[Cities] AS city
[dbo].[Countries] as country ON city.CountryCode = country.CountryCode
First, we create the filter for selecting customer gender:
We start with creating a SQL Query that will return a list of the two gender items 'Female' and 'Male'. In the database, the gender column contains the values 'F' or 'M' so our filter will need to show Female or Male to the user, but use F or M as parameters when filtering the import data.
When creating a filter, you decide which column that should be shown as text and which should be shown as code by adding the names 'AS TEXT' or AS CODE' to the resulting columns. Our query will look like this:
SELECT 'F' AS Code, 'Female' AS Text UNION
SELECT 'M' AS Code, 'Male' AS Text
Click the Setup Filters button in the SQL Spreads tab in Excel, and then click New. Give the filter the name Gender, and add the query and test it by clicking the Execute SQL button.
Click OK and then click New to create our second filter.
Now, we will move up one step in difficulty level and create a SQL Query that will populate our filter pane with the decades that our customers were born in. The query takes the BirthDate and uses the SQL method YEAR() to get the Birth Year. The Birth Year is then divided by ten and multiplied by ten to remove the year value and get only the decade. We would like the text in the filter to be in the format 'From 1960 to 1969'.
Our query would then looks like this:
'From ' + CAST
(YEAR([BirthDate])/10*10 AS VARCHAR) + ' to ' +
CAST(YEAR([BirthDate])/10*10 + 9 AS VARCHAR) AS Text
,YEAR([BirthDate])/10*10 AS Code
ORDER BY YEAR([BirthDate])/10*10
Add the query to the Filter and name the filter BirthYear.
The last thing we have to do is to add a WHERE clause to our Database Import query.
Click Database Import, select your import and click Edit. Now add a filter statement to the end of the query:
YEAR([BirthDate])/10*10 = @BirthYear_Code AND
Gender = @Gender_Code
Click OK twice and then select a value in the filter pane for both BirthYear and Gender. The data in Excel will be refreshed from the SQL Server table and filtered based on the selected filter items
NOTE: When updating your Database Import query, you will see your available filters in the Drop Down list in the upper right corner in the Database Import dialog. To insert a filter as a parameter, place the cursor in your SQL query, select the parameter in the list and click the
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.