Control.com - Nerds in Control
from the Engineering department...
SCADA for existing SQL and graphic reports
Can anyone recommend a good SCADA for me please? It has to collect information from existing SQL database, be able to display reports in bar graph, pie graph as well as tables. It should be able to export reports to MS Office format for analysis. Thx.
If the information is already in a SQL database, in what way are you "collecting" it, and why do you need a SCADA? If you are just reporting out of a database, then wouldn't you want an ordinary database reporting package? There are lots of these. Enter "database reporting" into Google and you'll come up with lots of these.
If the reports are routine rather than ad-hoc, you can also do this with the scripting language of your choice. Pie charts and bar charts are common chart types. The cost of doing something like this is zero.
People also do simple web applications for these sorts of things as well. You just need Apache, PHP, and a chart library.
If the reports are routine rather than ad-hoc, you can also do this with the scripting language of your choice. Pie charts and bar charts are common chart types. The cost of doing something like this is zero.
People also do simple web applications for these sorts of things as well. You just need Apache, PHP, and a chart library.
I have also seen at one plant an Excel reporting tool that automatically generates predefined spreadsheets based on user criteria using a scheduler. The end users see only a finished spreadsheet that they can copy to their own desktop and edit at will. Only the DBA has to mess with the database itself.
MB
--
Michael Batchelor
www.IndustrialInformatics.com
Industrial Informatics, Inc.
3281 Associate Dr.
N. Charleston, SC 29418
843-329-0342 x111 Voice
843-329-0343 FAX
MB
--
Michael Batchelor
www.IndustrialInformatics.com
Industrial Informatics, Inc.
3281 Associate Dr.
N. Charleston, SC 29418
843-329-0342 x111 Voice
843-329-0343 FAX
In reply to Michael Batchelor: With OpenOffice Calc, you can make SQL queries to a database directly from the spreadsheet and use them to populate the spreadsheet with data. MS Office Excell can probably do something similar. It is likely that what you saw was a report which was created using an elaborate spreadsheet macro.
It isn't an approach that I would recommend though. Spreadsheets are fine for fiddling with numbers. When used to create an application though it is almost impossible for anyone other than the original author to figure how they work (or why they stop working).
Someone who feels comfortable enough to install and configure a SCADA system to produce reports should have no trouble writing some scripts to produce reports. You don't need the SCADA system for this though. Python, Perl, Ruby, etc. all have the capabilities to turn out HTML, PDFs, or whatever else you want. The computer could be configured to run them on a scheduled basis to turn out the reports automatically.
If someone was planning on hiring a third party to do the job, then they should be talking to someone who does custom web applications. Making pretty reports from databases, exporting data to spreadsheets, etc. is absolutely routine and there are lots of people doing this sort of freelance work everywhere. Ten or fifteen years ago, people would have done this sort of thing using a VB client to spit out reports onto paper. Today they use Apache with PHP (or Python or Ruby) to show them on a web browser.
It isn't an approach that I would recommend though. Spreadsheets are fine for fiddling with numbers. When used to create an application though it is almost impossible for anyone other than the original author to figure how they work (or why they stop working).
Someone who feels comfortable enough to install and configure a SCADA system to produce reports should have no trouble writing some scripts to produce reports. You don't need the SCADA system for this though. Python, Perl, Ruby, etc. all have the capabilities to turn out HTML, PDFs, or whatever else you want. The computer could be configured to run them on a scheduled basis to turn out the reports automatically.
If someone was planning on hiring a third party to do the job, then they should be talking to someone who does custom web applications. Making pretty reports from databases, exporting data to spreadsheets, etc. is absolutely routine and there are lots of people doing this sort of freelance work everywhere. Ten or fifteen years ago, people would have done this sort of thing using a VB client to spit out reports onto paper. Today they use Apache with PHP (or Python or Ruby) to show them on a web browser.
It was a commercial product named XL Reporter from Sytech that automatically generated scores of report spreadsheets hourly, end-of-shift, daily, weekly, monthly, and quarterly and dropped them into directories for the various "consumers" to use.
The finished product report spreadsheets were devoid of macros and completely disconnected from the original data structures to protect the underlying data in the tables. A scheduler engine runs on the same server as the SQL server, and there was a template generator facility that the administrator could use to edit/control the formats and schedules. They ran various reports from the SCADA system, from the shipping and receiving database, and from the financial system. I think the manufacturer targets the HMI industry, but it works on any data.
I agree one could probably do the same thing in either Excel (or Open Office Calc) with macros or in any good data manipulation language, but this thing worked, left data in a format that was familiar to the all the end users, and the complete engine cost less than I would have charged to write the same functionality as a project. (Granted they had some configuration time involved in setting it up not included in the licensing cost.)
When I first saw it I didn't expect to be impressed, but after working with it a day I'd recommend it to a future customer whole-heartedly. I had seen it advertised occasionally before, but never had a chance to work with it. The important features were A) it worked flawlessly, and B) it left an end product the users understood completely without requiring any explanations.
Every manager, QA person, shipping clerk, etc in the plant that needed regular structured data had it readily available in a format they could manipulate as necessary. Obviously ad hoc information had to be extracted and formatted on an ad hoc basis, but the regular structured stuff just appeared to the users as if by magic.
I have only used it at that one facility and they were connected to MS-SQL server as the back-end, so I didn't try to push it to it's limits. But it should work fine regardless of what back end you might want to use, Oracle, PostgreSQL, DB2, or anything else you could connect with.
The biggest caveat to most SCADA applications dumping data into an SQL server is that the integrator often doesn't understand data well, so the data model is either a mess or a giant flat file. Yes, the data is there, but there's no way to extract it cleanly. This problem often exists regardless of what "reporting tool" you might want to use to extract that data.
MB
--
Michael Batchelor
www.IndustrialInformatics.com
The finished product report spreadsheets were devoid of macros and completely disconnected from the original data structures to protect the underlying data in the tables. A scheduler engine runs on the same server as the SQL server, and there was a template generator facility that the administrator could use to edit/control the formats and schedules. They ran various reports from the SCADA system, from the shipping and receiving database, and from the financial system. I think the manufacturer targets the HMI industry, but it works on any data.
I agree one could probably do the same thing in either Excel (or Open Office Calc) with macros or in any good data manipulation language, but this thing worked, left data in a format that was familiar to the all the end users, and the complete engine cost less than I would have charged to write the same functionality as a project. (Granted they had some configuration time involved in setting it up not included in the licensing cost.)
When I first saw it I didn't expect to be impressed, but after working with it a day I'd recommend it to a future customer whole-heartedly. I had seen it advertised occasionally before, but never had a chance to work with it. The important features were A) it worked flawlessly, and B) it left an end product the users understood completely without requiring any explanations.
Every manager, QA person, shipping clerk, etc in the plant that needed regular structured data had it readily available in a format they could manipulate as necessary. Obviously ad hoc information had to be extracted and formatted on an ad hoc basis, but the regular structured stuff just appeared to the users as if by magic.
I have only used it at that one facility and they were connected to MS-SQL server as the back-end, so I didn't try to push it to it's limits. But it should work fine regardless of what back end you might want to use, Oracle, PostgreSQL, DB2, or anything else you could connect with.
The biggest caveat to most SCADA applications dumping data into an SQL server is that the integrator often doesn't understand data well, so the data model is either a mess or a giant flat file. Yes, the data is there, but there's no way to extract it cleanly. This problem often exists regardless of what "reporting tool" you might want to use to extract that data.
MB
--
Michael Batchelor
www.IndustrialInformatics.com
In reply to Michael Batchelor: I had a look at the web site for XL Reporter, and it does look interesting. They also have an HTML version which just turns out HTML pages instead of spreadsheets.
However, if I understand it correctly, it doesn't look very flexible from a user's perspective. Once you set it up, it turns out a report, and you get whatever you get. The user can't pick arbitrary baselines interactively. The HTML version has the same limitations, as it's semi-static and not a dynamic web site.
If I was to offer it as a solution to someone, I would make sure they know the limitations of it. It may be fine for what they need, but it often turns out that "needs" expand with use.
I did a small project a couple of years ago that turned out reports in spreadsheet format. In this case, I did it by writing a set of scripts. In hind sight though, I think a web site would have enabled better and more understandable reports while actually being simpler.
I also got a nice introduction to spreadsheet file format incompatibilities. I used Microsoft's latest and greatest XML based spreadsheet file format which was supposed to replace "xls". Microsoft then dropped that file format and came out with a completely different XML based file format ("xlsx"). This new format is in turn scheduled to be replaced by a yet newer one in 3 or 4 years. That is, unless they cave into customer pressure and support the same ODF format that everyone else is standardising on. I think I would rather just stick with HTML.
However, if I understand it correctly, it doesn't look very flexible from a user's perspective. Once you set it up, it turns out a report, and you get whatever you get. The user can't pick arbitrary baselines interactively. The HTML version has the same limitations, as it's semi-static and not a dynamic web site.
If I was to offer it as a solution to someone, I would make sure they know the limitations of it. It may be fine for what they need, but it often turns out that "needs" expand with use.
I did a small project a couple of years ago that turned out reports in spreadsheet format. In this case, I did it by writing a set of scripts. In hind sight though, I think a web site would have enabled better and more understandable reports while actually being simpler.
I also got a nice introduction to spreadsheet file format incompatibilities. I used Microsoft's latest and greatest XML based spreadsheet file format which was supposed to replace "xls". Microsoft then dropped that file format and came out with a completely different XML based file format ("xlsx"). This new format is in turn scheduled to be replaced by a yet newer one in 3 or 4 years. That is, unless they cave into customer pressure and support the same ODF format that everyone else is standardising on. I think I would rather just stick with HTML.
In reply to Michael Griffin: You are exactly correct that it is not one tiny bit flexible for the end consumer. What the administrator puts together is what the consumer gets, no exceptions. As an ad hoc or drill down tool it is of no value whatever. But for highly structured periodic reports it works great; set it up and forget about it.
Obviously the plant would have to determine if it fills a need they have. And as you pointed out, it isn't "configurable" to the end consumer so it's not a good fit for some applications. I though of it like an old style UNIX utility - it may only do one thing, but it does that one thing really well, and if that's what you need then there's the answer. If you need something else look somewhere else.
I didn't have access to any of the other products in their line, just the XL Reporter. The support staff was pretty good at helping me move the (dreaded MS-Win style) licenses to the new SCADA SQL server, and I assume they are probably OK for other technical issues. But there really weren't any other issues. We upgraded from an older version to the new version to run on Windows Server 2003, and I had to edit the path to the templates for about 70 reports because we changed the directory structure around some on the new machine. But once I got familiar with it everything went very smoothly.
MB
--
Michael Batchelor
www.IndustrialInformatics.com
Obviously the plant would have to determine if it fills a need they have. And as you pointed out, it isn't "configurable" to the end consumer so it's not a good fit for some applications. I though of it like an old style UNIX utility - it may only do one thing, but it does that one thing really well, and if that's what you need then there's the answer. If you need something else look somewhere else.
I didn't have access to any of the other products in their line, just the XL Reporter. The support staff was pretty good at helping me move the (dreaded MS-Win style) licenses to the new SCADA SQL server, and I assume they are probably OK for other technical issues. But there really weren't any other issues. We upgraded from an older version to the new version to run on Windows Server 2003, and I had to edit the path to the templates for about 70 reports because we changed the directory structure around some on the new machine. But once I got familiar with it everything went very smoothly.
MB
--
Michael Batchelor
www.IndustrialInformatics.com
Check out FactoryPMI from Inductive Automation. It will do all those things you mention. If you don't need to connect to an OPC (PLC) source, then as mentioned above, you can roll your app with a reporting or web solution.
----
Nathan Boeger
http://notanotherindustrialblog.blogger.com
"Design Simplicity Cures Engineered Complexity"
----
Nathan Boeger
http://notanotherindustrialblog.blogger.com
"Design Simplicity Cures Engineered Complexity"
Hi,
If you are looking for a good SCADA to create, log and handle data on SQL database, the best option is FactoryLink!.
Maybe, I will win enemies with what I am going to write but, there is no SCADA able to work with databases as well as FactoryLink does
(no offense).
Instead, if you already created your DB/tables and your data is logged on perfectly and you only need a Database Reporting tool, your best option (and free!) is to use MS SQL Server 200x Reporting Services Service.
It's is an Add On of SQL Server 200x therefore, it's free to download and use it. With it, you will have the best tables and graph types, export your reports to PDF, Excel, XML, Text, etc files and access them via web at any time (including security levels).
Best regards.
Saludos.
Gustavo A. Valero P.
BIConsulting C.A.
Valencia - Venezuela
gustavo.valero @ biconsulting. com
If you are looking for a good SCADA to create, log and handle data on SQL database, the best option is FactoryLink!.
Maybe, I will win enemies with what I am going to write but, there is no SCADA able to work with databases as well as FactoryLink does
(no offense).
Instead, if you already created your DB/tables and your data is logged on perfectly and you only need a Database Reporting tool, your best option (and free!) is to use MS SQL Server 200x Reporting Services Service.
It's is an Add On of SQL Server 200x therefore, it's free to download and use it. With it, you will have the best tables and graph types, export your reports to PDF, Excel, XML, Text, etc files and access them via web at any time (including security levels).
Best regards.
Saludos.
Gustavo A. Valero P.
BIConsulting C.A.
Valencia - Venezuela
gustavo.valero @ biconsulting. com
This thread follows all the old/new paradigms of manufacturing reporting (1980-Present). I enjoyed reading about all the camps (Microsoft vs. open source, COTS vs. custom, database vs. Excel,...) that have formed over the years. I think some valuable best practices are sprinkled in between the product accoldades and implementation silos. Here is my view of those best practices.
1. Never generate reports from a live data source (OPC or other). Always collect data first in a write transcation optimized data store (MS-SQL, mySQL, access,...).
2. Determine if reading/extracting data from this write optimized database will impact the writing of data. If yes, then replicate the data to a separate database for reading (ie. data warehouse).
3. Develop formal and ad hoc reporting capabilities on this database. Formal reports (ie. WIP, financial, TPT, ...) should not be editble and therefore tools like Excel are not appropriate. Using tools like Crystal Decisions or other enterprise reporting is ususally the best way to go. Ad hoc report, however, should be implemented in a fashion that allows the user to slice/dice and modify the output (although not the data).
4. The data extraction timing can either be batch (daily), real time (online query), or queued (run when resources available). Batch is typically ok for formal reports with formal date constraints and real time is better for ad hoc reports that want up to the minute information.
The nice thing about using these best practices is that it allows dozens of COTS and custom implementations without much overhead (time/money).
Gil Lund
Lund Engineering
www.lundeng.com
Seattle, WA
gwlund@yahoo.com
1. Never generate reports from a live data source (OPC or other). Always collect data first in a write transcation optimized data store (MS-SQL, mySQL, access,...).
2. Determine if reading/extracting data from this write optimized database will impact the writing of data. If yes, then replicate the data to a separate database for reading (ie. data warehouse).
3. Develop formal and ad hoc reporting capabilities on this database. Formal reports (ie. WIP, financial, TPT, ...) should not be editble and therefore tools like Excel are not appropriate. Using tools like Crystal Decisions or other enterprise reporting is ususally the best way to go. Ad hoc report, however, should be implemented in a fashion that allows the user to slice/dice and modify the output (although not the data).
4. The data extraction timing can either be batch (daily), real time (online query), or queued (run when resources available). Batch is typically ok for formal reports with formal date constraints and real time is better for ad hoc reports that want up to the minute information.
The nice thing about using these best practices is that it allows dozens of COTS and custom implementations without much overhead (time/money).
Gil Lund
Lund Engineering
www.lundeng.com
Seattle, WA
gwlund@yahoo.com
Hi,
I am a SCADA engineer and no paid advocate of reporting tools, but I can suggest crystal reports. By the sounds of it you don't need a realtime scada package. SCADA is used to extract the data from PLANT controllers (eg PLC/DCS).
http://www.crystalreports.com/
http://www.youtube.com/watch?v=yKiyUomr6k8
It is a stable conreporting product thats been around for a long time.
Ian Finlay
Freelance SCADA/C++/VB/.NET Electrical Engineer
www.earthed.net.au
I am a SCADA engineer and no paid advocate of reporting tools, but I can suggest crystal reports. By the sounds of it you don't need a realtime scada package. SCADA is used to extract the data from PLANT controllers (eg PLC/DCS).
http://www.crystalreports.com/
http://www.youtube.com/watch?v=yKiyUomr6k8
It is a stable conreporting product thats been around for a long time.
Ian Finlay
Freelance SCADA/C++/VB/.NET Electrical Engineer
www.earthed.net.au
If you buy Visual Studio 2008, or download the trial version (free) you can use either Crystal Reports or SQL Server reporting services (SRSS) to create a simple application.
Both tools are really powerful and are really the leading report development tools. I have use both extensively and there are advantages and disadvantages to both system but for most purposes they will suffice.
The good news it is easy to connect and work with SQL as the database.
Goodluck.
Both tools are really powerful and are really the leading report development tools. I have use both extensively and there are advantages and disadvantages to both system but for most purposes they will suffice.
The good news it is easy to connect and work with SQL as the database.
Goodluck.
Take a look at Eramosa SCADA Reporting (e.SCADA.r). e.SCADA.r is a web based reporting tool that enables non-technical users to produce dashboards of charts and data grids within minutes. The e.SCADA.r reporting environment allows data stored in relational database sources to be visualized with data from historical SCADA database sources. e.SCADA.r is integrated with popular off the shelf historian products and provides users with a full list of tag names, descriptions and engineering units directly from the source database. Reports can be accessed by multiple users without requiring the installation of proprietary software. e.SCADA.r is open-source software distributed under the GNU General Public License (GPL).
Your use of this site is subject to the terms and conditions set forth under Legal Notices and the Privacy Policy. Please read those terms and conditions carefully. Subject to the rights expressly reserved to others under Legal Notices, the content of this site and the compilation thereof is © 1999-2010 Nerds in Control, LLC. All rights reserved.
Users of this site are benefiting from open source technologies, including PHP, MySQL and Apache. Be happy.
Fortune
This land is full of trousers!
this land is full of mausers!
And pussycats to eat them when the sun goes down!
-- Firesign Theater
