Configuring SQL Analytics

1 Queries Tab
2 New Query
3 Update Existing Queries
4 Configuring Parameters
5 How to Use Parameters
6 Out of the box Built In Parameter Queries
7 Existing Queries
8 Action Options
9 Pinning Queries

If a tenant isn’t set up to use the new analytics report builder, the old interface and options still show.

1- Queries can be pinned to the top of analytics.

2- The New query tab allows for creating a new query ad-hoc or saved. Existing queries saved can be viewed on the Existing Queries tab.

3- In SQL analytics you can pick tables and columns from the schema if you need to use them or view what is available.

4- Type your SQL-based query here.

5- Actions to run on the query results are shown in this bar.

Queries Tab

Users can directly create or edit existing SQL queries by clicking the New Query or Existing Queries buttons, respectively, under the Queries tab.

Note: The following columns are required when querying data from the document, folder/workspace tables for New queries as well as Existing queries. These columns are required to perform Query Menu Options on a queried result.

Column Names:'extId', 'extContainerId', 'number', 'version', 'folderType'.

Also, update your existing saved queries with these required fields.

New Query

Queries can be added to Analytics from the New Query option. The Litera database configured for use with CAM is listed in the left-hand side pane with the following database button -

The tables available within the Litera database are listed below. Click the arrow next to the table to view the list of columns available within each table. You can further click on the column names to view the column types.

Note: A Data Sync task must be configured and run to populate the database with records.

Click on the table name, to view the columns under each table.

Tip: Double-click on the table name to auto-populate a SELECT statement query in the Query field. Click Execute to view the query results.

In the Queries tab, click New Query
Define the query. The query must meet the following conditions:
- The query must include a SELECT statement.
- The Parameters in the query cannot be declared - they must be configured in the Parameter Configuration dialog.
- You can only query the database given in the left-hand side panel.

Click Execute to run the query and view the results on the same screen.
Click Save to save and add the query to the Existing Queries drop-down.
Autosaving the query is now possible in CAM as you type it. Users can navigate to another tab if they need to look something up, and return. Note: This only keeps the latest query version of when you typed in something to the window or changed it. Prior edits aren’t saved automatically unless you manually click Save to the Existing queries list. Enable the setting in Admin to enable this.
In the New Query window, type information in the provided fields, based on the table below, then click Save.

Field	Description

Field	Description
Name	Enter a name for the query. This name will be displayed in the drop-down in the existing queries list.
Description	Enter a description for the query
Query	Contains the body of the query
Query Results Type	This is auto-filtered and displays the query results type.

Update Existing Queries

Configure and run the existing query script for a newly created tenant to view the existing queries. You may download the following script file:

Sample existing query script

Configuring Parameters

To configure parameters, click Save after you enter a query in the query field. In the New Query window, click the Parameters button, type information in the provided fields, based on the table below, then click Save.

Field	Description

Field	Description
Parameter Name	The name of the parameter as used in the query
Description	A descriptive label for the parameter that will be displayed in the Existing Queries tab when the query is selected from the drop-down.
Control Type	Specifies the parameter type to be displayed. Options available are : Textbox - A text input field into which users can enter any text. Datefield - A text input field that allows the user to select the date. Boolean - A Boolean value (True or False) that specifies whether to display the field in the form. Multiselect - A multi-text drop-down selection field to assign document access.
Default Value	An optional default value for the parameter.

Click the Execute button to run the query.

How to Use Parameters

Parameters are supported in Analytics queries to pass specific metadata to the query. Then a lookup appears to select the data behind the parameter.

To set a parameter:

Add the parameter in the query with the following format: ’${Matter}

A sample query using parameters is below:

Select number, version, Name, author, classCode 
                from document 
                where clientCode='${Client}' and matterCode='${Matter}'

Then if your parameter returns more than one result, use the IN clause with the parameter.

Out of the box Built In Parameter Queries

There are two parameter queries that are built into Analytics queries and can be used to pull users or usernames.

They are:

#username
#userid

The username query is as such:

select extId,number as number,version as version,extContainerId as extContainerId from (SELECT doc.extId, us.username,us.userid, doc.createdbyuser as createdbyuser, doc.number AS Number, doc.version AS Version, doc.description AS Description, doc.clientCode AS ClientCode, doc.matterCode AS MatterCode, doc.authorId AS AuthorId, doc.author AS Author, doc.operatorId AS OperatorId, doc.createdDate as CreatedDate, doc.camEditedDate AS LastModifiedDate, doc.extContainerId, ex.system AS System, ex.systemInstance FROM document doc JOIN external_system ex ON doc.extSystemId = ex.id JOIN user us on us.userid=doc.createdbyuser where doc.extcontainername = 'euactive' and us.username='${us.username}') L limit 5

The userid query is:

select extId,number as number,version as version,extContainerId as extContainerId from (SELECT doc.extId, us.username,us.userid, doc.createdbyuser as createdbyuser, doc.number AS Number, doc.version AS Version, doc.description AS Description, doc.clientCode AS ClientCode, doc.matterCode AS MatterCode, doc.authorId AS AuthorId, doc.author AS Author, doc.operatorId AS OperatorId, doc.createdDate as CreatedDate, doc.camEditedDate AS LastModifiedDate, doc.extContainerId, ex.system AS System, ex.systemInstance FROM document doc JOIN external_system ex ON doc.extSystemId = ex.id JOIN user us on us.userid=doc.createdbyuser where doc.extcontainername = 'euactive' and us.email='${us.email}') L limit 2

Existing Queries

The Existing Queries tab allows you to edit or execute the saved queries. All the queries created and saved in the New Query tab are available here for selection. Several out-of-the-box queries are also available to run reports. See out-of-the-box queries for more information.

In the Queries tab, click Existing Queries
All the saved queries are listed in the drop-down. Select the query to execute.
The selected query name is displayed.

Click the button to view options for the query -

Click Show to view the query details. You cannot edit the query here.
Click Edit to edit the query. In the New Query window, make the necessary changes and click Save.
Click Delete to delete the query.

Click the Execute button to run the query.
The query results are displayed in the Query Results panel. Click on any of the items in the panel to view all the column details.