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