Reporting in Cimplicity

You must have Microsoft Query and Microsoft Excel loaded and installed on your computer to do this lab!

Notes on Installing Microsoft Excel:

Generally, installing Microsoft Excel is a straightforward process. However you must perform additional steps when loading Microsoft Excel in order to successfully generate reports. The procedures are somewhat different for Excel for 95 and 97 vs Excel in Office 2000.

Excel for Office 95 and 97

When you install Microsoft Excel from Office 95 or 97, make sure that you select the Microsoft Query Add-in in the Add-ins section. When using Excel 95 or 97, CIMPLICITY software expects xlodbc.xla and xlodbc32.dll to be in the C:\msoffice\excel\library\msquery directory. After you finish installing Microsoft Excel, do the following:

1. Locate xlodbc.xla and xlodbc32.dll.

2. Create the path C:\msoffice\excel\library\msquery.

3. Copy xlodbc.xla and xlodbc32.dll from their current directory to the new path.

If you do not do this, a Path not found error message displays when you attempt to open any of the report files.

Installing from Microsoft Office for Windows 95 CD:

When you install Microsoft Excel, do the following to install the Microsoft Query Add-in:

1. Select Converters, Filters and Data Access.

2. Click Change Option.

3. Select Data Access.

4. Click Change Option.

5. Select MS Query. It is not necessary to select any of the ODBC drivers at this point, as they will be installed with your CIMPLICITY HMI software.

6. Continue with your installation.

Installing from Microsoft Office for Windows 97 CD:

When you install Microsoft Excel, do the following to install the Microsoft Query Add-in:

1. Select Data Access.

2. Click Change Option.

3. Select Microsoft Query. It is not necessary to select any of the ODBC drivers at this point, as they will be installed with your CIMPLICITY HMI software.

4. Continue with your installation.

Excel for Office 2000

Installing from Microsoft Office 2000:

When you install Microsoft Excel for Office 2000, make sure you select the ODBC Add-in option under the Excel installation. With Office 2000 the files xlodbc.xla and xlodbc32.dll are loaded by default in the C:\Program Files\Microsoft Office\Library\MSQuery directory. This will work just fine for our example.

When you install Microsoft Excel, do the following to install the Microsoft Query Add-in:

1. Select Add or Remove Features.

2. Select Microsoft Excel for Windows.

3. Select Add-ins.

4. Select ODBC Add-ins and chose “Run from my computer”

5. Continue with the install.

Now on to this part of the Workshop

We’ve done lots of things so far in all our labs. We’ve created screens, trend charts, looked at data currently being collected and data that has been saved to a database. Now let’s run a report to view our logged data. CIMPLICITY HMI provides some pre-built reports that have been designed in EXCEL using Visual Basic. These reports use SQL (Structured Query Language) Commands to extract data from the CIMPLICITY log files which are stored in Access database files.

Use the Start button on the task bar to launch Windows Explorer. Scroll down to the CIMPLICITY directory. Under the CIMPLICITY/HMI directory folder there is a folder for “report.” Double-click on the report directory folder to open it and view the files. Notice that there are spreadsheets for Data, Alarm and Group. Also, notice that there are two versions of each - one for Office 95 and one for Office 97! The ones for Office 97 have "97" in their file name. Anyway, this was required due to some changes in the way that Excel works. For those of you on Office 2000, don’t worry, we will use the Office 97 versions. As you go on, please select the right version to match the version of Excel that you are using!

Remember when we configured logging for our project? We had several choices, but we chose to make a Group Log. So, we need to double-click on the SQLGROUP.XLS file for Office 95 or SQLGROUP97.XLS if you have Office 97 or Office 2000.

Now that the SQLGROUP spreadsheet is open, we can use it to run a report on our logged data. Click on the “Control” tab. This should be how the spreadsheet opened, but just in case, you now know how to get to the starting point.

In the dialog box replace “Group_Table_ID” with the name of our group log “DEMO.” Now click on “Generate Report.”

The Status window tells you what happened. In this case, Excel went out and fetched 2198 data records for the 3 points we configured to be logged.

Notice the tabs at the bottom of the spreadsheet. These let you look at the data you pulled in, as well as the macros that did all the work. Click on “Chart” at the bottom of the screen to view the chart for the data. This will give you a graphic look at your data.

Now, click on Data and take a look at the data records themselves. You can scroll around the spreadsheet to see more of the columns or more of the rows of data. And, since it’s a spreadsheet, all of the other powerful spreadsheet tools are available to you to perform additional data analysis.

AND since CIMPLICITY HMI supports a DDE interface to Excel, you can use calculations in this report to drive point values back into CIMPLICITY HMI and on down to the programmable controllers or devices!

Total integration - top to bottom using readily available office automation tools and CIMPLICITY HMI.

What if the data was logged to a Microsoft SQL Server database? Well with CIMPLICITY 5.0 you are doing just that since it now uses MSDE as the default database engine. If you click on the Set Parameters button, you will notice “CIMPLICITY Logging – Points” in the Data Source field. If you go to the Microsoft task bar and Start\Settings\Control panel you can then review the ODBC datasources setup on the computer. Open Data Sources (ODBC). If you go to the tab marked System DSN you can see the source CIMPLICITY Logging-Points is using the SQL Server Driver.

Now let’s go back to the spreadsheet. There are additional tabs that will let you view an error log, as well as the code used to configure both the chart and the report. These are created using standard Excel macro programming and SQL techniques. You can use the provided reports as models for your own! What if your users don’t have Excel on their PC’s? Well, Microsoft Access can also be used to generate reports. Or you can use the Access Developer’s kit to create reports that can be used on computers without Access. Or use any of a number of standard tools that can use ODBC to link into a Microsoft Access database! A variety of tools are available for this!

So, you can collect data with CIMPLICITY HMI. You can share that data with your other CIMPLICITY HMI users through screens and shared data. And you can log data in a standard database. Then you can generate reports or feed the data to other applications to analyze the information! Again, seamless integration at its finest!

Before you go off on your own, we need to stop the logging to prevent you from using up unnecessary disk space everytime you run the demo project. Go back to the Workbench and double-click on the Database Logger icon to bring up the CIMPLICITY Database Logger Configuration dialog box. Select the DEMO table and delete it to stop the logging of the DEMO points. Don’t worry – you will still be able to chart the historical data even without the table!