Resolving WinCC DataMonitor Excel Workbook Tag Resolution Overrides and Historical Archive Sampling

WinCC DataMonitor Excel Workbooks ignore manual resolution settings (such as 3,600,000 ms for 1-hour intervals) when configured to retrieve raw compressed archive tags rather than interpolated, cycle-compressed values. To enforce a 1-hour resolution, configure the DataMonitor Excel Add-In archive query mode from 'Raw Archive Values' to 'Interpolated Values' or 'Aggregated Values' (such as Average or Last Value), and set the WinCC OLE DB Provider time grid parameter explicitly. Raw queries bypass time grids and stream every recorded cyclical or event-driven archive record stored by Tag Logging.

DataMonitor Archive Query Mechanics: Raw vs Interpolated Sampling

SIMATIC WinCC DataMonitor allows process and operational data to be extracted directly into Microsoft Excel workbooks, web browsers (Webcenter), and automated management reports. When configuring the Excel Workbook data provider, engineers query process value archives configured under WinCC Tag Logging.

A frequent operational defect occurs when an engineer enters a custom resolution (for example, 3,600,000 ms to sample hourly values across a 24-hour shift), but the resulting spreadsheet displays raw 500 ms historical samples. This behavior is determined by the underlying WinCC OLE DB Provider query syntax (TAG:R). In WinCC Tag Logging, if the query type is left at its default 'Archive Data' (Raw Values), the OLE DB engine retrieves all actual logged records without mathematical downsampling. Because Tag Logging cycles at 500 ms acquisition cycles, the query returns tens of thousands of lines rather than the intended 24 hourly values.

Configuring Excel Workbook Time Filters and Aggregation Functions

To resolve data at specific time increments, the query configuration in the WinCC DataMonitor Excel Add-In must use calculated or aggregated archive retrieval. The DataMonitor wizard provides multiple query types:

Raw Values (Values as Logged): Returns every recorded point in the archive database regardless of the specified resolution parameter. If 3,600,000 ms is typed into the resolution box while Raw Values is selected, the resolution field is completely ignored by the query processor.
Interpolated Values (Linear / Step): Calculates process values at fixed equidistant time steps based on linear interpolation between neighboring recorded points. This mode strictly honors the resolution grid (e.g., 3,600,000 ms).
Aggregated Values (Statistical Functions): Applies aggregation over the specified time bucket (Average, Minimum, Maximum, Sum, or Difference). For hourly plant reporting, the 'Average' or 'Interpolated' function should be selected with a time interval of 1 hour.

WinCC OLE DB Provider Time Grid and Tag Logging Parameters

Behind DataMonitor, communication relies on the WinCC Connectivity Pack and Microsoft SQL Server. The native query format for tag logging is:

TAG:R,('ArchiveName\TagName'),'StartDateTime','StopDateTime' [,'TIMESTEP=3600,TYPE=1']

Where TIMESTEP represents the interval in seconds and TYPE defines the calculation mode (TYPE=1 for linear interpolation, TYPE=2 for step interpolation, TYPE=5 for time-weighted average). When the DataMonitor wizard is configured without an explicit aggregation algorithm, it generates a query omitting the TIMESTEP and TYPE parameters, forcing Microsoft SQL Server to execute a simple scan of the Tag Logging segments.

Step-by-Step Diagnostic and Configuration Procedure

To correct the resolution in Microsoft Excel using WinCC DataMonitor:

Open Excel and Access DataMonitor Wizard: Launch Microsoft Excel with the DataMonitor Add-In active. Open the workbook and select the target data table or click DataMonitor > Historical Data > Archive Values.
Edit the Data Provider Configuration: Click Edit Query. In the Tag Selection tab, select the required archive tags.
Select Processing Method: In the Data Processing or Value Extraction tab, change the extraction mode from 'Values as stored (Raw)' to 'Calculated values' or 'Equidistant time grid'.
Define Time Grid: Enter 3600000 ms (or select 1 hour from the drop-down unit selector). Set the calculation type to 'Linear Interpolation' or 'Average'.
Define Output Range and Execute: Specify the destination worksheet range and click Finish. Verify that exactly 24 data rows appear for a single day rather than thousands of raw points.
Verify Tag Logging Configuration: In WinCC Explorer, verify that the tag in Tag Logging has 'Archive with time stamp' enabled and that historical segments are fully available in SQL Server without corrupt archive partitions.

Archive Query Mode Comparison

The following table compares the different WinCC DataMonitor extraction modes and their behavior with resolution filters:

Extraction ModeOLE DB Provider SyntaxResolution Filter EffectSQL Server WorkloadPrimary Industrial ApplicationRaw Values (As Stored)TAG:R,('Tag'),'Start','Stop'Ignored (Returns all raw cycles)High I/O for large time spansMicrosecond root-cause trip analysisLinear InterpolationTAG:R,('Tag'),'Start','Stop','TIMESTEP=x,1'Enforced exactly at time gridLow network, CPU calculationEquidistant trending and chartsStep InterpolationTAG:R,('Tag'),'Start','Stop','TIMESTEP=x,2'Enforced; holds previous valueLow network, minimal CPUDiscrete states, valve positions, recipe IDsAggregated AverageTAG:R,('Tag'),'Start','Stop','TIMESTEP=x,5'Enforced; averages time windowModerate SQL calculationShift and daily KPI energy monitoringAggregated Max / MinTAG:R,('Tag'),'Start','Stop','TIMESTEP=x,3/4'Enforced; extremes in windowModerate SQL calculationEquipment thermal limit tracking

Verification

Verify the DataMonitor configuration through the following procedure:

Trigger an Excel refresh across a 24-hour test window. Confirm that the total row count generated equals 25 points (inclusive of start and end hours).
Check that each timestamp increment corresponds exactly to 01:00:00, 02:00:00, etc., with zero 500 ms offset lines.
Open SQL Server Management Studio on the WinCC OS Server and inspect the CC_ExternalScanning or Connectivity Pack session traces to ensure the query contains TIMESTEP=3600.
Compare the interpolated value in Excel against the WinCC Online Trend Control ruler value at identical timestamps to confirm mathematical parity.

Limitations and Operational Precautions

While DataMonitor aggregation simplifies reporting, consider the following limitations:

Requesting raw values over multi-week intervals can trigger memory exhaustion in Microsoft Excel (exceeding the 1,048,576 row limit) and induce severe SQL disk throttling on the OS Server.
Linear interpolation can mask brief process spikes or safety trips that occur between hourly sample intervals; never use aggregated queries for trip sequence investigation.
When WinCC redundancy is active, ensure the DataMonitor connectivity provider points to the active redundant partner or the WinCC CAS / Process Historian to avoid reading incomplete segments during synchronization.
WinCC DataMonitor client licenses are counted concurrently; ensure adequate web client licenses are provisioned on the DataMonitor web server.

Engineer FAQ

What is the difference between acquisition cycle and archiving cycle in Tag Logging?

The acquisition cycle defines how often WinCC samples the process tag from the PLC memory. The archiving cycle determines when that acquired value is written to the hard drive database, potentially applying deadband compression or averaging.

Can DataMonitor extract data directly from Siemens Process Historian (PH)?

Yes, DataMonitor can query Process Historian directly using the Process Historian Ready connection, offloading heavy historical calculation overhead from live PCS 7 OS runtime servers.

Why do some timestamps display quality code 0x4C or 0x00 in Excel?

Quality codes indicate archive state. Code 0x4C represents uncertain or clamped data, while 0x00 denotes bad or uninitialized communication between the AS and the OS server Tag Logging engine.