Location

Select one or more locations (or location groups).

If one or more locations are selected, only data for Logger(s) and Series that belong to the selected locations are retrieved. If no locations are selected, data for all locations are retrieved.

The Location parameter values can also be set by the Enterprise Dashboard Filter Widgets.

Individual(s)

Select one or more locations.

Group(s)

Select one or more location groups.

Logger

Select one or more loggers.

Only loggers for the currently selected facility (or facility group) will be available. The loggers must also have one or more series with SERIES_TYPE set to NULL or 'DATUM'.

If one or more loggers are selected, only data for the selected loggers will be retrieved. If no loggers are selected, data for all loggers will be retrieved.

Series

Select one or more series.

If one or more series are selected, only data for the selected series will be retrieved. If no series are selected, data for all series will be retrieved.

Time Formats

Select one or more time formats to be included as columns in the results.

Note: Time Formats is not used (e.g., with the Date Range) to filter what data are retrieved.

UTC (Coordinated Universal Time)

Include DATUM_UTC_DT in results.

Column Name: DATUM_UTC_DT

Local time of the logger (not adjusted for DST)

Include DATUM_UTC_DT + DT_LOGGER.UTC_OFFSET_HRS in results.

Column Name: DATUM_LOGGER_DT

UTC offset hours (not adjusted for DST)

Include DATUM_UTC_DT + UTC offset hours (e.g., enter -5 for Eastern Time, or -3.5 for Newfoundland) in results.

Column Name: UTC+/-##:##:##_DT

Date Range

The report returns data where DT_LOGGER_DATUM.DATUM_UTC_DT is between the Start Date and End Date (inclusive). The Start Date and End Date are expected to be UTC.

Note: The Start Date and End Date values are not limited to the predefined dynamic values in Enterprise. These values can also be modified in ST_USER_REPORT_PARAMETER.PARAM_VALUE (for a user report).

For example, in Enterprise, "Today" is defined as today at midnight, and uses the server's local time: $select dateadd(dd, 0, datediff(dd, 0, getdate())). However, users can manually change it to use UTC-5 hours: $select dateadd(hh, -5, dateadd(dd, 0, datediff(dd, 0, getutcdate()))). (The '$' specifies that the parameter value is SQL, instead of a constant value. Also, users can run these selects in SSMS to debug them.)

Start Date

Enter the desired start date (defaults to the first day of the current month).

End Date

Enter the desired end date (defaults to the first day of the following month).

Qualifier(s)

Include or exclude data with these qualifiers.

Comparer

Select whether to include or exclude the qualifiers.

Qualifier(s)

Select one or more qualifiers.

The qualifiers in this list are from RT_LOGGER_QUALIFIER, and for performance reasons, DT_LOGGER_DATUM.DATUM_QUALIFIER does not have RT_LOGGER_QUALIFIER.QUALIFIER as a foreign key. The following SQL will insert any missing qualifiers into RT_LOGGER_QUALIFIER that exist in DT_LOGGER_DATUM:

Aggregate

Aggregate DT_LOGGER_DATUM.DATUM_VALUE, DATUM_QUALIFIER, and/or EBATCH.

Note: The aggregated data is between the Start Date and End Date (inclusive).

By default, the Start Date determines the start of each period. For example, if the Start Date is on the 22nd of the month, and the aggregate period is monthly, then each period starts on the 22nd of the month. See the Aggregate Date Expression for more info.

Depending upon the End Date value, the last period may not be a full period. The last period will only include data up to the End Date.

Function(s)

Select one or more aggregate functions.

Note: Except for COUNT(*), aggregate functions ignore NULL values.

Period

Select an aggregate period (DATEPART).

Example: hour, day, or month.

Period multiplier

Select the number of periods to aggregate based on Period selection above.

Example: aggregate for x hours, days or months, where x is an INT (the DATETIME functions DATEADD and DATEDIFF use INTs).  

Note: 1.5 hours will not work, instead, use 90 minutes.

Date Expression

Hidden parameter that contains the SQL expression used (in the GROUP BY clause) to aggregate the data, and (in the SELECT clause) to get the DATUM_UTC_DT values.

Default value (adapted from here): dateadd(@agg_period, datediff(@agg_period, @start_date, dt_logger_datum.datum_utc_dt) / @agg_period_multiplier * @agg_period_multiplier, @start_date)

The default value uses the Start Date as the 'anchor date'. For example, if the Start Date is on the 22nd of the month and the aggregate period is monthly, then each period starts on the 22nd of the month.

The default value 'floors' the DATUM_UTC_DT values to the start of each period, but it can be tweaked to get the middle or end of each period:

Max Decimal Digits

Optionally, enter the maximum number of decimal digits desired in the retrieved DATUM_VALUEs, between 0 and 9.

NULL Qualifier Swap

Optionally, replace NULL DATUM_QUALIFIERs with this value in the report output (e.g., to display data in the Time Series Chart Widget and group data by DATUM_QUALIFIER).

Remarks

Optionally, show remarks and remark types (from DT_LOGGER_REMARK) for the data.

A remark row is shown for a datum row if:

1.The remark's LOGGER_ID matches the datum's and the remark's LOGGER_SERIES_ID is NULL (i.e., it is for all of this logger's series), or the remark's LOGGER_SERIES_ID is equal to the datum's LOGGER_SERIES_ID (i.e., it is only for this series), and

2.The remark's REMARK_UTC_DT is equal to the datum's DATUM_UTC_DT, or, if the remark's DURATION_S is not NULL, if the datum's DATUM_UTC_DT is >= REMARK_UTC_DT and <= REMARK_UTC_DT + DURATION_S seconds.

If a datum row has multiple remarks (and remark types), they are separated by pipes. An empty string is displayed if a REMARK or REMARK_TYPE is NULL (or an empty string), to be consistent with the way EQuIS displays other (NULL) values.

Show Remarks

Check this parameter to show the remarks.

Show Remark Types

Check this parameter to show the remark types.

Show Only Remarks with Type

Select one or more REMARK_TYPEs to filter the remarks shown by the report.

If nothing is selected, and Show Remarks and/or Show Remark Types is checked, it shows all remarks and/or all remark types.

Show Coordinates

Check this parameter to retrieve X and Y coordinates, and FACILITY_ID, for each datum, e.g., to display data in the Traffic Light Widget, and/or Traffic Light Map Widget.

Show Inactive Data

Check this parameter to retrieve (active and) inactive data.

Data is active if both its series and logger have STATUS_FLAG = 'A', otherwise it is inactive.

Additional Fields

Select additional table columns to include in results.

The columns for the DT_LOGGER, DT_LOGGER_SERIES, and DT_LOGGER_DATUM tables are included by default, and other tables can also be added by providing join conditions. For more info see How To Customize Additional Fields Parameter.

Extra Selects

Add extra selects to include in results, for example:

equis.unit_conversion(DATUM_VALUE, SERIES_UNIT, 'deg F', 9999999) AS [Temperature (deg F)]

For more info see Extra Selects.

Note: This parameter is hidden by default.