SQL, SQL Wizard

From WinCC OA Report 3.2 upwards you can execute SQL queries with the Excel Report and display the result as report. There exists already a SQL master template "SQL query". Open this template (see Master templates) to create manually a query or by using the SQL wizard.

For how to edit a master template, refer to the chapter Structure of a template

After opening the SQL template, you can use the menu entry SQL Wizard in menu Template:

It is highly recommended to reread the chapter SQL with the SQL panel.

Note that alias names cannot be used for SQL queries with Excel Report. Alias names can, however, be used for direct queries via the History Database. See chapter Adding a data point.

The SQL Wizard should not be used for queries of data points of a distributed system. It applies only these from the local system. Data points of a distributed system cannot be added to the list of the selected data points.

Working with the SQL Wizard

The following example creates a query, requesting all day from weatherstation_1 where the temperature was below 30° C.

You have to create a data point type weatherstation with three elements atmospheric_pressure, wind_velocity and temperature and select typeref for the element type with data point type ANALOG_1.. Two data points weatherstation_1 and weatherstation_2 have to be created and be exported to Excel (see Archiving/Compression). These data points have been imported as archive structure and data point list to the report (see Configuration WinCC OA Report). Afterwards you have to open the template SQL query, define the protocol type (Configuring the report type) and now open the menu template.

1. Click to SQL Wizard.

1. Select for this example Values and click on Forward.

1. Select _online.._value from Source and click ">" to move the value to the Target. Click on Forward.

1. Select from the combo boxes data point type, data point and data point element and click on ">" to apply the element. Click on Forward.

1. Select Value comparison from the combo box and further options as in the figure and click ">" to apply the option, then on Forward.

1. Click Forward (no specific timerange or bonus).

1. Select DP element, to sort according the size of the online value and click Finish.

Your SQL template has to look as follows. Save your result as SQLTemperature.xls in the directory Report.

For displaying the query in a report, respectively for further details on data analysis reread the corresponding chapters of the WinCC OA Report (see Creating a report, Linking data points to cells). A possible, very simple form, could look as follows: