Filtering rows from SQL Server

Intro to Designer filters

Designer Filters decide which rows in the database to load into Excel. You can create a single filter or a combination of several filters.

Adding a Column filter

A Column filter lets you filter the fetched rows based on the value in one or several columns.

Column filters have a pre-defined set of conditions to quickly setup a new filter.

Adding a Custom SQL filter

A custom SQL filter gives you full flexiblilty but requires that you enter your filter using a SQL query:

Using a sub-query in a filter

A common way to find a specific row or a smaller subset of rows is to use what is called a sub query in SQL. Below are a few examples for how to use a sub query in a filter.

Get the last row based on a date

This example shows how to use a sub query in a Custom SQL filter to get the last hired employee from a human resource table. The sub query first finds the last hired date in the table and then uses that date to find the last hired employee:

HireDate = (
SELECT 
    TOP 1 HireDate 
FROM 
    AdventureWorks2016.HumanResources.Employee 
ORDER BY 
    HireDate DESC
)

Restrict row access based on the current user - fixed setup

This example shows how to use a sub query in a Custom SQL filter to give different users access to specific regions. The @WindowsUser parameter is a SQL Spreads parameter that holds the current user's Windows Login and is used to filter out specific regions for each user.

RegionKey = 
(
    SELECT CASE 
        WHEN @WindowsUser = 'DOMAIN\USER1' THEN 1 -- User 1 will see the rows in the Region with the key 1
        WHEN @WindowsUser = 'DOMAIN\USER2' THEN 2 -- User 2 will see the rows in the Region with the key 2
        WHEN @WindowsUser = 'DOMAIN\USER3' THEN 3 -- User 3 will see the rows in the Region with the key 3 
    END 
)

Restrict row access based on the current user - dynamic setup

The example above uses a fixed setup that will be stored in the SQL Spreads Excel document. If you would like to have a more dynamic solution where the user access can be more easily maintained, there is an example here for how to create the User Access table.

With the User Access table in place you will get a dynamic setup where you can control the user access from a SQL Server table that can be easily maintained using a SQL Spreads document.

To add the dynamic user access to a SQL Spreads document, first create the user access table as in this example and then add the Custom SQL filter below in the SQL Spreads Designer:

RegionKey IN 
(
    SELECT
        Code
    FROM
        [SQLSpreadsDemoDW].[dbo].[meta_UserAccess]
    WHERE 
        [TableToAccess] = 'Region'    -- This field allows us to use the same access table for several tables
        AND [UserLogin] =  @WindowsUser -- This SQL Spreads parameter will hold the current user's Windows login
)

"Ambiguous column..." error when using a Custom SQL Filter

When using a Custom SQL Filter you may get a SQL error message saying "Ambiguous column name '[Database Column]'":

To avoid this error, you will need to add the name of the table before the column name. The easiest way to do that is to click the Insert link button above the filter field...

...and then select your database fields from the list in the Database Columns tab:

Using a cell value in a filter

To use the value from a cell in Excel in a filter, click the Insert link button above the filter field:

Then select Cell value tab and your sheet in the drop-down list and enter the cell reference:

You can also use a Named Range in Excel...

...as the cell reference in a Cell parameter:

The CellParameter format

The parameter has the following format:

@SSCellParameter_SHEET[MySheet]_CELL[A5] 

where MySheet is the worksheet where to fetch the value and A5 is the cell reference.

Removing a filter

To delete a filter, click the Trash can symbol on the right side in the filter list.

Preview of 50 rows in the SQL Spreads Designer

When the SQL Spreads Designer is opened, SQL Spreads will preview the first 50 rows in the table.

You can turn off the Preview and also see the total number of rows in the table at the top of the Filter tab:

To turn off the preview in the SQL Spreads Designer and show all rows, click the Show all rows checkbox.

When you close the Design mode and click Refresh, all rows from the table will be loaded into Excel.