For demonstration purposes, lets assume we want to get a list of Instruments with some basic data from the Index (I/O Type, Location, Instrument Type), plus details of the I/O Channel for the Instrument (Panel, I/O Card, Channel). Perhaps we need to provide this data to our DCS Supplier, for them to import into their own applications.
Also, lets for argument sake, assume we also want to include the Specification Sheet number for each Instrument, just for demonstration purposes.
This means we're looking for data from the Index, Wiring and Specification modules of SPI and typically you wouldn't be able to produce a report like this in the SPI Browser module. You could perhaps use Infomaker, MS-Access, or a similar query tool, but these would require a deeper understanding of the SPI database and the resulting queries would be quite complex.
APS Reporter/SP makes this process a lot easier.
Firstly, we start with a New Report for our SPI Connection
Once the Query Builder window opens, drill down the table tree to find the Component table, this is the main Instrument table in SPI.
Double-click the Component table to add it to the Query Drawing pane, and select the fields we'd like to include in our query. I've selected the Instrument, Service, Function Type, Location, I/O Type, Loop Number and Loop Drawing fields.
Notice that in APSR we can select all of these fields directly from the Component table. In reality, in the database they are actually stored as part of five other tables, but APSR understands the database structure and resolves these fields for us.
We need to join the Control System Tag table to the Component table, which we do by dragging the Instrument ID (CMPNT_ID) field from one table and dropping it on the corresponding field in the other table.
We then can select the fields we need from the Control System Tag table. I've selected the Control System Tag Name, Panel, Strip and Channel names.
To get the Specification Sheet Number, we also need to add the Spec Sheet Data table. Again, double-click this to add it to the query drawing pane.
Again, we need to join the new table by dragging the Instrument ID (CMPNT_ID) field from the Component table to the Spec Sheet Data table. We can also select the Specification Sheet Number field at this time.
We need to make one more change to our query. By default, our results will ONLY include tags that have BOTH an I/O Channel assigned PLUS a Spec Sheet Number.
In this case, we actually want to include ALL Instruments in our results, so we need to change the Joins between the tables as shown in the image to the right (right-click on the Join line to see the menu).
That's It! In addition to the data, the results grid also has correct title for all the columns.
If this query were written in some other tool (Infomaker, MS-Access, etc), it would have required a minimum of twelve tables, and upwards of 15 joins. Also, the fields would be labelled with the standard database names and would need to be modified for our end-users.
Our query required a total of three tables and two simple joins. APSR was able to resolve the other tables and fields as required. Of course, if you're an SQL Guru, don't worry, the APSR Query Builder can be used like any other SQL Query tool, and the Virtual Tables & Fields can be mixed and matched with standard objects. In short, SQL Virtualisation adds a whole lot of power, without taking anything away.
If you'd like to try APSR for yourself, download the Free Version from the link to the right (the Free Version is not a time-limited trial, it will not expire, and can be used in production systems if required).
Regards,
Brian Raynor.
No comments:
Post a Comment