Working with Database Import Filters

Database Import Filters
Create a new Database Import Filter
Using the selected filter values as parameters in a database query
Filter Example 
Filter Settings

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. 

Create a new Database Import Filter


To create a new Database Import Filter:

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


    Note: To enable the Advanced Setup menu, go to Document Settings > Other Settings tab and check the Show Advanced Setup menu checkbox.  

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


  3. Enter your filter SQL query for generating the filter items.


  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.

Using the selected filter values as parameters in a database query


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 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. 


Filter Example

In this example, we work with the data in a DimCustomer table which contains several hundred thousand rows of data. To improve loading times we would like to filter the imported data based on gender and birth year.

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:
 
SELECT 
DISTINCT 
'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   
FROM [AdventureWorksDW2012].[dbo].[DimCustomer]
ORDER BY YEAR([BirthDate])/10*10      

Add the query to the Filter and name the filter BirthYear.


Click OK and then OK again. Our new filter values will load into the filter pane:


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:

WHERE 
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 Insert Parameter button.

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.

Feedback and Knowledge Base