Black Box ICOMP, User Manual

Page: 62 / 92

Copyright (c) 2009 - 2012 Black Box Corporation
724-746-5500 | blackbox.com 62
iCompel Data Engine - User Guide (V3.1.2)
7. Using the Excel Connector
This section covers advanced topics related to the use of the Microsoft
®
Excel
®
Connector.
7.1 Advanced Excel Settings
Overview
The iCompel Data Engine Microsoft
®
Excel
®
Connector uses a dialect of SQL to specify the data to retrieve. The
exact query issued can be finely controlled using the Advanced Query settings from the Excel page.
On the Excel page, click the Advanced Query button to modify the settings. A summary of the current settings is
shown alongside the Advanced Query button.
The following parts of the SQL query can be specified:
SELECT
WHERE
GROUP BY
HAVING
ORDER BY
The FROM part is is specified by the Cell Range field.
The contents of the SELECT field will be processed to ensure field names are surrounded by square brackets.
Surround the SELECT field entry with double quotes to avoid this processing and use the string exactly as entered.
If field names are referenced by any field, these must exactly match the field names in the first row (if first row is data
header is enabled) or must follow the format
Fn
, where n is the one based column number of the field within the
worksheet.
If field names contain blank spaces, the field name must be surrounded by square brackets when referenced.
If the field name is incorrectly specified, then the error '
No value given for one or more parameters
' is likely to
be returned.
Advanced Query Example
Display the number of celebrities born in London.
The spreadsheet is replicated in entirety below:
Name BirthCity
A A Milne London
Abi Titmuss London
Ainsley Harriott London