Overview
Quantive Results allows you to build insights using SQL queries and then display them using HTML markup. They can be edited in the online editor or in Visual Studio Code using the Offline Insight Editor.
The Quantive Results data warehouse is based on PosgreSQL. Any functions that are supported out-of-the-box by PosgreSQL, are also supported by Quantive Results insight engine.
For more information, see PosgreSQL.
The insight widgets are based on AngularJS. When the insight server responds with the calculated data, the widget binds the response to the user interface.
For more information, see AngularJS.
Create the insight
To create an SQL insight, perform the following:
In the navigation pane click Insightboards.
In the toolbar, click Manage insights.
Expand Add new insight and select Create Insight.
Enter a Title for the insight.
In Description, describe what the insight will do.
Select the Use SQL expert editor option.
Click Create. The SQL editor appears.
The SQL editor
The SQL editor has the following:
On the left is an HTML markup editor.
In the bottom of the HTML editor, you can use predefined HTML templates or insert HTML snippets.
Using the Automatic UI button, you can display the markup editor or the output that it produces.On the right is an SQL editor.
You can use the Execute button to check for errors in your SQL syntax.In the right pane, there is a reference of all connected Quantive Results data source entries that you can copy in the editors.
When finished with the insight, click Save and leave.
For more information, see:
Add the insight to an insightboard
Perform the following:
In the navigation pane click Insightboards.
Click the insightboard where you want to add the created insight.
In the upper-right corner, click Add to expand the menu.
Click Insight.
Select the insight or Insights you would like to add and click the Add Insight button.
In the toolbar, click Save changes.
Example
Use the following example to understand better the parts of an SQL insight.
Insight algorithm: For the algorithm the example uses a simple SELECT statement that selects a text from the server and returns it as a named column.
Insight user interface: The example uses the following HTML markup for our SQL insight:
<div class='title'>{{ data.message }}</div>SQL: The example uses the following SQL statement:
SELECT 'Hello, World!' as message
Result: Executing the insight produces the following result:
Key concepts
Each Quantive Results insight has its own Angular scope. The results of the SQL query of the insight are transformed into a JavaScript object and added to the insight scope in the data property.
The first row returned by the SQL will be placed as object properties in the root of the data property in the Angular scope for the insight. As of August 2022, the default property under the data property will contain an array of objects with one object for each row returned by the SQL. If no rows are returned by the SQL, the data property will contain an object with a default property with an empty array. No other properties will be populated.
Examples
The following SQL query returns one row of data:
SELECT COUNT(1) AS count
FROM users
Return the data as follows:
{
count: 1604,
default: [
{count:1604}
]
}
The following SQL query returns multiple rows of data:
SELECT
COUNT(1) AS count,
issuetypename AS type
FROM jira_issues
GROUP BY issuetypename
Will return data in a format like this (the root of the object has the first row of data):
{
count: 179,
default: [
{count:179, type:"Bug"},
{count:399, type:"Story"},
{count:50, type:"Task"},
{count:230, type:"Sub-task"},
{count:49, type:"Epic"}
],
type: "Bug"
}
In the event that your query returns no rows, the response will look like this:
{
default: []
}
NOTE: When building insights, you can retrieve up to 5000 records at a time from the Quantive Results data warehouse. Retrieving more than 5000 records will result in an error. You can add a LIMIT 5000 to your query or use insightboard parameter filters to avoid this error.