Writing Parameterized Queries
You can create dynamic spreadsheets by referencing cells in your SQL statement. The add-in executes the statement as a parameterized query. As you change the values in the sheet the add-in executes a new query.
Creating Searchable Spreadsheets
Use a parameterized SELECT WHERE query to create a spreadsheet that dynamically filters the data as you edit cells. The following filter criteria references the @InputSheet!A2 parameter -- cell A2 of the sheet InputSheet.
SELECT Id, Name FROM [CData].[Elasticsearch].Employee WHERE Industry = @InputSheet!A2
Defining Optional and Required Parameters
When you prefix a parameter with @@, empty values in the referenced cell do not affect the query (i.e. any condition that refers to a cell with an empty value is removed).
In the following query, Name is an optional search parameter, but Industry must be specified.
SELECT * FROM [CData].[Elasticsearch].Employee WHERE Industry = @InputSheet!A2 AND Name = @@InputSheet!A3
Dynamic Table References
You can dynamically reference a table in a query by prefixing the cell reference with @@@. The following query will read the value from cell A1 on the InputSheet sheet and use that for the table name when executing the request.
SELECT * FROM @@@InputSheet!A1
Writing Other Search Conditions
By default, the add-in reports a match if the Elasticsearch column contains the cell value. For example if the referenced cell has the value 'Floppy Disks', then the equivalent SQL predicate is:
LIKE '%Floppy Disks%'
You can execute starts-with and ends-with searches by adding a '%' to the cell value:
InputSheet!A2 Cell | Query | Equivalent Predicate |
'Floppy Disks' | SELECT * FROM [CData].[Elasticsearch].Employee WHERE Industry LIKE @InputSheet!A2 | LIKE '%Floppy Disks%' |
'%Floppy Disks' | SELECT * FROM [CData].[Elasticsearch].Employee WHERE Industry LIKE @InputSheet!A2 | LIKE '%Floppy Disks' |
'Floppy Disks%' | SELECT * FROM [CData].[Elasticsearch].Employee WHERE Industry LIKE @InputSheet!A2 | LIKE 'Floppy Disks%' |