A few customers have asked us how to embed SQL Results in an InduSoft report. There are actually several ways of doing this, so we put the challenge to our InduSoft engineers who came up with some different options to share how they would go about accomplishing this.
Option One:
Let’s presume that we have a database table called ProcessResults that has five columns called StepNo, StartDateTime, Runtime, AvgTemp, MinTemp, and MaxTemp. Let’s further presume that we have some process that has some number of steps. While the process is running, summary information from each step is written to the table called ProcessResults such that the contents of the table look something like the following after the completion of the process.
Our objective is to create a Report in InduSoft where we can insert a formatted set of results in the body of the report. The following instructions presume that you already know how to create and run Reports in InduSoft, use the InduSoft built-in database commands to access a SQL-based database, and are adept at using VBscript.
First, we will create a named Database/ERP Connection to the desired database. Open the Tasks tab of the Project Explorer, expand the Database/ERP folder, right-click on the Connections folder, and click Insert as shown below.
This will open the Database Connections dialog.
Enter a name for your connection in the field labeled Name and configure the connection settings in accordance with the technical documentation for your desired database.
For the purposes of this example, we will name our connection myDB.
Next, we will create a Procedure to run the database query and return a formatted set of results. Open the Global tab of the Project Explorer, expand the Procedures folder, right-click on Main Procedures, and click Open as shown below.
The Main Procedures will open.
Add the following VBscript function called CreateProcessResultsTable at the bottom of your Main Procedures.
This Function runs a SQL Select statement to read the process results for the database and returns a formatted table as a text string.
We can add these results to an InduSoft Report definition by simply imbedding a call to the Function using RunGlobalProcedureOnServer().
To create the Report, Open the Tasks tab of the Project Explorer, right-click on the Report folder, and click Insert as shown below.
This will open a Report editor. Simple create a report similar to the one shown below inserting the call to RunGlobalProcedureOnServer() wherever you want to insert the process results.
Save the report and call it ProcessReport. Finally, run the report using the Report command and save it to disk,
Report(“Disk:ProcessReport.rep”)
Presuming a Report definition as shown above, the final report will look like the following.
Option Two:
First Create a Database connection to the desired database.
- Click on the Tasks tab of the Project Explorer.
- Expand Database/ERP folder, then right0click on Connections. Click insert and enter a Name into the name column of the new dialog.
- Then click the 3 dots button at the end of the Connection String field. This will bring up the “Data Link Properties” dialog. Configure the connection in accordance with the technical documentation for your desired database.
For the purposes of this example let us assume the name of the connection was configured as DB.
Now, you just need to create a function that queries (read) data from selected database and exports it to a report (e.g.: CSV file).
Here is a short example (assuming a table with the name “Table_1” and columns with the names “Time_Stamp”, “TankLevel”, “Pressure”, and “Temperature”)
In addition to the many training supplements InduSoft provides in the form of webinars and demonstration videos, it’s also possible to get conventional training at InduSoft’s facilities worldwide. The best way to save time in development of SCADA or HMI applications is to take advantage of the expert knowledge of the InduSoft team, and there’s no better stage for learning these important techniques than the guided hands-on SCADA software development training that InduSoft provides.
