3 Replies Latest reply on Sep 13, 2016 2:51 PM by jfmascaro

    Is it possible use a SQL query in the modeler or front end to manipulate data returned?

    davidc Employee

      I want to have a SQL statement such as...

       

      SELECT * FROM COMPUTER WHERE DEVICENAME LIKE 'abc%' or DeviceName like 'def%'

       

      .. and have that data returned. I know I can apply filters at the front end or model but SQL queryies seem more flexible.

       

      Thanks.

       

      BRD

        • 1. Re: Is it possible use a SQL query in the modeler or front end to manipulate data returned?
          jfmascaro Apprentice

          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:

          XRD.jpg

           

          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.

          1 of 1 people found this helpful
          • 2. Re: Is it possible use a SQL query in the modeler or front end to manipulate data returned?
            davidc Employee

            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.

            • 3. Re: Is it possible use a SQL query in the modeler or front end to manipulate data returned?
              jfmascaro Apprentice

              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:

              GTT.jpg

              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:

              GTT2.jpg

              1 of 1 people found this helpful