`<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.

example:

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.

Note:

Parent Elements
`<parameters>` (§3.13.7)

Attributes

Description

<boolean> (Boolean)

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> (Cell Reference)

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).

<double> (Double)

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> (Integer)

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.

<name> (Parameter Name)

The name of the parameter.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<parameterType> (Parameter Type)

Type of parameter used. If the @parameterType=value, then the value from @boolean, @double, @integer, or @string will be used. In this case, it is expected that only one of {@boolean, @double, @integer, or @string} will be specified.

The possible values for this attribute are defined by the ST_ParameterType simple type (§3.18.56).

<prompt> (Parameter Prompt String)

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).

<refreshOnChange> (Refresh on Change)

Flag indicating whether the query should automatically refresh when the contents of a cell that provides the parameter value changes. If true, then external data is refreshed using the new parameter value every time there's a change. If false, then external data is only refreshed when requested by the user, or some other event triggers refresh (e.g., workbook opened).

The possible values for this attribute are defined by the XML Schema boolean datatype.

<sqlType> (SQL Data Type)

SQL data type of the parameter. Only valid for ODBC sources.

Supported values include:

-22	SQL_UNSIGNED_OFFSET
-20	SQL_SIGNED_OFFSET
-11	SQL_GUID
-10	SQL_WLONGVARCHAR
-9	SQL_WVARCHAR
-8	SQL_WCHAR
-7	SQL_BIT
-6	SQL_TINYINT
-5	SQL_BIGINT
-4	SQL_LONGVARBINARY
-3	SQL_VARBINARY
-2	SQL_BINARY
-1	SQL_LONGVARCHAR
0	SQL_UNKNOWN_TYPE
1	SQL_CHAR
2	SQL_NUMERIC
3	SQL_DECIMAL
4	SQL_INTEGER
5	SQL_SMALLINT
6	SQL_FLOAT
7	SQL_REAL
8	SQL_DOUBLE
9	SQL_TYPE_DATE or SQL_DATE
10	SQL_TYPE_TIME or SQL_TIME
11	SQL_TYPE_TIMESTAMP or SQL_TIMESTAMP
12	SQL_VARCHAR
101	SQL_INTERVAL_YEAR
102	SQL_INTERVAL_MONTH
103	SQL_INTERVAL_DAY
104	SQL_INTERVAL_HOUR
105	SQL_INTERVAL_MINUTE
106	SQL_INTERVAL_SECOND
107	SQL_INTERVAL_YEAR_TO_MONTH
108	SQL_INTERVAL_DAY_TO_HOUR
109	SQL_INTERVAL_DAY_TO_MINUTE
110	SQL_INTERVAL_DAY_TO_SECOND
111	SQL_INTERVAL_HOUR_TO_MINUTE
112	SQL_INTERVAL_HOUR_TO_SECOND
113	SQL_INTERVAL_MINUTE_TO_SECOND

The possible values for this attribute are defined by the XML Schema int datatype.

<string> (String)

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>

<parameter> (Parameter Properties)

`<parameter>` (Parameter Properties)