<parameter>
(Parameter Properties)
This element stores properties about any parameters used with external data connections. Parameters are used to change the query executed externally and cause different data to be retrieved into the workbook. The type of parameter used – see <ST_parameterType>
(§3.18.56) – determines whether the user will be prompted for a value before data is refreshed, or the value will be pulled from a cell in the workbook, or whether the same value should be used until explicitly changed in the data connection. Parameters are valid for ODBC and web queries.
Data connectivity can use a number of different technologies. The following is an example of XML defining a connection to a Microsoft Access database, with a <parameter>
based on the value in cell C1 on the first sheet.
<connection id="1" name="Connection" type="1" refreshedVersion="2" background="1" saveData="1"> <dbPr connection="DSN=MS Access Database;DBQ=C:\Desktop\db1.mdb;DefaultDir=C:\Desktop;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" command="SELECT Table1.Field1, Table1.Field2_x000d__x000a_FROM `C:\Desktop\db1`.Table1 Table1_x000d__x000a_WHERE (Table1.Field2=?)"/> <parameters count="1"> <parameter name="user specified value" sqlType="4" parameterType="cell" cell="Sheet1!$C$1"/> </parameters> </connection>
Note that the @command
string in the <dbPr>
element contains a "?" character. This character serves as a parameter marker.
Parent Elements |
---|
|
Attributes |
Description |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Boolean value to use as the query parameter. Used only when parameterType = value. The possible values for this attribute are defined by the XML Schema boolean datatype. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Cell reference indicating which cell's value to use for the query parameter. Used only when parameterType = cell. <Parameter parameterType="cell" cell="Sheet1!$C$1"> The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96). |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Non-integer numeric value to use as the query parameter. Used only when parameterType = value. The possible values for this attribute are defined by the XML Schema double datatype. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Integer value to use as the query parameter. Used when parameterType = value. The possible values for this attribute are defined by the XML Schema int datatype. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
The name of the parameter. The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96). |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Type of parameter used. If the The possible values for this attribute are defined by the ST_ParameterType simple type (§3.18.56). |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Prompt string for the parameter. Presented to the spreadsheet user along with input UI to collect the parameter value before refreshing the external data. Used only when parameterType = prompt. The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96). |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Flag indicating whether the query should automatically refresh when the contents of a cell that provides the parameter value changes. If The possible values for this attribute are defined by the XML Schema boolean datatype. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
SQL data type of the parameter. Only valid for ODBC sources. Supported values include:
The possible values for this attribute are defined by the XML Schema int datatype. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
String value to use as the query parameter. Used only when parameterType = value. The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96). |
The following XML Schema fragment defines the contents of this element:
<complexType name="CT_Parameter">
<attribute name="name" use="optional" type="ST_Xstring"/>
<attribute name="sqlType" use="optional" type="xsd:int" default="0"/>
<attribute name="parameterType" use="optional" type="ST_ParameterType" default="prompt"/>
<attribute name="refreshOnChange" use="optional" type="xsd:boolean" default="false"/>
<attribute name="prompt" use="optional" type="ST_Xstring"/>
<attribute name="boolean" use="optional" type="xsd:boolean"/>
<attribute name="double" use="optional" type="xsd:double"/>
<attribute name="integer" use="optional" type="xsd:int"/>
<attribute name="string" use="optional" type="ST_Xstring"/>
<attribute name="cell" use="optional" type="ST_Xstring"/>
</complexType>