SIMATIC Information Server - Setup the SIMATIC Information Server to display WinCC OA data based reports

By using the SIMATIC Information Server you always have a transparent access to plant data. Historic data can be displayed in web-based dashboards or reports. The report system is based on the Microsoft SQL Server Reporting Services.

The Microsoft SQL Server Reporting Services XML data extension is capable of retrieving reporting data from the gSOAP interface provided by the WinCC OA Reporting service. It ships with Reporting Services by default and no additional software needs to be installed.

Two ready-to-use Reporting templates are delivered for the SIMATIC Information Server with the WinCC OA installation. WinCC_OA_AlarmTable.rdl and WinCC_OA_LineChart.rdl

You can find the templates in the wincc_oa_path/data/Reporting/Templates/InformationServer directory.

This chapter describes how to setup the SIMATIC information server to display WinCC OA data-based reports. Proceed as follows:

Create an XML Data Source

Open the report template WinCC_OA_AlarmTable.rdl from the wincc_oa_path/data/Reporting/Templates/InformationServer directory. If you want to create your own templates, take the ready-to-use templates as an example and modify them. You can find the templates under (wincc_oa_path/data/Reporting/Templates/InformationServer).
Create an XML data source (embedded) by selecting the " WinCC OA" data source from the left side of the Microsoft SQL Server Report Builder window (see figure below). If you create a new report template, make sure that your report template contains the following code:
```
<DataSources>
<DataSource Name="WinCCOA">
<DataSourceReference>/Datasources/WinCC OA</DataSourceReference>
<rd:SecurityType>Integrated</rd:SecurityType>
<rd:DataSourceID>7261b98e-86e0-459e-8228-1466ecfc5007</rd:DataSourceID>
</DataSource>
</DataSources>
```
Add the code to the beginning of the template file (see figure below):

Figure 1. WinCC_OA_AlarmTable.rdl file
Once the template contains the code above, you can select the data source under Data Sources in the Microsoft SQL Server Report Builder.

Figure 2. Created XML Data Source in the Microsoft SQL Server Report Builder

Create data sets for the XML data source

The Reporting Services XML data extension does not permit raw SOAP requests and requires the use of the XmlDP query language. The language is used to generate the actual SOAP bodies.

Create new datasets for your XML data source via a right-click on the Datasets directory and by selecting Add Dataset.... Enter a name, select the "Use a dataset embedded in my report" option, select the Query Type "Text" and enter XmlDP query strings into the “Query” input box:
```
<Query>
<Method Namespace="urn:Siemens.OA" Name="alertGetPeriod">
<Parameters>
<Parameter Name="startTime" Type="string">
<DefaultValue>1970-01-01T01:00:00</DefaultValue>
</Parameter>
<Parameter Name="endTime" Type="string">
<DefaultValue>2016-07-15T01:00:00</DefaultValue>
</Parameter>
<Parameter Name="alertAttribute" Type="string">
</Parameter>
```
The query contains the function name and parameter names. You can change the function name and the parameters depending on the function you use (see reporting functions) - see also the highlighting in the figure below.

Figure 3. Microsoft SQL server Report Builder - Dataset Properties
On the left-hand side of the window select the Fields option. Enter the fields that are returned by the specified function (see the Reporting functions of the Online help).

Note: Hyphens may not be used for the field names!

Figure 4. Microsoft SQL server Report Builder - Dataset fields
Select the options for the Collation, Case sensitivity etc. (see figure below).

Figure 5. Microsoft SQL server Report Builder - Dataset options
Dynamic parameter values must be specified in the “Parameters” section of the dataset properties.

Figure 6. Microsoft SQL server Report Builder - Dataset parameters

Take care when interpreting data point values numerically by rendering them graphically (e.g. in charts). Internally, the Reporting Services XML extension stores numeric data returned by the gSOAP service as strings. This results in unexpected behavior when attempting to interpret values on systems with mixed language configurations. For example, the SOAP value 123.456 might be numerically interpreted as 123456 because the expression evaluation might expect a comma instead of a full stop as a decimal delimiter. This problem can be avoided by using fixed culture parsing expressions instead of the plain value:

=Double.Parse(Fields!value.Value, System.Globalization.CultureInfo.CreateSpecificCulture("en-US"))

Figure 7. Parsing expression in the WinCC_OA_LineChart.rdl file

Example queries

A regular SOAP request (in the Microsoft SQL Server Report Builder) to retrieve the names of available WinCC OA data points could look like this:

<?xml version="1.0"?>
<SOAP-ENV:Envelope
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body>
<m:dpNames xmlns:m="urn:Siemens.OA">
<dpPattern xsi:type="xsd:string">Reporting*</dpPattern>
<dpType xsi:type="xsd:string">ANALOG1</dpType>
</m:dpNames>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

This is the corresponding XmlDP query:

<Query>
<Method Namespace="urn:Siemens.OA" Name="dpNames">
<Parameters>
<Parameter Name="dpPattern" Type="string">
<DefaultValue>Reporting*</DefaultValue>
</Parameter>
<Parameter Name="dpType" Type="string">
<DefaultValue>ANALOG1</DefaultValue>
</Parameter>
</Parameters>
</Method>

Use the following query to retrieve runtime data of the WinCC OA data points from the gSOAP interface:

<Query>
<Method Namespace="urn:Siemens.OA" Name="dpGetPeriod">
<Parameters>
<Parameter Name="startTime" Type="date"></Parameter>
<Parameter Name="endTime" Type="date"></Parameter>
<Parameter Name="bonus" Type="integer">
<DefaultValue>0</DefaultValue>
</Parameter>
<Parameter Name="dpName" Type="string"></Parameter>
</Parameters>
</Method>

In this case no default values are specified for the three of the four parameters. Thus, additional configuration in the “Parameters” section of the dataset properties window is not required.