To configure the export settings for query reports, go to: Navigation bar → SQL stats → Edit SQL stats.
See also: Create / Edit query reports

The following options are currently available for easy export of the query reports:
Email delivery, HTTPS retrieval (PULL), HTTPS export (PUSH), and FTP/SFTP.

Configure file format

CSV file

You can configure the file format in the File format section.
To ensure that the file can be read correctly in other systems, you can specify the file format for a CSV export. The following settings are available.

File name
You can specify the name of the CSV file to be exported. Variables can be used optionally. If you do not specify a file name, the name of the SQL report is automatically used as the file name.
Optional variables:

• _DATE_YMD_HHMMSS_ for timestamp
• _UNIXTIME_ for Unix time
• _VAR_COLUMN_NAME_ for column contents

The file extension .xml also allows you to provide the CSV structure as an Excel file.

Character set / encoding
Determines the text encoding of the CSV file. UTF-8 is the standard and supports international characters. If you encounter problems with umlauts or special characters, you can select other encodings such as ISO-8859-1 (Latin-1) or Windows-1252 (ANSI).

Field separator
Specifies which character separates the individual data fields (columns) from each other. Frequently used options are:

• Semicolon (;) - Standard in CSV files
• Tabulator - For tabulator-separated files (Excel)

Field encloser
Defines whether text fields are enclosed by special characters. Important for texts with line breaks or if the field separator itself appears in the text. If field delimiters appear in the text, they are automatically doubled (escaped).

Line break
Determines how line ends and breaks are marked in the file:

• CR (Carriage Return) - Classic Mac format
• LF (Line Feed) - Unix/Linux format
• CRLF - Windows format

Additional options
You can activate the additional options by checking the respective checkbox:

• Start CSV file with attribute row
  Activate this option if the first row should contain the column names. This makes it easier to interpret the data when importing into other systems.
• Append line break after last line
  Adds an additional line break at the end of the file.
  Some systems require this for correct processing.

These settings ensure that CSV files can be interpreted and imported correctly.

Additional file formats

Optionally, you can use export transformation code to provide other file formats for automatic export, such as JSON or XML.

Configure connections for export

In the Connections area, you can specify how and where the results of your query reports are automatically sent.

Email dispatch

The following settings are available for sending fully automated, personalized emails from your laboratory database with dynamic content and attachments based on your SQL report data:

Basic settings

Email address
Enter one or more email addresses (separated by commas).
The recipient address can be stored permanently or filled with a dynamic variable, e.g., VAR_AN. The variable is filled from the columns of the SQL report data—for this, the SQL report must contain a column “To,” then the variable _VAR_AN_ is automatically replaced by the content of the column.

Of course, CC/BCC are also available.

• CC (Carbon Copy): Visible copy recipients
• BCC (Blind Carbon Copy): Hidden copy recipients

Subject and text
The subject and text of the email can also optionally contain variables from the SQL report, which are automatically replaced by values from the corresponding columns of the SQL report. Variables begin with _VAR_, use uppercase letters and underscores instead of spaces.

Sending options
In the drop-down menu below the text field for the email text, you can specify whether one e-mail is sent per SQL report or one e-mail per SQL report line.

• The content of the first row is used for the email variables:
  In this case, one email is always sent for each SQL report .
• The content is used row by row for up to 10 emails:
  With this selection, each line of the SQL report generates a separate email with the respective variable values.

Add attachments
You have several options for attaching data. Activate the appropriate option by checking the respective checkbox.

• Add the link to this export interface as attachment to the e-mail text.:
  Inserts a link to the online view of the original SQL report into the email text, which is useful if the email is sent to users of the laboratory database.

• Attach the result of this export as a file to the e-mail.:
  Attaches the complete SQL report result as a file to the email. If necessary, the file name can be inserted dynamically into the email text using the variable _FILENAME_. The file settings should be defined in advance in the ‘File format’ section.

• Attach file (uploads or PDFs) line by line to each email.:
  Attaches files (uploads or PDFs) line by line to each email.
  Requires a column with upload_id_for_download_link==123 for signed reports or quotes.
  Alternatively, a column with one of the following options to attach PDFs:
  pdf_datatype==invoice==x - Invoices (with invoices.id)
  pdf_datatype==offer==x - Offers (with offer.id)
  pdf_datatype==report==x - Reports (with reports.id)
  pdf_datatype==order_worksheets==x==y - Worksheets (with orders.id and designs.id)
  pdf_datatype==sample_worksheets==x==y - Sample worksheets (with samples.id and designs.id)
  pdf_datatype==onlineorder==x - Online orders (with onlineorders.id)
  pdf_datatype==problem==x - Problem reports (with problems.id)

In addition, it is possible to send generated files (e.g., .ics/.xls) line by line.
This requires three columns in the SQL report:

• File name ... e.g., 'abc.xls'
• Content type ... e.g. ‘xls’
• File content ... e.g.
  '160001 Test sample 1442268000
  160002 ABC 1442872800
  160003 ABC 1442872800'

Send email

The emails will then be sent according to your configuration as soon as you click the gray “Send file by email” button above the SQL report.

Display email preview

Optionally, you can preview your email in advance.
To do this, go to “Email preview” below the email send button and click on the drop-down menu.
A preview window will open with the following information (with the resolved variables, if applicable):

• Recipient addresses
• Subject
• Email text

Track sent emails

All emails sent by the Labordatenbank can be found under: Navigation bar -> Customers -> Outgoing messages

SFTP/FTP export

You can save the results of a SQL report on an SFTP server.

Activate SFTP account

• Activate the SFTP account in your SQL report by clicking on “Activate FTP/SFTP account.”
• Select the SFTP option from the “Type” drop-down menu.
• Enter the access data (server address, port, user name, password, directory path) for the server.

Configure PDF file export

Once your account has been activated, you can choose whether a PDF file should be stored on the server.

Select the “Generate and upload PDF file line by line” checkbox.
Below the checkbox, the syntax used in the SQL report is described in order to make the respective data types available for export.
If this checkbox is not activated, the export will be carried out as defined in the “File format” section.

Set up automatic trigger

In the Enterprise Cloud version of the laboratory database, you also have the option of scheduling the export to run at a specific time or defining an automatic trigger for the SFTP export, for example, the signing of a test report.

Test connection

Click on “Test SFTP/FTP connection” to check the connection to the server.

Export file

The file is always transferred when you click the gray “Send file to SFTP server” button above the SQL report.

HTTPS export

With the HTTP PUSH function, you can send the result of an SQL report as a JSON data record to a web service of your choice.

Enable HTTPS export

Enter the URL of the target web service
The export of files via HTTP PUSH can be activated by entering the target address, including username/password, in the field provided.

• URL format: https://username:password@api.customer.com
• Always use https:// for secure connections.
• If necessary, an entry from the first row of the SQL table can be inserted into the URL using a variable.
  Notation: _VAR_COLUMN_NAME_
  e.g.https://api.customer.com/results/submit_results?test_id= _VARProbeNO_
• Authentication is performed via username:password in the URL.
• The SQL report is sent as a JSON data record. By default, a flat-formatted JSON is sent. The format can be customized using an export transformation code, if stored.

HTTPS header
Additional headers can be specified in the “HTTP header” field.

HTTPS method
The default method for HTTP export is POST, but you can also use the PUT method.

Webservice Response Handler
A response from the HTTPS endpoint can be processed using Webservice Response Handler and JavaScript code.

Registration with P12 certificate (optional)
For registration at the HTTPS endpoint, we offer you the option of storing a P12 certificate in your SQL report.

Export data record

To send the data record, click on the gray “Send dataset” button in your SQL report.

HTTPS retrieval

HTTP PULL enables external systems to retrieve SQL report data on request. Your appropriately configured SQL report provides a web service endpoint that other systems can query.

Enabling HTTPS retrieval

To make files available via HTTPS PULL, select the “Enable HTTP authentication” button and define a user name and password in the following window.
Optionally, you can set an IP address restriction in this step.

Obtain URL for file retrieval
If data transfer via HTTPS pull is enabled, the links that external systems can use to retrieve the SQL report are displayed.
This SQL report data can now be retrieved as a CSV file or JSON file via the links displayed after entering the user name and password, e.g.:
URL for CSV retrieval: https://labordatenbank.com/demo/stats/view/327/https
URL for JSON retrieval: https://labordatenbank.com/demo/stats/view/327/https_json
Forward the URL to the external system that is to access your SQL report.

The retrieval URL with key can be generated using the SQL report and links to an SQL report view that is also visible to users who are not logged in.