C135 PCS 7 (PCS7)
6C
6 Cielo Ciencia y Tecnología
·Publicada en C135 PCS 7 (PCS7)

Generating Microsoft Excel Reports from SIMATIC PCS 7 Historical Process Data

Generating Microsoft Excel Reports from SIMATIC PCS 7 Historical Process Data

Direct native export of automated Excel reports is not an integrated feature of standard WinCC/PCS 7 OS stations without software add-ons. The official Siemens system solution is SIMATIC Information Server, which integrates with Process Historian or WinCC OS servers to deliver native Microsoft Excel and Word add-ins. This enables scheduled, automated generation of reports in .xlsx format querying historical trends, alarms, and batch logs. Alternatively, engineers can query WinCC Tag Logging archives directly into Excel using the WinCC OLE DB Provider (WinCC Connectivity Pack) or export runtime trends manually to CSV. Validate all Information Server packages through an authorized Siemens distributor or the Siemens Industry Mall.

Table of Contents

Executive Summary

Plant operations, quality management, and process optimization teams frequently demand daily, weekly, or shift reports formatted directly as native Microsoft Excel spreadsheets (.xlsx). While SIMATIC PCS 7 includes an integrated Report Designer, its native output is geared toward physical paper printing or static PDF generation rather than editable Excel workbooks. To bridge this gap, Siemens provides the SIMATIC Information Server, an enterprise web reporting system that leverages Microsoft Internet Information Services (IIS) and dedicated Office add-ins to query Process Historian or WinCC archives. For smaller architectures where Information Server is not deployed, direct SQL queries via the WinCC OLE DB Provider or runtime CSV toolbar exports provide practical alternatives.

Architecture and Fundamental Mechanics

Extracting historical process data into Microsoft Excel involves three distinct technical pathways:

1. The Enterprise Solution: SIMATIC Information Server

SIMATIC Information Server connects to central Process Historian (PH) or local WinCC OS Servers:
- It provides a dedicated Microsoft Excel Add-In that embeds a ribbon toolbar directly inside Microsoft Excel.
- Engineers configure dynamic report templates that query tag logging curves, statistical aggregations (min, max, average, standard deviation), alarm frequency tables, and SIMATIC BATCH records.
- The Information Server web engine executes automated reporting schedules, generating .xlsx workbooks and emailing them automatically to stakeholders or saving them to network shares.

2. The Direct Integration: WinCC OLE DB Provider (Connectivity Pack)

For standalone OS Servers without Information Server:
- WinCC stores compressed historical process values in segmented Microsoft SQL Server databases (CC_..._TLG_...).
- Standard SQL SELECT queries cannot decompress WinCC tag archives directly. Instead, Siemens provides the WinCC OLE DB Provider (licensed via the WinCC Connectivity Pack).
- Inside Excel, an engineer writes a Visual Basic for Applications (VBA) macro or uses Microsoft Query via ADODB connecting to Provider=WinCCOLEDBProvider.1. The provider decompresses archive records and streams process values directly into spreadsheet rows.

3. The Operator Runtime Export: Trend and Alarm Control CSV Toolbar

  • Standard APL faceplates and WinCC Online Trend Control (CCAxOnlineTrend.ocx) include a toolbar icon labeled 'Export data'.
  • Clicking this button exports the currently displayed curve buffer to a comma-separated .csv file, which opens natively in Excel.

Step-by-Step Implementation Procedure

To implement automated Excel reporting using SIMATIC Information Server:

Step 1: Install and Configure SIMATIC Information Server

  1. Install SIMATIC Information Server on a dedicated PC station or the Process Historian server.
  2. Ensure Microsoft Internet Information Services (IIS) and SQL Server Reporting Services (SSRS) are configured.
  3. In the Information Server management console, establish the data source connection pointing to the WinCC OS Server or Process Historian.
  4. Verify that the Information Server runtime license is active in Automation License Manager (ALM).

Step 2: Install the Information Server Microsoft Office Add-In

  1. On the reporting client PC (engineer's office workstation), open a browser and navigate to the Information Server web portal.
  2. Navigate to Administration -> Tools -> Download Office Add-In.
  3. Install the Information Server Excel Add-In.
  4. Launch Microsoft Excel; confirm that the new Information Server tab appears on the Excel ribbon.

Step 3: Design the Excel Report Template

  1. In Excel, click the Information Server ribbon tab and click Connect.
  2. Enter the server URL and log in with Windows credentials.
  3. Click Insert Process Values:
    - Browse the plant hierarchy and select the desired tags (e.g., Reactor Temperature, Feed Flow).
    - Select time range: e.g., 'Relative: Last 24 Hours' or 'Shift 1'.
    - Choose aggregation mode: Raw data, 15-minute averages, or hourly maximums.
  4. Format the Excel sheet with company headers, formulas, charts, and conditional formatting.
  5. Click Upload Template to store the template on the Information Server.

Step 4: Schedule Automated Report Generation

  1. In the Information Server web portal, navigate to Reports -> Schedules.
  2. Create a new recurring schedule (e.g., Daily at 06:00 AM).
  3. Select the uploaded Excel template.
  4. Configure output options: Save as .xlsx to a corporate network file share, or distribute automatically via email.

Verification

To confirm report generation:

  1. Manual Execution Test:
    - In the Information Server web portal, select the report and click Execute Now.
    - Confirm that the generated Excel workbook downloads cleanly.
    - Open the .xlsx file; verify that numerical process values, timestamps, and charts populate accurately without formula errors.

  2. Scheduled Trigger Verification:
    - Check the configured destination folder following the scheduled run time.
    - Confirm that the automated report file is present with the correct timestamp.

Limitations and Operational Precautions

Operational limitations and precautions:

  1. Licensing Constraints: SIMATIC Information Server requires client access licenses (CALs) or report engine licenses, while WinCC direct querying requires the WinCC Connectivity Pack license.
  2. Network Bandwidth: Querying vast tag archives (millions of data points) directly from an active OS Server during peak production can degrade WinCC runtime responsiveness; archive reporting should ideally query a dedicated Process Historian.
  3. Excel Row Limits: Historical raw data queries with sub-second sample rates can quickly exceed Microsoft Excel's limit of 1,048,576 rows; always apply statistical aggregation (e.g., 1-minute averages) for long-duration reports.
  4. Commercial Disclaimer: Verify all software options, Information Server packages, and Process Historian licenses with an authorized Siemens distributor or the Siemens Industry Mall.

Engineer FAQ

Does SIMATIC PCS 7 natively generate Excel reports out of the box?

No. The built-in Report Designer prints to PDF or physical printers. Native Excel reporting requires SIMATIC Information Server, WinCC Connectivity Pack, or manual CSV export.

What is SIMATIC Information Server?

It is Siemens' web-based reporting system that queries Process Historian or WinCC archives to generate automated, scheduled reports in Excel, Word, and PDF formats.

Can I extract historical data from WinCC using standard SQL queries?

No. WinCC tag archives are compressed and encrypted in SQL Server. Decompressing and reading historical data requires the WinCC OLE DB Provider included with WinCC Connectivity Pack.

How can an operator quickly export a trend to Excel during runtime?

Click the 'Export to CSV' button on the WinCC Online Trend Control toolbar. The resulting CSV file opens natively in Microsoft Excel.

Can Information Server reports be sent automatically by email?

Yes. Information Server includes a built-in scheduling engine that can automatically email generated Excel reports to configured distribution lists.

Technical References

Related Articles

Related Tags

  • Information Server
  • Excel Reporting
  • Process Historian
  • WinCC OLE DB
  • Tag Logging Archive
20 respuestas