1 of 1 people found this helpful
There are two places in Xtraction where you can use SQL:
1. Report Designer in Xtraction (front-end)
2. In the data model editor, depending on your license
In the Report Designer you can simply create table-based reports by writing SQL just as you would in any query builder:
In the data model editor you will need to create the table from your query in a new or existing view and any fields you want to be available in the front end. The benefit of using the data model editor is that you will be able to use your data in Xtraction to create dashboards and documents. For more information I recommend reviewing the Data Model Editor Guide.
That said, you can do quite a lot within the Xtraction interface itself. Your original query can be created as a standard filter, no SQL required. Generally speaking, when we edit the data model, it's because the data we want isn't available or we need to perform specific tasks before it can be used in a report, such as calculations, conversions or sub-queries.
Excellent, thanks very much for this. I've managed to get my query working in the report designer. Some rtfm on my part .
Can you give me an example of how to run the query at the data model layer?
Part of this is in order to drive scorecards.
2 of 2 people found this helpful
First, the standard disclaimer about editing the data model, back it up, it's what makes Xtraction work with your data source. I highly recommend reviewing the Data Model Editor manual or even opening a support case for assistance. You can very easily cause issues in your Xtraction environment by editing the data model incorrectly.To create your query in the data model you would need to create a table in a new or existing view, the expression for that table would be your query. You would then create any necessary fields and make them available in Xtraction.
This is a table from our data model that contains the number of Group transfers recorded on a ticket:
Instead of pointing to an actual table in the data source, the table field is populated with the query. The fields below are used to join to the ticket table and display the Group Transfers field in the Xtraction interface. When Group Transfers is used in a dashboard or document in Xtraction this query is run, the table is created and the results are displayed: