Introduction

If you have a textual data, which is stored in a different database outside CXO, and you want to use it in a CXO report, you can query it with SQL and then use the SQL result set in the CXO 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 textual data for custom descriptions. This connection string is specified in the 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 entering the ODBC Connection String 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 Descriptions with SQL query:

A stand-alone SQL query inserted into Custom Description field

To create these custom descriptions you need to use a standard SQL SELECT command, which selects the appropriate text, in the Custom Description field.

This example query selects Specification (i.e. description) for Europe entity from a particular SQL table:

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

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 Label Expressions by adding them both into the SQL command in front of the line and inside the SQL query itself:

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

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

CONTENT