Excel Add-In for Microsoft Dynamics 365

Build 22.0.8462

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 GoalHeadingId, GoalHeadingId FROM GoalHeadings WHERE Name <> @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, GoalHeadingId is an optional search parameter, but Name must be specified.

SELECT * FROM GoalHeadings WHERE Name <> @InputSheet!A2 AND GoalHeadingId = @@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 Microsoft Dynamics 365 column contains the cell value. For example if the referenced cell has the value 'MyAccount', then the equivalent SQL predicate is:

LIKE '%MyAccount%'

You can execute starts-with and ends-with searches by adding a '%' to the cell value:

InputSheet!A2 Cell Query Equivalent Predicate
'MyAccount' SELECT * FROM GoalHeadings WHERE Name LIKE @InputSheet!A2 LIKE '%MyAccount%'
'%MyAccount' SELECT * FROM GoalHeadings WHERE Name LIKE @InputSheet!A2 LIKE '%MyAccount'
'MyAccount%' SELECT * FROM GoalHeadings WHERE Name LIKE @InputSheet!A2 LIKE 'MyAccount%'

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462