8/22/13 HMI Reports: Creating Alarm Database (WWALMDB) Reports
Tech Note 663
HMI Reports: Creating Alarm Database (WWALMDB) Reports
All Tech Notes, Tech Alerts and KBCD documents and software are provided "as is" without warranty of any kind. See the
Terms of Use for more information. Topic#: 002427
Created: October 2009
Introduction
This Tech Note explains configuring HMI Reports to easily connect to and create reports against the Wonderware Alarm Database (WWAlmdb).
Recommendations
The following recommendations ensure the best results:
Use Consolidated Mode for your Alarm Database (wwalmdb).
Create a new SQL View and DSN Connection for the Alarm Database (wwalmdb) reporting.
Application Versions
HMI Reports
Microsoft SQL Server 2008
Creating the SQL View and the DSN Connection
Before you can generate the reports, you must create a SQL View and a DSN (Data Source Name) connection.
To create a new SQL View
Create a new SQL View for your Alarm Database reports. You will connect to and create reports using this new View.
1. Open SQL Management Studio and click New Query.
FIGURE 1: NEW SQL QUERY
The query creates a new SQL View in the Alarm Database. In this example, the View is called v_HMIReportsAlarms.
USE [WWALMDB] GO
/****** Object: View [dbo].[v_HMIReportsAlarms] Script Date:09/15/09 ******/ SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON GO
CREATE VIEW [dbo].[Qv_HMIReportsAlarms] AS
SELECT dbo.AlarmConsolidated.AlarmId, dbo.AlarmMaster.TagName, dbo.AlarmConsolidated.AlarmTime,
CASE WHEN dbo.AlarmConsolidated.ReturnTime = '9999-12-12 23:59:59.997' THEN NULL WHEN dbo.AlarmConsolidated.ReturnTime = '1900-01 -01 00:00:00.000' THEN NULL ELSE dbo.AlarmConsolidated.ReturnTime END AS ReturnTime,
dbo.AlarmMaster.GroupName, dbo.AlarmConsolidated.Priority,
CASE WHEN dbo.AlarmConsolidated.AckTime = '9999-12-12 23:59:59.997' THEN NULL WHEN dbo.AlarmConsolidated.AckTime = '1900-01-01 00:00:00.000' THEN NULL ELSE dbo.AlarmConsolidated.AckTime END AS AckTime
FROM dbo.AlarmMaster INNER JOIN dbo.AlarmConsolidated ON dbo.AlarmMaster.AlarmId = dbo.AlarmConsolidated.AlarmId
WHERE (dbo.AlarmConsolidated.AlarmTime > DATEADD(dd, - 90, GETDATE())) AND (dbo.AlarmConsolidated.ReturnTime < DATEADD(mi,
ABS(dbo.AlarmConsolidated.AlarmTimeZoneOffset), GETDATE()))
8/22/13 HMI Reports: Creating Alarm Database (WWALMDB) Reports
FIGURE 2: CREATE VIEW QUERY SUCCESSFUL
You can then see your new View in the list of WWALMDB Views:
FIGURE 3: NEW VIEWIN WWALMDB
For this Tech Note, we will create a System DSN; however, you can create a User, System or File DSN for this purpose.
1. Click Start > Control Panel > Administrative Tools > Data Sources (ODBC). 2. Click the System DSN tab, then click Add to create a new Data Source. 3. Select the SQL Native Client driver, then click Finish (Figure 4 below).
FIGURE 4: CREATEA SYSTEM DSN
4. Name your DSN and select your server. The description is optional (Figure 5 below).
FIGURE 5: CREATEA NAMEAND SERVER CONNECTION
8/22/13 HMI Reports: Creating Alarm Database (WWALMDB) Reports
FIGURE 6: LOGIN ID AND STRONG PASSWORD
6. Click Next.
Note: It is highly recommended you replace the defaults with strong Login ID and Passwords. Consult your system administrator for their direction.
7. Click the Change the default database to option and choose WWAlmdb from the list. If necessary, click the ANSI options shown in Figure 7 (below).
FIGURE 7: DEFAULT DATABASEIS WWALMDB
8. Click Next, then Finish.
9. Click Test Data Source to make sure the connection was configured correctly (Figure 8 below). 10. When the test completes successfully, click OK to close the dialog. If the test completes
FIGURE 8: TESTTHE DATA SOURCE CONNECTION
Configure the Driver Definition for the Alarm Database
Now that you've created a DSN, you can configure the Alarm Database Driver Definition. 1. Open HMI Reports Studio and select Logger/Driver Configuration from the main menu. 2. Type a Source Name in the Source Definition area. This can be any name.
3. To define the Alarm Database Driver, select ODBC History Access from the History Driver list.
FIGURE 9: DEFINETHE DATA SOURCE
4. Click the Configure button to the right of the History Driver field. The Database Definition window appears.
5. Select the DSN you created from the DSN File list and type the User Name and strong Password (SQL). 6. In the Select database type area, select Column-Item structure from the list.
7. Click the Connect button to make sure the connection is successful.
The Alarm History Data and Item History Data fields are populated automatically. 8. Highlight the new view Qv_HMIReportsAlarms, in the Alarm History Data area.
9. Configure the Table Fields for Alarm ID, Alarm Text, Alarm Priority, Start Time, Ack Time and End Time from their lists.
10. Click Done when you finish.
8/22/13 HMI Reports: Creating Alarm Database (WWALMDB) Reports
FIGURE 10: DATABASE DEFINITION WINDOW
FIGURE 11: NEW SOURCE DEFINITION
Note: Be sure to click the Add Definition button after configuring your Source Definition or the definition will not be saved.
Now you are ready to connect to Alarm Database Data for use in your reports.
Create a Simple Alarm Database Report
In this section we will create two different types of Alarm Reports. One using the Free SQL Query Table and
One using the Alarm Table.
In HMI Reports Report Design Studio
1. Create a Free SQL Query Table and run a query against the View you just created. 2. Create a Report (default settings are ok).
8/22/13 HMI Reports: Creating Alarm Database (WWALMDB) Reports
FIGURE 12: FREE SQL QUERY TABLE REPORT
FIGURE 13: CONFIGURE APPEARANCE OPTIONS
5. Figure 14 (below) shows the data returned using the Free SQL Query Table.
FIGURE 14: FREE SQL QUERY TABLE RESULTS
8/22/13 HMI Reports: Creating Alarm Database (WWALMDB) Reports
FIGURE 15: ADDAND CONFIGURETHE ALARM OBJECT
7. Click Edit List button to configure the priority filter.
FIGURE 16: CONFIGURE ALARM FILTER OPTIONS
9. Click Add Filter to add it, then OK to close the Alarm Filter Manager window.
Figure 17 (below) shows a sample of data returned in this report using the Alarm Table.
FIGURE 17: SAMPLE REPORT USINGTHE ALARM TABLE
Additional Resources
Tech Note 601: HMI Reports: Creating a Historian (InSQL) Driver Configuration
Tech Note 620: Preventing Column Misalignment with HMI Reports Generated by Free SQL Table
Click the following icon to view this file in .pdf format:
A. Rantos
Tech Notes are published occasionally by Wonderware Technical Support. Publisher: Invensys Systems, Inc., 26561 Rancho
Parkway South, Lake Forest, CA 92630. There is also technical information on our software products at Wonderware Technical Support.
8/22/13 HMI Reports: Creating Alarm Database (WWALMDB) Reports