The Kepler SQL Query Fields plugin supports three types of dynamic parameters to enhance your SQL queries. You can use them to make your queries more flexible and powerful.

Use Cases for Dynamic Parameters

When working with the Kepler SQL Query Fields plugin, it's crucial to understand how to best leverage the three dynamic parameters: {projectId}, {components}, and {customfield_xxxxx}. These parameters are designed to simplify the customization of your SQL queries based on your specific needs.

{projectId}: Use this parameter when you want to incorporate a project's identifier into an SQL query. Instead of creating separate queries for each project, use {projectId} to make your query applicable to all your projects. This way, your query will automatically adapt to each project, providing a more efficient solution.
{components}: When you need to include a task's component in your SQL query, the {components} parameter is the solution. It allows your query to adjust based on the currently selected component. If the issue's component changes, your query will adjust accordingly. Please note that this parameter supports only one component at a time.
{customfield_xxxxx}: The {customfield_xxxxx} parameter is the most flexible tool at your disposal. It enables you to use a variable in your query, displaying a result for each value of this parameter. If the value of {customfield_xxxxx} changes, your query will automatically update. This provides advanced customization to address specific needs.

By judiciously combining these dynamic parameters, you can optimize your SQL queries, resulting in more precise outcomes and enhancing your overall experience with the Kepler SQL Query Fields plugin.

Using a Project Identifier

One of the available dynamic parameters is the use of a project identifier. You can incorporate this identifier into your SQL queries using the {projectId} parameter. Here's an example:

projectId request

SELECT carName FROM cars WHERE idCar = '{projectId}'

In this query, {projectId} will be replaced by the project identifier at runtime.

The {projectId} can be exceptionally used in create screen.

Using a Dynamic Parameters Values from an issue

As for using {projectId} parameter in your SQL queries, you can use other parameters related to the issue. This is explained in the following table.

In this query, this parameters will be replaced by their values at runtime according to the linked issue.

To use it, insere the keyword between '{}'.

Keyword	Type	Jira Description Parameter
currentUser	String	Get the logged user name according to the account of Jira.
displayName	String	Get the logged user name according to the account of Jira.
id	Long	The unique ID of the issue.
key	String	The issue key.
statusId	String
issueTypeId	String	The ID of the IssueType for this Issue.
parentId	Long
projectId	Long	Gets the ID of the Project for this Issue. See section "Using a Project Identifier".
description	String
summary	String
environment	String
watches	Long
votes	Long
originalEstimate	Long	This is the "original estimate" of work to be performed on this issue, in milliseconds.
estimate	Long	This is the "remaining estimate" of work left to be performed on this issue, in milliseconds. A better name would be getRemainingEstimate but for historical reasons it is called what it is called.
timeSpent	Long	This is the "total time spent" working on this issue, in milliseconds.
created	Timestamp
updated	Timestamp
dueDate	Timestamp
resolutionDate	Timestamp	Returns the datetime that an issue was resolved on. Will be null if it hasn't been resolved yet, or if an issue has been returned to the 'unresolved' state.
archivedDate	Timestamp	Returns the timestamp when this issue was archived, or null if it wasn't archived.
creatorId	String	A user key of the creator.
reporterId	String	A user key of the reporter.
assigneeId	String	A user key of the assignee.
archivedById	String	Returns the userid of user who archived this issue, or null if it wasn't archived.
number	Long
resolutionId	String
securityLevelId	Long	Returns the Security Level for this Issue.
.....

See official Atlassian Issue Documentation to more details about description of keyword.

The dynamic parameters values can be used only after creation.
But, the {projectId}, {currentUser} and {components} can be exceptionally used in create screen.

Compatible with Jira 9.x.x

These Keywords are only operational and compatible from version of Jira 9.x.x

Using Jira Components

You can also leverage Jira components in your SQL queries using the {components} parameter. However, it's important to note that this works only when you have a single component. Here's an example:

components request

SELECT carBrand FROM cars WHERE carName = '{components}'

In this query, {components} will be replaced by the selected Jira component.

If you don't know how to create a component, follow these steps.

The {components} can be exceptionally used in create screen.

Working with Custom Fields

Using custom fields offers you maximum flexibility. You can create custom fields in Jira, retrieve their identifiers, and directly integrate them into your SQL queries. Here's an example:

customfield request

SELECT carBrand FROM cars WHERE carName = '{customfield_10320}'

In this query, {customfield_10320} will be replaced by the value of the associated custom field.

customfield_xxxxx must contain a primitive or atomic value.

Conclusion

In conclusion, the dynamic parameters offered by the Kepler SQL Query Fields plugin are a valuable asset for customizing your SQL queries in Jira. These parameters allow you to make your queries more flexible and tailored to your specific needs. Here's a summary of the key advantages of each parameter type:

Using a Project Identifier ({projectId}): You can easily incorporate the project identifier into your queries, simplifying data customization based on the current project.
Utilizing Jira Components ({components}): Components are important elements of your workflow. By using this parameter, you can adjust your queries to reflect the current component value, facilitating data management.
Working with Custom Fields ({customfield_xxxxx}): Custom fields add a layer of flexibility to your queries. You can directly incorporate them into your SQL queries to interact with specific data.

By combining these dynamic parameters, you can optimize your SQL queries and achieve more precise results, enhancing your overall experience with the Kepler SQL Query Fields plugin.

Navigateur non pris en charge