Query Builder

The Query Builder is a graphical tool within the Analytics Dashboard and VisualVault Report Designer that allows you to build a Query for your selected data source and preview the results. With the friendly user interface, you can quickly select the data you need to build your custom reports without prior knowledge of programming in SQL.

The Query Builder is used to select and join tables used on reports.

Once you have run the query builder, you will see a preview of the SQL string that was generated by the query builder. This is an editable field, for users who would like to make manual changes to the SQL string.

The SQL string generated is shown after running the query builder. 

Access the Query Builder

The Query Builder is available both in the Analytics Dashboard and the VisualVault Report Designer. This section specifies how to access the query builder in different contexts. Jump ahead to the next section for information on how to run the query builder once it has been accessed.

Analytics Dashboard

The query builder is available in several locations within the Analytics Dashboard. You can use it to select your initial data, make edits to existing data sources, or add data sources on a report that has data sources already selected.

Add the Initial Data Selection

1. Navigate to the Analytics Dashboard.
   New Analytics Dashboard
2. Select the data source.
   1. Select Create in the center of the page, or
   2. Open the Dashboard Menu by selecting the hamburger menu (☰) in the upper left-hand corner and then select the New... button.
      Create a new dashboard
3. Input the Dashboard Name and then select Create Data Source...
   Create Data Source

    

4. Select the data source type and data connection:
   1. For Form data (most common data source), select Database > VisualVault Forms 
      
   2. For Views (least common data source), select Database > VisualVaultSelecting form data is the most common data source type
      
5. To launch the Query Builder, select Run Query Builder.
   Dashboard Data Source Wizard - Run Query Builder

    

Add a Query to an Existing Report

1. Open the Dashboard Menu by selecting the hamburger menu (☰) in the upper left-hand corner of the Analytics Report Designer.
2. Select Data Sources.
3. Select Add Query to open the Query Builder.
   Steps 1-3

    

Edit an Existing Query

1. Open the Dashboard Menu by selecting the hamburger menu (☰).
2. Select Data Sources.
3. Select the edit icon (pencil icon) next to the query name to open the Query Builder.
   Steps 1-3

    

VisualVault Report Designer

Similar to the Analytics Dashboard, the query builder is available in a number of locations within the VisualVault Report Designer. You can use it to select your initial data or use it to add data sources on a report that already has one source selected previously.

Initial Data Selection

1. When creating a new report, open the Menu by selecting the hamburger menu (☰).
   Menu on the report designer

    

2. Select Design in Report Wizard, to build a report with predetermined structures and aesthetics; or select Add Data Source, to build a report from scratch.
   Options to add a data source 
3. Select Database as the data source type.
   Select the Data Source Type
4. In box 1, choose your data connection. 
   1. For Form data (most common data source), select VisualVault Forms
   2. For Views (least common data source), select VisualVault
5. In box 2, select the plus sign (+) next to Queries and select Run Query Builder. Choose a data connection in box 1 then run the Query Builder from box 2.
   Choose a data connection in box 1 then run the Query Builder from box 2.

    

Add a Query to an Existing Report

In addition to using the query builder to select the initial data set to be used on the report, you can add additional queries at any time. In the VisualVault Report Designer, the steps to add a new query are the same as the initial data selection.

Edit an Existing Query

1. Select the Field List on the right-hand menu.
2. Expand the SQL Data Source using the arrow to the left of the data source name.
3. Next to the query name, select the edit icon.
4. Select Run Query Builder to open the Query Builder and make changes to the query.
   Steps to edit an existing query in the Report Designer.

    

Run the Query Builder

When you first run the query builder, you will be brought to the following screen:

New Query Builder Screen

Select a Table or View

The first thing you will want to do when running the query builder is to select the table(s) or view(s) you would like to report on. This is done with a simple drag-and-drop motion.

Add a table or view to the query builder:

1. On the right-hand panel, underneath the Available Tables and Views tab, drag and drop the table or view you would like to report on.
2. Select the columns to be reported on.
   1. To use all columns in the table, check either the box next to the table name or select the *(All Columns) option.
      1. Checking the box next to the table name will manually populate all columns into the query. This means that any columns currently present will be added, but any fields added in the future will not appear on reports.
      2. Selecting the *(All Columns) option will use the asterisk (*) function in the query, so any columns that are added to the table will appear on future reports.
   2. To use only certain columns on your report, select only the needed columns using the check box next to each field name. This method is recommended for large datasets.

Preview Data

Once you have added a table or view to the query builder, you can select the Preview Results button in the bottom left-hand corner to see the first 100 rows of the data table created with the query builder. In the following image, only the DhDocID, First Name, Last Name and Hire Date columns were selected from the Employee Record table.

Preview Query Reports

 

Properties

The right-hand panel of the Query Builder can be used to edit various properties of the query and the tables and columns that make up the query. Many of the properties that can be modified at the Data Source Level (in the query builder) can also be modified at the Report Level. 

Query Properties

The Query Properties panel is available at all times in the query builder. In this panel, you can determine the name of the query as well as set different filtering and selection options used in the query.

Table Properties

Once you have added a data table to the query, you can select the table to open the Table Properties tab in the right-hand panel. The following options are available on the Table Properties panel:

Column Properties

Once you have added a data table to the query and selected columns to report on, you can adjust the properties of the column. The Column Properties tab becomes available in the right-hand panel of the query builder when a column is selected. The Column Properties enable you to shape the data in your query at the Data Source Level.

The following options are available in the Column Properties panel:

Available Tables and Views

This tab allows you to select the tables or views to be used in the query. You can use the search bar at the top of the tab to search for the name of a certain table.

Parameters

The parameters tab can be used to add parameters to the query.

Joining Tables

When multiple data sources are used in a report, you can use joins to combine records that you want to see, based on how the data tables are related to each other. Joins rely on a primary and foreign key that is a unique identifier to relate the tables. When using VisualVault form data, this is often the DhDocId, which is also known as the Form ID. Other unique identifiers such as an email, or username can also be used. 

Joining Tables in the Query Builder

To two or more tables in the query builder:

1. Drag and drop all tables needed onto the query builder.
2. Create the relationship between the tables based on the key columns. To do this, click and hold the column on one table and drag and drop it to the corresponding column on the other table.
3. Once the join relationship is established, you will see a line connecting the two columns.

Relationship Properties

By joining two or more tables, you create a relationship between the two tables. To further define the relationship between your joined tables, you can open the Relation Properties panel in the right-hand panel by clicking on the line joining the two columns in the query builder. From there, you can select the Join Type and the Operator used to define the relationship.

You can open the Relation Properties panel in the right-hand panel by clicking on the line joining the two columns in the query builder. 

The Join Type dropdown list determines the type of join that occurs between the two tables. The two join types available in the query builder are an inner join and a left outer join. The Operator dropdown list establishes the logical relationship between the two columns to determine which rows will be displayed on the resulting dataset. By default, Inner join is used for the Join Type and Equals to is used for the Operator. These may be changed; however, these default settings are recommended.