Dynamic Datasets and Customizable Parameters for Data Analysis

flowtrail logo

10 Jun-2024

In our previous blog, we unveiled the magic of Flowtrail AI’s Text-to-SQL functionality, which empowers users to craft complex queries using natural language. This innovation eliminates the need for deep SQL expertise, making data analysis more accessible than ever. Today, we’re excited to delve into Flowtrail AI’s advanced capabilities—specifically, its dynamic datasets and customizable dataset parameters. These features offer unparalleled flexibility and control, allowing users to generate highly customizable datasets and elevate their data exploration experience.

Understanding Flowtrail AI Dataset Parameters

Dataset parameters in Flowtrail AI function like adjustable settings for your data. Available in various formats such as text, numbers, dates, and even custom SQL queries, these parameters can be customized with unique names, default values, and specifications for whether they are mandatory or optional. This flexibility enables the creation of tailored queries without the need for complex coding.

add-parameters.png

Types of Dataset Parameters

  1. String: Tailor your queries with text-based inputs.
  2. Number: Inject numerical values for precise control.
  3. Date: Specify dates for time-bound analyses.
  4. Select: Choose from predefined options for streamlined selection.
  5. Multi-Select: Pick multiple values for comprehensive filtering.
  6. SQL: Craft custom SQL queries within parameters for advanced control.

Each parameter type has its unique characteristics:

  • Variable Name: A user-defined name for the parameter, ensuring clarity.
  • Default Value: A pre-set value for the parameter if left unspecified.
  • Required vs. Optional: Define whether the parameter is essential for the query.

params-types.png

Crafting Your Dataset

Let’s illustrate how to create a dynamic dataset using Flowtrail AI’s features. Suppose we want to retrieve salary process details by year. Here’s a SQL query generated by the Text-to-SQL feature for the year 2024:

SELECT id, name, startDate, endDate, paymentDate, totalAmount
FROM employee_payroll
WHERE YEAR(startDate) = 2024

To make the year dynamic, we can use a parameter. We will add a Select parameter named selectedYear.

crafting-your-dataset.png

Adding a Parameter to the Dataset

  1. Name the Variable: In the form, name the variable selectedYear.
  2. Choose Parameter Type: Select the Select option from the parameters list.
  3. Enter Values: Enter your select values, separating them with commas. For a default value like the current year, select it from the Default value column.
  4. Set Required Status: Since selectedYear is necessary to retrieve data based on the ‘where’ condition, check the ‘required’ checkbox.

param-mandatory.png

Integrating the Parameter into the Dataset

Replace the year 2024 with our variable name selectedYear in the following format: '{{selectedYear}}'. The modified SQL query becomes:

SELECT id, name, startDate, endDate, paymentDate, totalAmount
FROM employee_payroll
WHERE YEAR(startDate) = '{{selectedYear}}'

This change makes the dataset more dynamic and flexible, allowing users to retrieve specific data based on their chosen year. This is just one example of how Flowtrail AI’s customizable dataset parameters can enhance your data exploration experience.

custom-prams.png

Parameters with Optional Values

In some instances, parameters will be optional. If a parameter value is present, the query will execute based on that parameter; if not, it will execute without it, offering greater flexibility in analytics.

Consider this query to get employee salary details:

SELECT e.id, e.firstName, e.lastName, es.totalSalary
FROM employee e
JOIN employee_salary es ON e.id = es.employeeId

To filter employees by their name, we will add a text-based parameter called employeeName. If an employee name is provided, the query will display that employee’s salary; if no name is given, it will display the salary details of all employees.

Implementing Optional Parameters

SELECT e.id, e.firstName, e.lastName, es.totalSalary
FROM employee e
JOIN employee_salary es ON e.id = es.employeeId
{`% if employeeName %}
    WHERE e.firstName LIKE '%{{employeeName}}%'
{% endif %`}

This SQL query uses an ‘if’ statement to check whether an employeeName is provided. If it is, the query adds a ‘WHERE’ clause to filter by the specified name. If not, it retrieves all salary details.

statement-in-dataset.png

Conclusion

Flowtrail AI’s dataset parameters open a world of possibilities for crafting highly customized and dynamic data explorations. With a variety of parameter types, unique names, and control over required vs. optional fields, you can tailor your queries to perfectly suit your analytical needs.

Stay tuned for our next blog post, where we’ll delve deeper into creating reports using these dynamic datasets. Happy exploring with Flowtrail AI!

logo

With Flowtrail AI, you can seamlessly create, analyze, and scale your reports and graphs, empowering your data-driven decisions.

© 2024 RadixHR