SQLXFIRST returns a value directly from an external database (such as a SCADA Historian, LIMS...) using the SQL Query. If the result of the SQL Query is a table of more than 1 column by 1 row, SQLXRESULT must be used to retrieve other values.
SYNTAX:
SQLXFIRST(Col, Row, "Connection String","SQL Query", MaxColumns, MaxRows, Parameter1,…,Parameter20)
Col: Column number to retrieve result from
Row: Row number to retrieve result from
"Connection String": An ADO connection string to the external database or the name of an External Data Source. See External Data Source Setup for example connection strings. If your connection string contains quotes (") you MUST but the connection string in different cell and refer to the cell.
"SQL Query": SQL select statement to execute
MaxColumns: Specifies the number of columns in the resulting table
MaxRows: Specifies the number of rows in the resulting table
Parameter1..20: OPTIONAL. External values to be used substituted into the query, should the query refer to them. In SQL Query, use @Px@ to refer to the parameter, or #Px# to refer to the parameter as a date, $Px$ as a number.
NOTES:
You must install the OLE DB driver (provider) for your database on each PC that will use the report. See your database admin for the connection string and driver for any database you want to connect to.
EXAMPLES:
=SQLXFIRST(1,1,"Provider=SQLOLEDB;Data Source=LT006;initial catalog=Cimplicity;User Id=sa;Password=admin;OLE DB Services =-2;","SELECT * FROM EPA_LOG WHERE RIGHT(BatchNum, 1) = '@P3@' AND STARTTIME>='@P1@' and STARTTIME <'@P2@'",8,100,C4,K5,C3)
Returns the 1st column and first row from 8X100 table of records from the EPA_LOG table in the Cimplicity database on the LT006 MSSQL Server.
=SQLXFIRST(1,1,"Provider=ihOLEDB.iHistorian.1;User Id=;Password=;Data Source=IS001;","SELECT Avg(Value) from ihRawData WHERE SamplingMode = 'Lab' And Tagname = 'LT006.Simulation00001’ And TimeStamp >= '11/24/2003 00:00:00' And TimeStamp <= '11/24/2003 23:59:59' And NumberOfSamples = 1440 And Quality = 100",1,1,C4,K5,C3)
Returns the average value for tag LT006.Simulation00001 from an Ihistorian server via the OLEDB Ihistorian driver.
=SQLXFIRST(1,1,"ChemicalCosts","Select TOP 1 Price FROM ChemCosts WHERE Chem = 'Cl2' ORDER BY DATESTAMP DESC",1,1)
Returns the Price of the most recent entry from the ChemCosts table for Cl2. Uses the connection string defined in the External Data Source named "ChemicalCosts". Using the external data source allows you to hide connection information (Username and Password) for the users of the report.