Using Excel to Report on Wonderware Alarms

Problem Statement

Intouch and Application Server log alarms to the WWALMDB. This Tech Note will walk you through creating a custom View on the WWALMDB that specifies the data to be shown in excel as well as a time period to be queried. So typically you could create a View to show 24 hours of data, 1 week of data, and 60 days of data and create separate reports in excel viewing this data.

Solution Details

If you look at the WWALMDB it contains several built in views already, however these views contain all the InTouch Alarm and Event data and may not be what you are looking for in a report.

So the first step is to create a view that will query the WWALAMDB tables or views and give you the data that you want.

To do this, right click the folder for Views and select new View. When creating a new view SQL Server will open in design mode and ask you to add a table. You can design you View this way or click cancel and manually insert your query to create the View.

The screenshot below is an example configuration of a View that queries the last day returning the EventStamp, Tagname, Area, and Value for all Unacked Alarms during that time period. The where clause uses the GETDATE function to calculate the current date and return any data that happened over the past day.

Close the View and SSMS will prompt you to give it a name. Name it appropriately and now you have created your view for an excel report.

Now you can open Microsoft Excel and connect to the View. To do this open excel and from the Data menu choose the option to Import External Data, then choose Import Data.

Excel will ask you to Select a Data Source, choose the option to create a New Source.

This will open the Data Connection Wizard. Choose Microsoft SQL Server and click next.

Enter in the Server Name and the User Name and Password to be used to connect to SQL server and click next.

In the drop down menu select the WWALMDB and select the appropriate view that you created and click next.

Enter a name for the Data Connection File and choose finish.

The select Data source window will reappear and you will have the newly created connection select, click open.

This will open the Import Data option and prompt you to select an output cell for the data.

The SQL Server Login page will appear. Enter in the appropriate User Name and Password and click OK.

The data from the View will appear in the spreadsheet along with an External Data tool bar that allows you to refresh the data.

Now you can save this spreadsheet and every time it is opened the spreadsheet will connect to the view and bring in the last days worth of data.