Filters - using a cell value in a filter

To use a the value in a cell in Excel as a filter in the SQL Spreads Designer, there is a parameter you can use to fetch the value from a cell. 

The parameter is entered in the following format:
@SSCellParameter_SHEET[MySheet]_CELL[A5]  

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

In this example the user will be able to enter a min and a max value into two cells to filter which rows that should be loaded. 
  1. First, select your table in the SQL Spreads Designer and configure the columns etc.

  2. Second, in the Excel sheet, decide for two cells to use for filtering. In this example we use cell H2 for the min value and cell J2 for the max value. 


  3. Go to the Filters tab in the SQL Spreads Designer and select a new Custom SQL filter:


  4. In the filter box at the bottom, add a filter query that includes the cell parameters to fetch the values from the two cells:
      

  5.  In this example we are using the query:
    Quantity >= @SSCellParameter_SHEET[Sales]_CELL[H2] AND Quantity <= @SSCellParameter_SHEET[Sales]_CELL[J2]

    You will have to update the marked item in the query to fit your table and sheet.
    This query will get the rows from the table where Quantity is larger or equal to the value in sheet Sales, cell H2 and less or equal to the value in sheet Sales, cell J2.

  6. Click Save to save the filter and reload the rows from the database.

  7. Now, change a value in any of the filter cells and click Refresh to reload the data.


Feedback and Knowledge Base