SQL API Stored Procedures

The SQL API is a collection of SQL Server stored procedures that can read Process+ data. The API provides access to following types of Process+ data

  • The interim tag data for TagPV, TagSP, TagMV, TagControlMode, DeltaLowerLimit and DeltaUpperLimit. This interim data can be used to populate trends for the specified tags up to the last 30 days of calculated Process+ data.

  • Histogram data for Error distribution (PV - SP) and TagMV distribution in the form of bin indexes and their paired bin values. A simple vertical bar graph with bin index as the x value and bin value as the y value can make use of this data.

  • Grouped Process+ KPI data. For more information on Process+ KPIs, refer to Understanding Process+ KPIs.

  • Process+ Shift performance data.

Important Concepts and Definitions used in the SQL API

  • Entities: An entity defines a generic object type that can have properties.  In Process+, all Control Loops are entities.

  • Entity Class: An Entity Class defines a specific type of object and the properties that the object may have.  In Process+ there is a single Entity Class definition for a Control Loop that also defines all the properties and KPI’s that a Control Loop will expose (see Entity Class Properties).

  • Entity Class Properties: An Entity Class Property belongs to a specific Entity Class and may represent an attribute of the Entity Class or KPI definition for a specific Entity Class.

  • Entity Instance: Entity Instances are instances of an Entity Class. In Process+, all Control Loops are Entity Instances of the Control Loop Entity Class.

  • Schedules: When deploying Process+ Action Objects, scheduled determine when results are calculated.

  • Periods: Periods, when combined with a timestamp, provide the exact time and the data window (period) for which a KPI was calculated.

  • Interim data: Interim data is cached process data stored in a CSense database.  This data is not kept permanently and will be removed periodically (The default setting for Interim Data to be kept is 30 days).

  • Loop Filters: Loop Filters are optional Entity Class Properties defined for each Loop.  These can be used to filter Loop Results.  Plant, Group and Section are typical examples of Loop Filters.

Related topics

SQL API List

The following is a list of available stored procedures

Stored Procedure

Description

GetSQLAPIVersion

Gets the version of this API.

GetEntityClasses

Gets the list of entity classes available.

GetEntityInstancesForEntityClass

Gets the entity instances for a specified entity class.

GetPeriodsForEntityClass

Gets the list of available shifts that have been used by loops.

GetKPIHistoricalData

Gets the loop KPIs for a specified loop over the specified reporting period.

GetLimitsExceededBarChart

Gets the percentage of time the limits were exceeded for each control mode across all loops over the specified reporting period and filters.

GetDurationBarChart

Gets the percentage time spent in each control mode across all loops over the specified reporting period and filters.

GetLoopPerformanceTable

Gets the loop performance statistics across loops over the specified reporting period and filters.

GetTotalConfigurationChanges

Gets the total number of configuration changes across loops over the specified reporting period and filters.

GetPVOscillations

Gets the PV Oscillation peaks for a specific loop for a specific reporting period.

GetControlModeSummary

Gets a summary of control modes for a specific loop for a specific reporting period.

GetControllerPerformance

Gets the controller output performance statistics for a loop for a specific reporting period.

GetPVPerformance

Gets the process variable performance statistics for a specific loop for a specific reporting period.

GetErrorStats

Gets the error statistics (process variable minus set point) statistics for a specific loop for a specific reporting period.

GetPIDFStartAndEndValues

Gets the PIDF values of the reporting period for a specific loop.

GetControlLoopData

Gets the control loop data for a specific loop for a specific reporting period.

GetShiftPerformance

Gets the shift performance for a specific loop for a specific reporting period.

GetMVHistogram

Gets the data (Bin Index and Bin Value) for the MV histogram for a specific loop for a specific reporting period.

GetErrorHistogram

Gets the data (Bin Index and Bin Value) for the Error histogram for a specific loop for a specific reporting period.

GetPVSPTrend

Gets the time series data for the tag PV, tag SP, the Delta Lower Limit and the Delta Upper Limit for a specific loop for a specific reporting period.

GetMVModeTrend

Gets the time series data for tag MV and tag Control Mode for a specific loop for a specific reporting period.

GetControlLoopTrendData

Gets the time series data for the tag PV, tag SP, Delta Lower Limit, Delta Upper Limit, tag MV, and Control Modes for a specific loop for a specific reporting period.

GetDistinctPropertyValuesForEntityClass

Gets the distinct values for the specified property across all loops.

GetNameForPeriodId

Gets the name of the shift for the specified id.

GetLoopFiltersForEntityInstanceId

Gets the loop filters (Priority, Plant, Section and Group filters) for a specific loop.

Return to top...


Get SQL API Version

Gets the version of this API. This API version can be used to check for compatibility against the expected version that is being used by client applications.

Note: Applications using this API should check the version for compatibility before use. Not verifying the version for compatibility may result in undefined behavior and/or unexpected errors.

Stored Procedure Name

GetSQLAPIVersion

Parameters

None

Output

Name

SQL DataType

Description

Major

INT

The major version component of the API. A difference between the version expected by a client application and the version returned indicates a compatibility breaking change.

Minor

INT

The minor version component of the API. The versions are compatibility if the version expected by a client application is less than or equal to the version returned.

Patch

INT

The patch version component of the API. Differences between the version expected by a client application and the version returned has no effect on compatibility.

Return to top...


Get Entity Classes

Gets the list of entity classes available.

Note: All loops belong to the Control Loop entity class.

Stored Procedure Name

GetEntityClasses

Parameters

None

Output

Name

SQL DataType

Description

EntityClassId

INT

The unique id for an entity class.

EntityClassName

NVARCHAR(256)

The unique name for an entity class.

Description

NVARHCAR(256)

The description for an entity class.

Return to top...


Get Entity Instances

Gets the entity instances for a specified entity class.

Stored Procedure Name

GetEntityInstancesForEntityClass

Parameters

Name

SQL Data Type

Description

EntityClassId

INT

The entity class id for the specified entity class. To get the list of available entity classes that includes the entity class ids, execute the Get Entity Classes stored procedure.

Output

Name

SQL DataType

Description

EntityInstanceId

INT

The unique id for an entity instance.

EntityClassId

INT

The unique id for an entity class.

Name

NVARCHAR(256)

The unique name for an entity instance.

Description

NVARCHAR(256)

The description for an entity instance.

IsRetired

BIT

Property indicates if an entity instance is deployed and running or no longer deployed but still contains data.

The value 0 indicates the entity instance is deployed and running.

The value 1 indicates no longer running but still contains data.

Return to top...


Get Periods For an Entity Class

Gets the list of available shifts that have been used by loops.

Stored Procedure Name

GetPeriodsForEntityClass

Parameters

Name

SQL Data Type

Description

EntityClassName

NVARCHAR(256)

The unique name for an entity class. To get the list of available entity classes that includes the entity class names, execute the Get Entity Classes stored procedure.

Output

Name

SQL DataType

Description

PeriodId

INT

The unique id of the required shift.

PeriodOffset

BIGINT

The amount of time in seconds between each Action Object execution.

ScheduleName

NVARCHAR(256)

The name of the period.

Description

NVARCHAR(256)

The description of the period.

Time

TIME

The starting time of the period for Action Object execution.

Period

BIGINT

The length in seconds of the Action Object execution window.

Return to top...


Get Loop Performance Historical KPI Statistics

Gets the loop KPIs for a specified loop over the specified reporting period.

Stored Procedure Name

GetKPIHistoricalData

Parameters

Name

SQL Data Type

Description

EntityInstanceID

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

Window

INT

The number of days in the reporting period up to the end date.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

EntityInstanceId

INT

The id of the entity instance.

NA

Timestamp

DATETIME2

The timestamp data in the local time format.

NA

PeriodId

INT

The unique id for a shift.

NA

PercentageAuto

FLOAT

The percentage of time spent in the auto control mode.

Auto (%)

PerecentageAutoLLExceeded

FLOAT

The percentage of time spent in the auto control mode while the lower limit was exceeded.

Auto Lower Limit %

PerecentageAutoULExceeded

FLOAT

The percentage of time spent in the auto control mode while the upper limit was exceeded.

Auto Upper Limit %

AverageAbsoluteError

FLOAT

The average absolute value of the tag error, where the tag error is the difference between process variable and set point.

AVG Absolute Error

Average

FLOAT

The average difference between process variable and set point.

Average Error

ControlAmplitude

FLOAT

The average amount of movement in the manipulated variable.

Average MV Change [%]

ReverseAmplitude

FLOAT

The average amount of movement in the manipulated variable before changing its direction.

Average Oscillation Amplitude [%]

PercentageCascade

FLOAT

The percentage of time spent in the cascade control mode.

Cascade (%)

PercentageCascadeLLExceeded

FLOAT

The percentage of time spent in the cascade control mode while the lower limit was exceeded.

Cascade Lower Limit %

PercentageCascadeULExceeded

FLOAT

The percentage of time spent in the cascade control mode while the upper limit was exceeded.

Cascade Upper Limit %

PercentageControlOn

FLOAT

The percentage of time the data was not of bad quality and the controller was On.

The controller is considered on, when it is of a good quality and the control loop operated in the Auto and the Cascade mode.

%Time when control on

DValueEndOfReport

FLOAT

The value of D at the end of the reporting time

Report End (Actual)- D

PercentageNotUtilized

FLOAT

The percentage of time spent in the manual mode or if the controller output is saturated.

Duration Not Utilized [%]

StdDeviation

FLOAT

The standard deviation of the tag error, where the tag error is the difference between process variable and set point.

Error Std Deviation

FValueEndOfReport

FLOAT

Retrieves the value of F at the end of the reporting period.

Report End (Actual)- F

PercentageGoodQuality

FLOAT

The percentage of time that the data was not of bad quality

Good Quality Data (%)

IValueEndOfReport

FLOAT

Retrieves the value of I at the end of the reporting period.

Report End (Actual)- I

IntegratedSum

FLOAT

The absolute sum of all differences between the process variable and the set point.

Integrated Error

PercentageLimitsExceeded

FLOAT

The percentage of time the limits exceeded in all the control modes except shutdown.

Limits Exceeded (%)

PercentageControllerOutput

FLOAT

The average Manipulated variable expressed in percentage.

Manipulated Variable [%]

PercentageManual

FLOAT

The percentage of time spent in the manual control mode.

Manual (%)

PercentageManualLLExceeded

FLOAT

The percentage of time spent in the manual control mode while the lower limit was exceeded.

Manual Lower Limit %

PercentageManualULExceeded

FLOAT

The percentage of time spent in the manual control mode while the upper limit was exceeded.

Manual Upper Limit %

ReverseIndex

FLOAT

The number of times MV changes its direction.

MV Oscillation Count [#]

PercentageCVSaturation

FLOAT

The percentage of time that the MV is saturated.

MV Saturation [%]

NumberOfSamples

FLOAT

The number of samples of data (rows of data) used during the calculation of the statistics for the reporting period.

No. of samples

OverallPerformance

FLOAT

Calculates the overall performance of the loop by combining Manual (%), MV Saturation [%], and Limits Exceeded (%) into a single percentage value.

OverallPerformance

`PValueEndOfReport

FLOAT

Retrieves the value of P at the end of the reporting period.

Report End (Actual)- P

FFTPeriodPeak1

FLOAT

The period (in seconds) of the frequency with the highest associated amplitude on the FFT graph for the reporting period.

Note: This is for the first peak

Period (Seconds)

FFTPowerPercentagePeak1

FLOAT

The power of the point in the FFT graph for which the amplitude is highest.  Expressed as a percentage of the total power in the frequency spectrum of the FFT for the reporting period.

Note: This is for the first peak

Strength (%power)

FFTPeriodPeak2

FLOAT

The period (in seconds) of the frequency with the highest associated amplitude on the FFT graph for the reporting period.

Note: This is for the second peak

Period (Seconds)

FFTPowerPercentagePeak2

FLOAT

The power of the point in the FFT graph for which the amplitude is highest.  Expressed as a percentage of the total power in the frequency spectrum of the FFT for the reporting period.

Note: This is for the second peak

Strength (%power)

FFTPeriodPeak3

FLOAT

The period (in seconds) of the frequency with the highest associated amplitude on the FFT graph for the reporting period.

Note: This is for the third peak

Period (Seconds)

FFTPowerPercentagePeak3

FLOAT

The power of the point in the FFT graph for which the amplitude is highest . Expressed as a percentage of the total power in the frequency spectrum of the FFT for the reporting period.

Note: This is for the third peak

Strength (%power)

PerformanceIndex

FLOAT

Calculates the percentage of time when Auto and Cascade Limits Exceeded.

PerformanceIndex

PIDFChanges

INT

The sum of the number of times any of the PIDF values have changed.

PIDF Configuration Changes

PVVariability

FLOAT

The measure of how spread or closely clustered the PV data set is, expressed in percentage.

PV Variability

PercentagePVVariance

FLOAT

A measure of the deviation of PV from the average PV, expressed in percentage.

PV Variance [%]

SamplingPeriod

FLOAT

The sampling period used during the calculation of the statistics for the reporting period.

Sampling period

PercentageShutdown

FLOAT

The percentage of time spent in the shutdown control mode.

Shutdown (%)

PercentageShutdownLLExceeded

FLOAT

The percentage of time spent in the shutdown control mode while the lower limit was exceeded.

Shutdown Lower Limit %

PercentageShutdownULExceeded

FLOAT

The percentage of time spent in the shutdown control mode while the upper limit was exceeded.

Shutdown Upper Limit %

MovementIndex

FLOAT

The sum of all the changes made in the manipulated variable.

Total MV Movement

Return to top...


Get Limits Exceeded

Gets the percentage of time the limits were exceeded for each control mode across all loops over the specified reporting period and filters.

Stored Procedure Name

GetLimitsExceededBarChart

Parameters

Name

SQL Data Type

Description

Required or Optional

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Required

Date

DATE

The end date of the reporting period.

Required

Window

INT

The number of days in the reporting up to the end date.

Required

PlantFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified plant.

Optional

LoopPriorityFilter

NVARCHAR(MAX)

Filters out loops that are not of the specified priority.

Optional

SectionFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified section.

Optional

GroupFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified group.

Optional

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

Timestamp

DATE

The date for each row of limits exceeded statistics.

NA

PercentageManualExceeded

FLOAT

The percentage of time the limits were exceeded while in manual control mode.

Manual Lower Limit % + Manual Upper Limit %

PercentageAutoExceeded

FLOAT

The percentage of time the limits were exceeded while in auto control mode.

Auto Lower Limit % + Auto Upper Limit %

PercentageCascadeExceeded

FLOAT

The percentage of time the limits were exceeded while in cascade control mode.

Cascade Lower Limit % + Cascade Upper Limit %

PercentageShutdownExceeded

FLOAT

The percentage of time the limits were exceeded while in shutdown control mode.

Shutdown Lower Limit % + Shutdown Upper Limit %

Return to top...


Get Control Mode Duration

Gets the percentage time spent in each control mode across all loops over the specified reporting period and filters.

Stored Procedure Name

GetDurationBarChart

Parameters

Name

SQL Data Type

Description

Required or Optional

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Required

Date

DATE

The end date of the reporting period.

Required

Window

INT

The number of days in the reporting up to the end date.

Required

PlantFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified plant.

Optional

LoopPriorityFilter

NVARCHAR(MAX)

Filters out loops that are not of the specified priority.

Optional

SectionFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified section.

Optional

GroupFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified group.

Optional

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

Timestamp

DATE

The date for each row of limits exceeded statistics.

NA

PercentageManual

FLOAT

The percentage of time spent in manual control mode.

Manual (%)

PercentageAuto

FLOAT

The percentage of time spent in auto control mode.

Auto (%)

PercentageCascade

FLOAT

The percentage of time spent in cascade control mode.

Cascade (%)

PercentageShutdown

FLOAT

The percentage of time spent in shutdown control mode.

Shutdown (%)

Return to top...


Get Loop Performance Statistics

Gets the loop performance statistics across loops over the specified reporting period and filters.

Stored Procedure Name

GetLoopPerformanceTable

Parameters

Name

SQL Data Type

Description

Required or Optional

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Required

Date

DATE

The end date of the reporting period.

Required

Window

INT

The number of days in the reporting up to the end date.

Required

PlantFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified plant.

Optional

LoopPriorityFilter

NVARCHAR(MAX)

Filters out loops that are not of the specified priority.

Optional

SectionFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified section.

Optional

GroupFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified group.

Optional

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

Name

NVARCHAR(256)

The name of the entity instance.

NA

Description

NVARCHAR(256)

The description of the entity instance.

NA

EntityInstanceId

INT

The id of the entity instance.

NA

Days

INT

The number of days that the entity instance has calculated statistics within the reporting period.

NA

PercentageManual

FLOAT

The percentage of time spent in manual control mode.

AVG Manual (%)

PercentageAuto

FLOAT

The percentage of time spent in auto control mode.

AVG Auto (%)

PercentageCascade

FLOAT

The percentage of time spent in cascade control mode.

AVG Cascade (%)

PercentageShutdown

FLOAT

The percentage of time spent in shutdown control mode.

AVG Shutdown (%)

PercentageMVSaturation

FLOAT

The percentage of time the controller output is saturated.

AVG MV Saturation (%)

OverallPerformance

FLOAT

The Overall Performance statistic is a statistic composed of the following elements in the following proportions:

- 1/3: Percentage of time limits were exceeded in all control modes except shutdown.

- 1/3: Percentage of time spent in manual control mode.

- 1/3: Percentage of time the controller output is saturated.

AVG Performance Index (100-0)

TravelIndex

FLOAT

The absolute sum of all changes made in the manipulated variable.

Total MV Movement (average loop performance)

AverageAbsoluteError

FLOAT

The average absolute value of the tag error, where the tag error is the difference between process variable and set point.

AVG Absolute Error (Aggregate)

PercentageAverageAbsoluteError

FLOAT

The average absolute value of the tag error, where the tag error is the difference between process variable and set point. Expressed as a percentage of PV Span.

Average Absolute Error %

PVVariability

FLOAT

The PV Variability is a measure of how spread or closely clustered the PV data set is, expressed as a percentage.

AVG PV Variability

PercentageGoodQuality

FLOAT

The percentage of time that the data is not of bad quality.

Good Quality Data (%)

PIDFChanges

INT

The sum of the number of times any of the PIDF values have changed.

PIDF Configuration Changes

PercentageLimitsExceeded

FLOAT

The percentage of time, limits were exceeded in all the control modes except shutdown.

AVG Limits Exceeded (%) (Auto + Cascade + Manual)

PerformanceIndex

FLOAT

The percentage of time in the Auto or Cascade control mode and exceeding the upper or lower limit.

AVG Limits Exceeded (%) (Auto + Cascade)

Return to top...


Get Total Configuration Changes

Gets the total number of configuration changes across loops over the specified reporting period and filters.

Stored Procedure Name

GetTotalConfigurationChanges

Parameters

Name

SQL Data Type

Description

Required or Optional

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Required

Date

DATE

The end date of the reporting period.

Required

Window

INT

The number of days in the reporting up to the end date.

Required

PlantFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified plant.

Optional

LoopPriorityFilter

NVARCHAR(MAX)

Filters out loops that are not of the specified priority.

Optional

SectionFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified section.

Optional

GroupFilter

NVARCHAR(MAX)

Filters out loops that are not in the specified group.

Optional

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

PIDFChanges

INT

The total number of configuration changes.

PIDF Configuration Changes

Return to top...


Get PV Oscillations

Gets the PV Oscillation peaks for a specific loop for a specific reporting period.

Stored Procedure Name

GetPVOscillations

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

Peak

NVARCHAR(6)

The peak number to describe the other 2 statistics (peak 1, peak 2 or peak 3).

PV Oscillations

FFTPeriodPeak

FLOAT

The period (in seconds) of the frequency with the 3 highest associated amplitudes.

Note that this KPI can only be calculated when there is 100% good quality data for the period for which the FFT is calculated, otherwise this KPI will return -1.

Period (Seconds)

FFTPowerPercentagePeak

FLOAT

The power of the frequency for the 3 highest amplitudes, expressed as a percentage of the total power in the frequency spectrum.

Note that this KPI can only be calculated when there is 100% good quality data for the period for which the FFT is calculated, otherwise this KPI will return -1.

Strength (%power)

Return to top...


Get Control Mode Summary

Gets a summary of control modes for a specific loop for a specific reporting period.

Stored Procedure Name

GetControlModeSummary

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

Mode

NVARCHAR(8)

The control mode name (Manual, Auto, Cascade or Shutdown).

NA

Duration

FLOAT

The percentage of time spent in each control mode.

Duration %

LowerExceeded

FLOAT

The percentage of time spent in each control mode while the lower limit was exceeded.

Cascade Lower Limit %

Auto Lower Limit %

Manual Lower Limit %

Shutdown Lower Limit %

UpperExceeded

FLOAT

The percentage of time spent in each control mode while the upper limit was exceeded.

Cascade Upper Limit %

Auto Upper Limit %

Manual Upper Limit %

Shutdown Upper Limit %

TotalExceeded

FLOAT

The percentage of time spent in each control mode while either the lower or upper limit was exceeded.

Total Limits %

Return to top...


Get Controller Performance

Gets the controller output performance statistics for a loop for a specific reporting period.

Stored Procedure Name

GetControllerPerformance

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

ControllerHeading

NVARCHAR(33)

The name of the controller output statistic.

NA

ControllerStat

FLOAT

The calculated value for each controller output statistic.

 

Manipulated Variable: The average Manipulated variable expressed as a percentage.

 

 

Total MV Movement: The sum of all changes made in the manipulated variable.

 

 

 

Average MV Change: The average amount of movement in the manipulated variable.

 

 

 

MV Oscillation Count: The number of times that the manipulated variable changes direction divided by 2.

 

Average Oscillation Amplitude: The average amount of movement in the manipulated variable before changing direction.

 

 

 

MV Saturation: The percentage of time the controller output is saturated.

 

 

Duration Not Utilized: The percentage of time spent in manual mode or if the controller output is saturated.

NA            

 

 

Manipulated Variable [%]              

 

 

Total MV Movement              

 

 

Average MV Change [%]                

 

 

 

MV Oscillation Count [#]                

 

Average Oscillation Amplitude [%]              

 

 

 

MV Saturation [%]          

 

 

Duration Not Utilized [%]

Return to top...


Get PV Performance

Gets the process variable performance statistics for a specific loop for a specific reporting period.

Stored Procedure Name

GetPVPerformance

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

PVHeading

NVARCHAR(15)

The name of the process variable statistic.

NA

PVStat

FLOAT

The calculated value for each process variable statistic.

PV Variance: A measure of the deviation of PV from the average PV, expressed as a percentage.

PV Variability: A measure of how spread or closely clustered the PV data set is, expressed as a percentage.

PV Variance [%]              

 

 

PV Variability

Return to top...


Get Error Statistics

Gets the error statistics (process variable minus set point) statistics for a specific loop for a specific reporting period.

Stored Procedure Name

GetErrorStats

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

ErrorHeading

NVARCHAR(22)

The name of the error statistic.

NA

ErrorStat

FLOAT

The calculated value for each error statistic.

 

Integrated Error: The absolute sum of all differences between the process variable and the set point.

 

 

Average Error: The average difference between process variable and set point.

 

 

Average Absolute Error: The average absolute value of the tag error, where the tag error is the difference between process variable and set point.

 

 

Percentage Average Absolute Error: The average absolute value of the tag error, where the tag error is the difference between process variable and set point. Expressed as a percentage of PV Span.

 

 

 

Error Std Deviation: The standard deviation of the tag error, where the tag error is the difference between process variable and set point.

           

 

Integrated Error              

 

 

 

Average Error            

 

 

Average Absolute Error          

 

 

 

Average Absolute Error %                    

 

 

 

 

Error Std Deviation

Return to top...


Get PIDF Start And End Values

Gets the PIDF values of the reporting period for a specific loop.

Stored Procedure Name

GetPIDFStartAndEndValues

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

Report

NVARCHAR(256)

The PIDF report labels.

NA

P

NVARCHAR(256)

The P values of the report.

Report Start (Actual)- P Report End (Actual)- P

I

NVARCHAR(256)

The I values of the report.

Report Start (Actual)- I Report End (Actual)- I

D

NVARCHAR(256)

The D values of the report.

Report Start (Actual)- D Report End (Actual)- D

F

NVARCHAR(256)

The F values of the report.

Report Start (Actual)- F Report End (Actual)- F

Return to top...


Get Control Loop Data

Gets the control loop data for a specific loop for a specific reporting period.

Stored Procedure Name

GetControlLoopData

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

ControlHeading

NVARCHAR(22)

The name of the control loop data statistic.

NA

ControlData

FLOAT

Sampling period: The sampling period used during the calculating of the statistics for the reporting period.

 

 

No. of samples: The number of samples of data (rows of data) used during the calculating of the statistics for the reporting period.

 

 

Good quality data: The percentage of time that the data is not of bad quality.

 

 

Time when control on: The percentage of data that is not of bad quality and the current control mode is enabled.

Sampling period              

 

 

 

No. of samples                  

 

 

 

Good Quality Data (%)          

 

 

Duration %

Return to top...


Get Shift Performance

Gets the shift performance for a specific loop for a specific reporting period.

Stored Procedure Name

GetShiftPerformance

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

ScheduleName

NVARCHAR(256)

The name of each shift.

NA

PercentageManual

FLOAT

The percentage of time spent in manual control mode.

Manual (%)

PercentageAuto

FLOAT

The percentage of time spent in auto control mode.

Auto (%)

PercentageCascade

FLOAT

The percentage of time spent in cascade control mode.

Cascade (%)

PercentageShutdown

FLOAT

The percentage of time spent in shutdown control mode.

Shutdown (%)

PercentageLimitsExceeded

FLOAT

The percentage of time limits were exceeded in all control modes except shutdown.

Limits Exceeded (%)

Return to top...


Get MV Histogram

Gets the data (Bin Index and Bin Value) for the MV histogram for a specific loop for a specific reporting period.

Stored Procedure Name

GetMVHistogram

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

BinIndex

FLOAT

The table of data that represents the bin index values for the MV histogram.

BinValue

FLOAT

The table of data that represents the bin values for the MV histogram.

Return to top...


Get Error Histogram

Gets the data (Bin Index and Bin Value) for the Error histogram for a specific loop for a specific reporting period.

Stored Procedure Name

GetErrorHistogram

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

BinIndex

FLOAT

The table of data that represents the bin index values for the Error histogram.

BinValue

FLOAT

The table of data that represents the bin values for the Error histogram.

Return to top...


Get PVSP Trend

Gets the time series data for the tag PV, tag SP, the Delta Lower Limit and the Delta Upper Limit for a specific loop for a specific reporting period.

Stored Procedure Name

GetPVSPTrend

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

Timestamp

DATETIME2

The timestamp data in local time.

Tag_PV

FLOAT

The raw tag pv data stored in the cache.

Tag_SP

FLOAT

The raw tag sp data stored in the cache.

LowerLimit

FLOAT

The raw delta lower limit data stored in the cache.

UpperLimit

FLOAT

The raw delta upper limit data stored in the cache.

Return to top...


Get MV Mode Trend

Gets the time series data for tag MV and tag Control Mode for a specific loop for a specific reporting period.

Stored Procedure Name

GetMVModeTrend

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

Timestamp

DATETIME2

The timestamp data in local time.

Tag_Control_Type

FLOAT

The raw tag control mode data stored in the cache.

Tag_MV

FLOAT

The raw tag mv data stored in the cache.

Return to top...


Get Control Loop Trend Data

Gets the time series data for the tag PV, tag SP, Delta Lower Limit, Delta Upper Limit, tag MV, and Control Modes for a specific loop for a specific reporting period.

Stored Procedure Name

GetControlLoopTrendData

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Date

DATE

The end date of the reporting period.

PeriodId

INT

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

KPIs Name (See Understanding Process+ KPIs)

Timestamp

DATETIME2

The timestamp data in local time.

NA.

Tag_PV

FLOAT

The raw tag pv data stored in the cache.

NA.

Tag_SP

FLOAT

The raw tag sp data stored in the cache.

NA.

LowerLimit

FLOAT

The raw delta lower limit data stored in the cache.

NA.

UpperLimit

FLOAT

The raw delta upper limit data stored in the cache.

NA.

Tag_MV

FLOAT

The raw tag mv data stored in the cache.

NA.

ManualMode

FLOAT

This is based on the raw tag control mode data stored in the cache. It will indicate if the current control mode is Manual. The value will be 100 when in the Manual mode and 0 when in the other modes.

NA.

AutoMode

FLOAT

This is based on the raw tag control mode data stored in the cache. It will indicate if the current control mode is Auto. The value will be 100 when in the Auto mode and 0 when in the other modes.

NA.

CascadeMode

FLOAT

This is based on the raw tag control mode data stored in the cache. It will indicate if the current control mode is Cascade. The value will be 100 when in the Cascade mode and 0 when in the other modes.

NA.

ShutdownMode

FLOAT

This is based on the raw tag control mode data stored in the cache. It will indicate if the current control mode is Shutdown. The value will be 100 when in the Shutdown mode and 0 when in the other modes.

NA.

Return to top...


Get Distinct Property Values

Gets the distinct values for the specified property across all loops.

Stored Procedure Name

GetDistinctPropertyValuesForEntityClass

Parameters

Name

SQL Data Type

Description

EntityClassName

NVARCHAR(256)

The unique name for an entity class. To get the list of available entity classes that includes the entity class names, execute the Get Entity Classes stored procedure.

Note: The API is specific to Process+ therefor the entity class would need to be Control Loop.

PropertyInstanceName

NVARCHAR(256)

An property name for the entity class.

Output

Name

SQL DataType

Description

Value

NVARCHAR(256)

The distinct values for the specified entity class property across all entity instances that belongs to the specified entity class.

Return to top...


Get Name For PeriodId

Gets the name of the shift for the specified id.

Stored Procedure Name

GetNameForPeriodId

Parameters

Name

SQL Data Type

Description

PeriodId

NVARCHAR(256)

The period id of the required shift. To get the list of available periods for an entity, execute the Get Periods For an Entity Class stored procedure.

Output

Name

SQL DataType

Description

ScheduleName

NVARCHAR(256)

The name of the period.

Return to top...


Get Loop Filters For Specific Loop

Gets the loop filters (Priority, Plant, Section and Group filters) for a specific entity instance that belongs to the Control Loop entity class.

Stored Procedure Name

GetLoopFiltersForEntityInstanceId

Parameters

Name

SQL Data Type

Description

EntityInstanceId

INT

An entity instance id that belongs to the Control Loop entity class. To get the list of available entity instances that belong to the Control Loop entity class, execute the Get Entity Classes stored procedure to get the entity class id for Control Loop, then execute the Get Entity Instances and pass in the id from the previous stored procedure to get the list of Entity Instances.

Output

Name

SQL DataType

Description

Name

NVARCHAR(256)

The name of the filter (Priority, Plant, Section and Group).

Value

NVARCHAR(256)

The value for the filter.

Return to top...


Related topics:

 

  

CSense 2023- Last updated: June 24,2025