8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

Tech Note 444

Configuring Shift Reports using ActiveFactory 9.1 Workbook All Tech Notes and KBC D documents and software are provided "as is" without warranty of any kind. See the Terms of Use for more information. Topic#: 002147 C reated: March 2006

Introduction This Tech Note is intended to provide ActiveFactory users with some information how to configure a work shift report using Microsoft® Excel Workbook and ActiveFactory version 9.1. In the example, the Shift report displays data for InSQL tags from yesterday's shifts. With the information in this document, you should be able to explore and find correct solutions for your particular application.

Application Versions Before you continue, make sure you have the following installed: IndustrialSQL Server Historian version 8.0 SP3 or later Microsoft Office Excel ActiveFactory version 9.1 or later Note: This Tech Note assumes the user has a basic working knowledge and understanding of InSQL Server, ActiveFactory, and Microsoft Excel. If you have problem or question on Microsoft Excel, please contact Microsoft at www.microsoft.com for assistance.

Calculating Time Using Excel We need to define the start- and end times of each shift. Assume that there are two shifts Shift 1 and Shift 2. They are defined as follows: Shift Shift Shift Shift

1 1 2 2

Start time: 5:00 AM End time: 2:00 PM Start time: 3:00 PM End time: Midnight

Two options are available when defining the start- and end times: You can enter the start and end time of each shift into cells, or; You can use a built-in Microsoft Excel function to calculate those times and dates. We are going to utilize the Excel's Today() worksheet function to calculate the shifts and store the information in a sheet named Shift calculation.

Today() Examples The following examples use the Today() function to provide various times: Note: For more information on the Today() function, please see Microsoft Excel Help or contact Microsoft technical support. Today(): Provides date and time values for today's midnight. Today() - 1: Provides date and time values for yesterday's midnight. Today() - 1 + (2/24): Provides date and time values for yesterday's 2:00 AM. https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

1/9

8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

Today() - 1 + (14/24): Provides date and time values for yesterday's 2:00 PM. The calculations for our shifts are as follows: Shift 1 Start time 5:00 AM: =Today() - 1 + (5/24) Shift 1 End time 2:00 PM: =Today() - 1 + (14/24) Shift 2 Start time 3:00 PM: =Today() - 1 + (15/24) Shift 2 End time Midnight: =Today()

Formatting the Cells Before entering the above information onto the Shift calculation sheet, make sure to format the cells ( C1, C2, C3, and C4 ) for the (highlighted) calculated information in the calculations above. 1. 2. 3. 4.

Open Excel and rename (default) Sheet1 Shift calculation. Click (select) cell C1. Press the Shift key and select cell C4. Right-click the selected cells and select Format cells.

5. When the Format Cells dialog box appears, select the following options: • Category = Custom • Type = m/d/yyyy h:mm:ss

F IGURE 1: F ORMAT C ELLS 6. Click OK to exit the Format Cells dialog box. 7. Type the following headings in cells A1, A2, A3, and A4 respectively: • Shift 1 Start time • Shift 1 End time • Shift 2 Start time • Shift 2 End time 8. Type the following times in cells B1, B2, B3, and B4 respectively: • 5:00 AM • 2:00 PM • 3:00 PM https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

2/9

8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

• Midnight 9. Type the following calculations in cells C1, C2, C3, and C3 respectively: • =Today() - 1 + (5/24) • =Today() - 1 + (14/24) • =Today() - 1 + (15/24) • =Today() The following figure displays what we have entered so far in the Shift calculation sheet:

F IGURE 2: SHIFT CALCULATION SHEET

Adding InSQL Tags to the Report Now it is time to add InSQL Server tags to the report. We will utilize the Shift calculation sheet as a container for the tags. For the sake of simplicity, we will use two InSQL Server system tags such as SysMinutesRun and SysPerfAvailableBytes. Any InSQL tag can be used by typing its name in a cell. 1. Type the tagname SysMinutesRun in cell A7 and SysPerfAvailableBytes in cell A8. The Shift calculation sheet should now look similar to the Figure 3 (below):

https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

3/9

8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

F IGURE 3: INSQL TAGS SYSMINUTESRUN AND SYSPERFAVAILABLEBYTES 2. Rename Sheet2 to Shift 1 and Sheet3 to Shift 2. 3. Select ActiveFactory/Connection Management from the main menu. 4. Use the Server List Configuration dialog box to connect with the IndustrialSQL Server Historian node. In this example, the InSQL Server BRIANLAP1 is used with wwuser Login and Password (Figure 4 below):

https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

4/9

8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

F IGURE 4: SERVER LIST C ONFIGURATION (C ONNECTION MANAGEMENT) 5. Click Close when the connection is configured. 6. Select the Shift 1 worksheet. 7. Select ActiveFactory/Tag Values/History Values. The History Values - Step 1 of 4 wizard appears (Figure 5 below). 8. Click the Select Cell(s) containing tag name(s) field. 9. Select the Shift calculation sheet. We want to include both cells A7 and A8 for both tags SysMinutesRun and SysPerfAvailableBytes (on this sheet) in the field. To do this, select the cell A7 and click cell A8 while pressing the Shift key. The Select Cell(s) containing tag name(s) field will now display the cell reference similar to 'Shift calculation'!$A$7:$A$8 (Figure 5 below).

https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

5/9

8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

F IGURE 5: INSERT C ELLS C ONTAINING TAG NAMES 10. Click Next. The History Values - Step 2 of 4 wizard appears (Figure 6 below). 11. Click the Select cell for output field. 12. Select cell A1 on the Shift 1 sheet.

F IGURE 6: SELECT OUTPUT CELLS 13. Click Next. Leave the default settings on the History Values - Step 3 of 4 Wizard. 14. Click Next. The History Values - Step 4 of 4 wizard appears (Figure 7 below). 15. Select the Absolute time radio button and the radio button at the left of the two empty fields for https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

6/9

8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

(Shift 1's) start- and end times, respectively. 16. Click within the field on the left. 17. Go to the Shift calculation sheet and select cell C1. 18. Click within the blank field on the right and select cell C2 on the Shift calculation sheet.

F IGURE 7: ABSOLUTE TIME C ELL SELECTION 19. Click Finish. The Shift 1 report should now be generated:

https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

7/9

8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

F IGURE 8: SHIFT 1 REPORT ActiveFactory inserts a function that returns values at regular time intervals (Cyclic retrieval) for the specified time period and cell range. It is also possible to return values when they change (Delta retrieval). The functions are displayed in Figures 8 and 9. 20. Repeat the previous steps for Shift 2 using cells C3 and C4 in the Step 4 (of 4) wizard for Shift 2's start- and end times, respectively. The Shift 2 report should now be generated and appears similar to Figure 9 (below):

F IGURE 9: SHIFT 2 REPORT The report can now be converted (as a static report) or reused with different value sets. Click the following icon to view this file in .pdf format:

B. Nguyen and C . Boutcher Tech Notes are published occasionally by Wonderware Technical Support. Publisher: Invensys Systems, Inc., 26561 Rancho Parkway South, Lake Forest, C A 92630. There is also technical information on our software products at Wonderware Technical Support For technical support questions, send an e-mail to [email protected].

back to top ©2013 Invensys Systems, Inc. All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying, recording, broadcasting, or by anyinformation storage and retrieval system, without permission in writing from Invensys Systems, Inc. Terms of Use.

https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

8/9

8/22/13

Configuring Shift Reports using ActiveFactory 9.1 Workbook

https://wdnresource.wonderware.com/support/kbcd/html/1/t002147.htm

9/9