GetDataByLocation 2008/06

The GetDataByLocation stored procedure retrieves data from AVEVA™ Production Management and delivers it to an external SQL database.

Note: Windows authentication is not supported for CLR Stored Procedure.

Parameters

The GetDataByLocationV200806 Stored Procedure contains the following parameters:

• endDateLocalTime

  Specify an end query date that uses a date time format that is specified in local time.

  For example: 01 May, 2008 10:00:00 AM

• filters

  Filters is an optional parameter that specifies advanced filters to use. These filters are anything that AVEVA™ Production Management itself can produce as a filter.

  Filters are specified as a name value string segment that follows these guidelines.

  @Filters="Fieldname={filter}, fieldname={filter}

  Example:

  @Filters="Deleted={true}, StartDateTime={value 1 or value 2}, Classification={>=value3}"

  • Where filtering supports the grammar rules defined in Filtering rules.

  • Filtering syntax is case-sensitive.

  • Each module has module-specific filters; for example @GroupBy applies to Metrics only.

  • Location or ObjectID names are not to be specified in advanced filters. Use the Stored Procedure parameter to specify a location.

  • You can have any number of Filters elements specified.

    Note: When selected, string values appear inside double quotation marks, such as "Not Scheduled". These quotation marks are important, as they preserve any spaces included in the string value.

  The stored procedure is considered server-side filtering and therefore takes the "invariant" version of filtering.

  • Invariant form

  • Uses Field Name instead of Display Name

  • Uses ISO 8601-compliant UTC dates

• fields

  Fields is an optional parameter that specifies which columns to return and in what order. The fields are specified as a comma-delimited string as follows:

  @Fields=’field1, field 3, field 2’

  • Use fields where possible to limit the amount of data that comes back in the new Web services.

  • In the standard stored procedure, you are required to specify the display name. The display name is also the name of the column.

  Encapsulating field names with commas

  When using lists of field names in StoredProc calls, @fields is intended to be a comma-separated fields list.

  To avoid confusion with field names that contain a comma, enclose the field name with square brackets. For example:

  [F, Field Two]

  This is the correct method for using square brackets to enclose field names, if they contain a comma.

  @fields = N'Field One, [F, Field Two], Field Three'

• modelFields

  Model Fields is an optional parameter that specifies which model item property to return and in what order. The model fields are specified as a comma-delimited string with the dot between the field associated with the model item and the name of the property of the model item:

  @ModelFields=’modelfield1.modelproperty1, modelfield2.modelproperty2’

  Use model fields if you want to get value of the model item property (modelproperty1, modelproperty2) associated with the reporting point field (modelfield1, modelfield2).

• inclusiveDateRange

  The Inclusive Date Range parameter calculates how the Start Date and End Date are converted to a sample period.

  These are the four settings to describe the inclusive level of start and end dates:

  Setting	Description
  0 = StartAndEndIncluded	> startDateLocalTime AND < endDateLocalTime
  1 = StartIncluded (default)	>= startDateLocalTime AND < endDateLocalTime
  2 = EndIncluded	> startDateLocalTime AND <= endDateLocalTime
  3 = StartAndEndIncluded	>= startDateLocalTime AND <=endDateLocalTime

  These settings toggle whether to include the date time or not. For example, if doing a day midnight to midnight comparison you might want to use StartIncluded instead of StartAndEndIncluded.

• location

  Location is a mandatory parameter used to specify the hierarchy location for the query.

  For example: Plant.Area 1

• module

  Module is a mandatory parameter used to specify the module used in the query.

  For example: Maintenance

• samplePeriod

  Sample period is the period of time to use in the query. This can be any filterable period definition or date time.

  • SamplePeriod uses UTC time

  If you specify both start and end date times as well as a sample period, the sample period takes priority and the start/end date times are not used.

• startDateLocalTime

  Specifies the start date and time for a query, where the time is specified in local time.

  For example: 01 May, 2008 11:00:00 AM

• viewName

  This is an optional parameter. Specifies an optional view name on which to query.

  For example: Standard View

• username

  Specifies an AVEVA™ Production Management user name to use as Basic security.

• password

  Specifies a password for the username.

• serverName

  Specifies the default base URL to use for the Web services.

  Example:

  http://localhost:8889/test

  It is important that the base URL ends in a forward slash (/).

  The default endpoint is added to the default base to make a complete URL.

  Example:

  http://localhost:8889/Ampla/WebService/Data/2008/06

• session

  The session parameter enables passing the same security credentials as the user in AVEVA™ Production Management. Previously, the CLR stored procedure permitted only passing Username and Password as security parameters.

  Enter the unique identifier for the session to access credentials for that session.

  For example: 8e65edfb-e81e-4891-aefa-f61ac18ba79b

• resolveIdentifiers

  When True, the resolveIdentifiers value returns textual values for Cause and Classification.

  For example: True

• pivotResults

  Indicates that data is returned in a pivoted format if True.

  For example: True

Related topics

• CLR Stored Procedure

• Install the CLR Stored Procedure