[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

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

end example]

Note that the command string in the dbPr element contains a "?" character. This character serves as a parameter marker.

[Note: Data connectivity can use a number of different technologies. One example of potential values stored in this attribute can be found at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcstatement_parameters.asp end note]

Parent Elements

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

 

[Example:

 

<Parameter parameterType="cell" cell="Sheet1!$C$1">

 

end example]

 

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>