Power BI Connector for Jira

Build 24.0.9062

Issues

Create, modify, delete and query Issues in Jira.

テーブル固有の情報

このテーブルをクエリするときにカスタムフィールドのカラムと値(Sprint、Epic など)を動的に取得するためには、IncludeCustomFields 接続プロパティを'True' に設定する必要があります。

SQL とJQL (Jira Query Language) を使用して、Issues を検索できます。

Select

JQL


SELECT * FROM Issues WHERE JQL = ' project = "New project" AND Status WAS "open" '

SQL


SELECT * FROM Issues WHERE ProjectId > 10001 OR Updated <= '2016/03/29 15:00'
本製品 はJira API を使用して一部のフィルタを処理します。本製品 はクライアント側で本製品 内で他のフィルタを処理します。SupportEnhancedSQL をfalse に設定することで、クエリのクライアント側の実行をオフにできます。その場合、その他のカラムを参照する検索条件は一致しないデータを生成します。 例えば、次のクエリはサーバー側で処理されます。

SQL およびJQL


SELECT * FROM Issues WHERE ProjectName = 'New project' AND JQL = ' status WAS "open" '

時間、日、週、分などを指定して、TimeSpent、OriginalEstimate、RemainingEstimate などの時間間隔のフィールドをフィルタリングします。

SELECT * FROM Issues WHERE RemainingEstimate = '1m 2d' AND TimeSpent = '3m 2w'

Insert

プロジェクトで新しい課題を作成します。挿入には、Project(ProjectId またはProjectKey を指定)、IssueType(IssueTypeId またはIssueTypeName を指定)、およびSummary が必要です。ProjectName を指定することもできますが、ProjectKey が属する値を見つけるためにサーバーへの追加リクエストが発生します。Project やIssueType によっては、さらに多くのフィールドが必要な場合があります。

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, CustomField1) VALUES (10000, 'Desc from prod', 10100, 'test')
INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, CustomField1) VALUES ('PROD', 'Desc from prod', 'Task', 'test')

Update

ID を入力して既存の課題の情報を更新します。

UPDATE Issues SET Summary = 'Desc FROM prod', CustomField1 = 'ValidValue' WHERE Id = '10003'

また、特定の課題の集計フィールドを変更することもできます。FixVersionsAggregate、AffectedVersionsAggregate、ComponentsAggregate、およびIssueLinksAggregate を変更できます。これらのカラムの特別な入力を使用して、課題の適切なフィールドを割り当て、削除、および更新できます。 プロバイダークエリで使用するには、Jira アプリケーションで先にオブジェクトを作成しておく必要があります。NOTE:IssuelinksAggregate フィールドは入力の要件がわずかに異なります。以下で概要を説明します。

修正バージョンを追加する構造:

{ "add": {"name":"example"} }

or

{ "add": {"id":"1234"} }
サンプルクエリ:
UPDATE Issues SET FixVersionsAggregate = '{"add":{"name":"extra"}}' WHERE Id = 19683

一度に追加できるオブジェクトは1つのみですが、set コマンドを使用すると、複数のオブジェクトを割り当てることができます。 重要:set コマンドは、以前のすべてのオブジェクトを上書きします。つまり、以前のエントリは破棄され、 set コマンドで指定されたオブジェクトのみが課題に保存されます。set コマンドの後ろには配列が必要です。

{ "set":  [ {"name":"New Component 1"}, {"name":"Old Component 2"} ] }

or 

{ "set":  [ {"id":"1234"}, {"name":"Old Component 2"} ] }

サンプルクエリは次のようになります。

UPDATE Issues SET ComponentsAggregate = '{ "set":  [ {"id":"1234"}, {"name":"Old Components 2"} ] }' WHERE Id = 19683

remove コマンドを使用して、エントリを一度に1つずつ削除することもできます。

{ "remove": {"name":"example"} }

or

{ "remove": {"id":"1234"} }

UPDATE Issues SET AffectedVersionsAggregate = '{"remove":{"name":"extra"}}' WHERE id = 19683

これらのコマンドは、新しい課題を挿入するときにも適用されます。

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"name":"Example1"}}')

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"set":[{"name":"Example1"}]}')

IssueLinksAggregate

IssueLinksAggregate では'add' 操作のみがサポートされており、リンクのタイプおよびリンクの方向('outwardIssue' か'inwardIssue' のいずれか1つ)を指定する必要があります。 使用可能なリンクタイプはBlocks、RootCause、Relates、Duplicate、およびCloners です。

{ "add":
  { "outwardIssue":
      {"key":"COB-15"},
    "type":
      {"name":"Cloners"}
  }
}

or

{ "add":
  { "inwardIssue":
    {"key":"FIL-78"},
    "type":
    {"name":"Blocks"}
  }
}

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"outwardIssue":{"key":"COB-15"},"type":{"name":"Cloners"}}}')

"UPDATE Issues SET IssuelinksAggregate = '{"add":{"inwardIssue":{"key":"FIL-1"},"type":{"name":"Blocks"}}}' WHERE Id = '22204'"

Delete

ID を指定して課題を削除します。完了していないサブタスクも削除されることに注意してください。

DELETE FROM Issues WHERE Id = '10003'

Columns

Name Type ReadOnly References SupportedOperators Platform Description
Id [KEY] Int True =,!=,>,>=,<,<=,IN,NOT IN Common

The Id of the issue.

Key String True =,!=,>,>=,<,<=,IN,NOT IN Common

The key of the issue.

IssueTypeId String False =,!=,IS,IS NOT,IN,NOT IN Common

The issue type Id.

IssueTypeName String True =,!=,IS,IS NOT,IN,NOT IN Common

The issue type name.

ProjectId String False =,!=,IS,IS NOT,IN,NOT IN Common

The project Id of the issue.

ProjectName String True =,!=,IS,IS NOT,IN,NOT IN Common

The project name of the issue.

ProjectKey String False =,!=,IS,IS NOT,IN,NOT IN Common

The project key of the issue.

ParentId Int False =,!=,IN,NOT IN Common

The Id of the issue's parent if the issue is a subtask.

ParentKey String True =,!=,IN,NOT IN Common

The key of the issue's parent if the issue is a subtask.

ParentIssueTypeId String True Common

The issue type id of the issue's parent.

ParentIssueTypeName String True Common

The issue type name of the issue's parent.

ParentStatusId String True Common

The status id of the issue's parent.

ParentStatusName String True Common

The status name of the issue's parent.

ResolutionId String True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The resolution Id of the issue.

ResolutionName String True =,!=,IS,IS NOT,IN,NOT IN Common

The resolution name of the issue.

ResolutionDescription String True Common

The resolution description of the issue.

ResolutionDate Datetime True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The resolution date of the issue.

Workratio Long True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The work ratio of the issue.

LastViewed Datetime True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The last time that the issue was viewed.

WatchCount Int True =,!=,>,>=,<,<=,IN,NOT IN Common

The number of watches of the issue.

IsWatching Bool True Common

Whether the currently authenticated user is watching the issue.

Created Datetime True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The creation date of the issue.

PriorityId String False =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The priority Id of the issue.

PriorityName String True =,!=,IS,IS NOT,IN,NOT IN Common

The priority name of the issue.

TimeSpentSeconds Long True Common

The time spent in seconds on the issue.

TimeSpent String True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The time spent on the issue.

RemainingEstimateSeconds Long True Common

The time estimate in seconds of the issue.

RemainingEstimate String False =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The time estimate of the issue.

OriginalEstimateSeconds Long True Common

The original time estimate in seconds of the issue.

OriginalEstimate String False =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The original time estimate of the issue.

AggregateTimeSpent Long True Common

The aggregate time spent of the issue.

AggregateTimeOriginalEstimate Long True Common

The original aggregate time estimate of the issue.

AggregateTimeEstimate Long True Common

The aggregate time estimate of the issue.

AssigneeDisplayName String True Common

Assignee display name.

AssigneeKey String True =,!=,IS,IS NOT,IN,NOT IN Common

[DEPRECATED] The assignee key of the issue.

AssigneeAccountId String False =,!=,IS,IS NOT,IN,NOT IN Common

The assignee account id.

AssigneeName String False =,!=,IS,IS NOT,IN,NOT IN Common

The assignee name of the issue.

AssigneeEmail String True =,!=,IS,IS NOT,IN,NOT IN Common

The assignee email of the issue.

Updated Datetime True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The updated date of the issue.

StatusId String True =,!=,IS,IS NOT,IN,NOT IN Common

The status Id of the issue.

StatusName String True =,!=,IS,IS NOT,IN,NOT IN Common

The status name of the issue.

StatusCategoryId String True =,!=,IS,IS NOT,IN,NOT IN Common

The status category id of the issue.

StatusCategoryKey String True =,!=,IS,IS NOT,IN,NOT IN Common

The status category key of the issue.

StatusCategoryName String True =,!=,IS,IS NOT,IN,NOT IN Common

The status category name of the issue.

Description String False IS,IS NOT Common

The description of the issue.

Summary String False IS,IS NOT Common

The summary of the issue.

CreatorDisplayName String True Common

Issue creator display name.

CreatorName String True =,!=,IS,IS NOT,IN,NOT IN Common

The creator name of the issue.

CreatorAccountId String True =,!=,IS,IS NOT,IN,NOT IN Common

The creator account id.

CreatorKey String True =,!=,IS,IS NOT,IN,NOT IN Common

[DEPRECATED] The creator key of the issue.

CreatorEmail String True =,!=,IS,IS NOT,IN,NOT IN Common

The creator email of the issue.

ReporterDisplayName String True Common

Issue reporter display name.

ReporterName String False =,!=,IS,IS NOT,IN,NOT IN Common

The reporter name of the issue.

ReporterKey String True =,!=,IS,IS NOT,IN,NOT IN Common

[DEPRECATED]The reporter key of the issue.

ReporterAccountId String False =,!=,IS,IS NOT,IN,NOT IN Common

The reporter account id.

ReporterEmail String True =,!=,IS,IS NOT,IN,NOT IN Common

The reporter email of the issue.

AggregateProgress Long True Common

The aggregate progress of the issue.

TotalProgress Long True Common

The aggregate total progress of the issue.

Votes Int True =,!=,>,>=,<,<=,IN,NOT IN Common

Votes of the issue.

HasVotes Bool True Common

The vote status of the issue.

DueDate Date False =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The due date of the issue.

Labels String False =,!=,IS,IS NOT,IN,NOT IN Common

The labels of an issue.

Environment String False LIKE,NOT LIKE,IS,IS NOT Common

The environment of an issue.

SecurityLevel String False =,!=,IS,IS NOT,IN, NOT IN Common

The security level of an issue.

FixVersionsAggregate String False Common

The fix versions of the issue

ComponentsAggregate String False Common

Aggregate list of components included in the issue.

IssueLinksAggregate String False Common

The issue links of the issue.

AffectedVersionsAggregate String False Common

The affected versions of the issue.

SubtasksAggregate String True Common

The subtasks of the issue.

CustomField1 Any False Common

A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true.

CustomField2 Any False Common

A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name Type Description
JQL String

JQL (Jira Query Language) allows you to build structured queries.

Attachments String

Search for issues that have or do not have attachments. This column can be queried with IS EMPTY or IS NOT EMPTY.

Comment String

Search for issues that have a comment that contains particular text.

Category String

Search for issues that belong to projects in a particular category.

SprintId Integer

Search for issues that belongs to a specific sprint, by specifying the Id of the Sprint.

SprintName String

Search for issues that belongs to a specific sprint, by specifying the name of the Sprint.

Filter String

Search for issues of saved filters. This column can be queried with filter name or filter id.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062