Excel Functions
Excel Functions
The CData Connect Spreadsheets add-in for Excel includes support for several Excel functions. A function is a preset formula that helps perform different operations and data analysis faster. You can use these functions to execute stored procedures.
Functions perform specific calculations in a particular order based on the specified values, called arguments or parameters. Function parameters can refer to specific cells; as these cell values change, the add-in automatically refreshes the results.
Since Excel functions are stored within the Excel file, you can easily share your dynamic spreadsheets with others. In addition, they eliminate time-consuming manual entry of formulas. You can perform your tasks efficiently and save time.
CDATAQUERY
=CDATAQUERY(query, connection, [defaultCatalog], [defaultSchema], [paramsRange] [options])
It contains the following parameters:
| Parameter | Description |
| query | A string containing the SQL query (or other statement) to execute. This should be in quotes. For example: |
"SELECT * FROM [MailChimp2].[MailChimp].[Automations]" |
|
| connection | A string containing the complete connection string or profile name. This is in quotes. For example: |
"Connection=MailChimp2,Schema=MailChimp,Table=Automations" |
|
| [defaultCatalog] | A string containing the default catalog to use. This can be left blank. |
| [defaultSchema] | A string containing the default schema to use. This can be left blank. |
| [paramsRange] | The Parameters argument specifies the range of cells that contains the names and values for the parameters. The first row of the range is a header row and specifies the parameter names. For example, if the range A10:B11 contains the parameters, then the cells A10:B10 should contain the parameter names, and the cells A11:B11 should contain the parameter values. |
| [options] | A string containing additional options to control the formula’s behavior, such as “header=false” to return the data without headers. |
The following is an example of a complete CDATAQUERY function:
=CDATAQUERY("SELECT * FROM [SQLServer1].[dbo].[ImportantDataTypes]", "Connection=SQLServer1,Schema=dbo,Table=ImportantDataTypes", "", "", A1:F2)
The results appear just below the cell where the formula was entered.