Add Query (Parameterized Queries)
  • 20 Jun 2024
  • 4 Minutes to read
  • Dark
    Light

Add Query (Parameterized Queries)

  • Dark
    Light

Article summary

Feature Details
Introduced in Version--
Modified in Version8.11

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 that become 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] as 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 database. 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

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 NameDescription
    NameSet the name to the query data source
    Return DataAllows you to choose whether or not to generate output for the query.
    Output Count
    Query TimeoutSet the timeout for the query in seconds
    QueryQuery to execute
    Input ParametersUser-defined variables that are used in a query to filter dynamically or process data
    Output SettingsSets 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, this will create a data source in the Designer folder with the given name.

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 0 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 the name of the newly created parameterized query to the Report. Add appropriate Data Fields.
  2. Click on the Data Source, and navigate to the Properties Panel on the right. Under the Parameters tab, all the parameters will get exposed. Click on the pencil icon to edit the parameter.

  1. From the following table, configure the parameters.
    Setting NameDescription
    ConstantThis will be a default and will allow users to enter the value like in the current behavior.
    Current User - Account IdIt provides a way to specify a filter for the Report based on the current user account id.
    Current User - Email AddressIt provides a way to specify a filter for the Report based on the current user's Email Address.
    Current User - IdentifierIt provides a way to specify a filter for the Report based on the current user identifier.
    Current Folder - Folder IdIt provides a way to specify a filter for the Report based on the current folder id.
    Current Folder - Folder NameIt provides a way to specify a filter for the Report based on the current folder name.
  2. To let users filter the Report during runtime, select the Runtime Editable option from the drop-down menu under Settings.

Feature Changes

DescriptionVersionDateDeveloper Task
Ability for Integration-based Data Sources to use SQL-Based Filters Natively
8.11April 14, 2023
DT-037112

Was this article helpful?