Reporting forms

I will tell you about the reporting system we have developed and implemented at one of the dispatching facilities

At the end of all the work, the Customer had a need for reports, which were discussed earlier, but rather as something secondary. In fact, this is a rather responsible link in the system, since it carries an economic component - numbers. All data in the tables is requested by a date selection from the SQLite database, processed by FBScript and displayed in forms developed in Visual Studio. 

In our task, we used the following reference material Sergey Moiseenko "SQL Tasks and Solutions"

The implementation of the reports began with the fact that at the end of the general commissioning, the Customer gave us seven tables related to each other, which had to be interactively recalculated and memorized.

Obviously, this is the task of the application (script) when accessing a selection to any database. Masterscada has its own database, but there was no paid tool for accessing it, and we had to make an application, create our own database and DBMS on SQLite. Masterskada, of course, has its own report creation wizard, but after understanding it, it was decided to develop our own subprogram that allows us to respond flexibly to any requests from our Customer. This decision was dictated by the need to inherit this reporting system from any other SCADA systems.

In Masterskade, a FB script was written that runs 1 time per second, polls variables and saves them in SQLite. Visualization (report forms) were developed in Visual Studio 2017. Report forms select the necessary data from the database (for a specific month) and form a table for display. The database at Masterskada is not closed, but we had difficulty in additional purchase of a tool for working with this very database. The fact is that the Customer and the integrator did not know that it was necessary to purchase an application for Masterskada, which would allow working with the database (recording data for further processing). By that time, the money had already been agreed upon and no one was going to buy anything - they had to develop their own application.

I also had to tinker with the choice of a DBMS (a DBMS is a program for working with a database). Here the difficulties were associated with the peculiarities of the Windows 7 operating system and the not very advanced computer configuration. Everything had to be pretty optimized to ensure stable operation of the system.

Why choose SQLite? The entire database consists of a single data file, which ensures the highest level of portability, i.e. nothing needs to be installed on the computer. I created a database, threw it into the project - and everything works great, no server is needed + we have a small load, there are no huge data arrays.

It turned out to be convenient that Masterskade has the ability to output ActiveX, which made it possible to place reports directly in the format of mnemonic diagrams without leaving the application using operator rights. Masterscada allows you to connect custom components/interfaces written in Visual Studio. The principle of operation is quite simple: visualization is written in Visual Studio - our reports, a library is assembled - dll, and then this dll is simply connected to Mastercade via ActiveX. Then the written user interface is dragged with the mouse in Masterskade to the right place. Thus, using the tools of Visual Studio, you can well finish the functionality of Mastercade.

For comparison, SimpleScada and Teslascada do not have the ability to add OLE objects to the project, which makes these software products not applicable for implementing this approach.

As a result, two databases were involved on the computer. One for charts and events (internal functions of Masterskada), the second for the reporting system. DBMS operate independently of each other, in different threads.

There were difficulties in the fact that Masterskada fell off episodically and we had to pretty much dig into the settings before we achieved stable operation of both the reporting application and Masterskada itself. It was quite difficult to process data at the beginning of the day, month, year. The thing is that the calculations had to take into account resets of integral indicators of metering devices, shutdown of a computer or SCADA system, as well as various factors, including human, such as short-term pump starts.

It was also possible to output reports to a connected laser printer, save a report (sample) for a specified period of time on disk.

The terms of reference were agreed on in appearance, these are about seven tables in MS Word.

Structural diagram of reports. How software components interact with each other, how everything works

Appearance of report windows, including the general report navigation window, monthly reports, and final annual reports. 

I will be glad if you express criticism, wishes, questions in the "comment" field

#Reports, #Masterskada, #Insat, #SQLite, #selection, #VisualStudio, #ActiveX, #DBMS, #annual, #monthly, #Excel, #SCADA