Introduction

As of CXO-Cockpit release 4.3.4 you can use SQL queries as Custom Description of the report lists.

If you have a certain set of data, which is stored in a different database outside CXO-Cockpit, and you want to use it in CXO-Cockpit report, you can query it with SQL and then use this SQL result set in the CXO-Cockpit Custom Description field inside the report list.

How to enable SQL query usage as Custom Description

To be able to use SQL queries as Custom Descriptions you need to specify the connection string to the database, from which you want to use data for custom descriptions. This connection string is specified in Relational Database Connection section in System Settings within Design Studio:

If you press on "Help button", you can see a universal set of example connections strings, which can be used both with Oracle and SQL databases:

To encrypt the password we use "cxo-password" token and then encrypt the actual password in "Password" field:

After that you can press "Check" button and get the confirmation that you are successfully connected to the required database:

How to create Custom Descriptions with SQL query

You can use two types of Custom Description with SQL query:

A stand-alone SQL query inserted into Custom Description field

To create such custom description you need to use the standard SQL command, which includes a particular data select, in Custom Description field.

This example query selects specification (description) for Europe entity from a particular SQL table:

Here is how the specified Custom Description will appear in the repoort:

Here is how this SQL query is specified in SQL Server database:

Please note, that if the query returns several lines, then the first one will be picked for the Custom Description in CXO-Cockpit.

Combination of SQL query and Dynamic Label Expressions in Custom Description field

You can compliment your SQL query with the dynamic labels by adding them both into SQL command in front of the line and inside the SQL query itself: