Add Query (Parameterized Queries)

Overview

Add Query is a type of database integration that allows users to build a data source based on SQL queries. Users can use complex queries to create a data source that can be used in Flow steps or to generate Reports. In these types of queries, instead of embedding user input directly into the SQL statement, parameters can be defined as variables when the query is run in the workflow process. Parameterized queries can then be used as steps in a Flow. These steps can be found under Integration > Database > [DatabaseName], which is one of the many query steps for the category.

Use Case

Such queries can be used to create reports by using JOIN statements to combine data from multiple tables, and it is possible to create comprehensive reports that include information from various databases. Parameterization allows users to filter the Report based on specific criteria, such as date ranges, user IDs, Folder IDs, or other factors, providing a more customized view of the data. This approach can be helpful in many contexts, including data analysis, financial reporting, customer relationship management, and project management.

This article explains the following topics.

1. Adding a Query Data Source
2. Example of a Parameterized Query
3. Generating Reports using Parameterized Query

Parameter Symbols

Different databases require specific parameters based on their types. Please refer to the table below for more details:

Adding a Query Data Source

Following are the steps to create a Parameterized query data source.

1. In the Designer Project, click on CREATE DATATYPES/INTEGRATIONS on the Global Action Bar and select Database Integration. Click Add Query.
2. Select the method of connecting to the database, which can either be an existing connection or a new integration. Click Next.
3. Configure the Database Integration window referring to the table below.

4. Property Name	Description
   Name	Set the name to the query data source
   Return Data	Allows you to choose whether or not to generate output for the query.
   Output Count
   Query Timeout	Set the timeout for the query in seconds
   Query	Query to execute
   Input Parameters	User-defined variables that are used in a query to filter dynamically or process data
   Output Settings	Sets a default output type to the query data source
   Create Type	This option is used when you want to define a custom data type to represent the query results.
   Select Rows	This option is used when you want to define a custom data type to represent the query results.
   Data Rows	This option is used when you want to return the entire set of query results as a single table.
   CSV Data Rows	This option is similar to "Data Rows," but the results are returned in comma-separated values (CSV) format.

5. Once saved, a data source with the given name will be created in the Designer folder.

Example

This example demonstrates creating and using a Parameterized Query to get a list of Assignments assigned to an account "r@decisions.com".

1. In the Designer Project, click on CREATE DATATYPES/INTEGRATIONS on the Global Action Bar and select Database Integration. Click Add Query.
2. Select an existing connection in the Database Integration dialog window and click NEXT.
3. Under the QUERY SETTINGS catalog, provide a name for the query (Assignemnt_Query). In the Query text area, enter select * from entity_assignment where all_assignments = @email_id. Under the INPUT PARAMETERS category, you will notice @email_id will populate.
4. Click the pencil icon to open the Edit Parameters dialog window. In this window, users can customize various settings such as the parameter's Datatype, Maximum Size, List option, and Default Value. 
5. As we are running the query to get all assignments for a specific account, i.e., r@decisions.com, we will pass the same under Default Value.
   
6. Click RUN QUERY to view the results. In this example, there are 2 assignments for the account "r@decisions.com".
7. Click OK to save the query.

Generating Reports using Parameterized Queries

We will use the Data source from the above Example to generate a Report. For more information, refer to Create Reports.

1. Add the Data Source by searching for the name of the newly created parameterized query. Add appropriate Data Fileds.
2. Click on the Data Source and navigate to the Properties Panel on the right. Under the Parameters tab, all the parameters will be exposed. Click on the pencil icon to edit the parameter.
3. From the following table, configure the parameters.

   Setting Name	Description
   Constant	This will be a default and will allow users to enter the value like in the current behavior.
   Current User - Account Id	It provides a way to specify a filter for the Report based on the current user account ID.
   Current User - Email Address	It provides a way to specify a filter for the Report based on the current user's Email Address.
   Current User - Identifier	It provides a way to specify a filter for the Report based on the current user identifier.
   Current Folder - Folder Id	It provides a way to specify a filter for the Report based on the current folder ID.
   Current Folder - Folder Name	It provides a way to specify a filter for the Report based on the current folder name.

4. To let users filter the Report during runtime, select the Runtime Editable option from the drop-down menu under Settings.

Feature Changes