Example: Creating report on Jira issues

Common

In this article we will show how to create a report with the list of Jira issues.

Step 1: Adding Reporting Table View macro to a page

In the following step we will add and configure a Reporting Table View macro. This macro is used as a container for a single reporting data provider and one to many reporting table items. The data is retrieved according to the data provider parameters and then formed to a table according to the reporting table items parameters.

Open the macro browser, locate the Reporting Table View macro and place it to the page. The faster way is to open the Insert macro menu, enter the beginning of the macro name (all Reporting macro names start with ‘Reporting’) and click on the Reporting Table View macro.

 

The Reporting Table view macro will appear on the page. You may leave all the parameter values in default state. If you need to show some special text when no items are returned from the data provider, change the Empty response text content parameter value. If you need to show HTML instead of plain text, specify your html in the Empty response text content parameter and change the Empty response text output type parameter value to HTML. We will leave all parameters by default here.

 

After all parameters are set, close the macro parameters window.

 

Step 2: Adding Reporting Jira Data Provider to the Reporting Table View macro body

In the following report we will add and configure the Reporting Jira Data Provider. Reporting Jira Data Provider as well as Reporting Confluence Data Provider macros read data from the appropriate sources and provide it to the view macro. In this example we will add the Reporting Jira Data Provider macro for it to read the list of issues from the specified Jira.

Click on the Reporting Table View macro body to put the focus on it.

Open Insert macro menu, enter “Reporting” and click on Reporting Confluence Data Provider macro.

 

Ensure that the macro has been added into the Reporting Table View macro body. Otherwise drag and drop it there.

Specify the following parameter values:

  • Source = https://{yourSiteName}.atlassian.net/jira/
    • In the case of Cloud the URL of your favorite Jira should be specified.
    • In the case of Server/Datacenter you should specify the Application link name. If it is not specified, a primary link will be used.
    • In the case of Cloud you should also install CelesteCS Reporting for Confluence Cloud - Jira Integration plug-in on your favorite Jira instance. As Reporting operates on behalf of the current user, it needs to be acknowledged on the appropriate Jira instance – that’s the goal of the integration plug-in.
  • Query = project=REP ORDER BY id
    • This JQL query specifies all the issues of the REP project. “REP” is a project key.
    • Any other correct JQL query may be specified here.
  • Fields = Empty value (default)
    • This value may be left by default, as the standard set of fields, returned by the Jira Data Provider will be enough for this example.
  • Max results = 7
    • We are making a “Top 7 issues” report here, so we limit the number of issues being returned.
    • Max number cannot be higher than 1000 anyways.

  

Step 3: Adding Reporting Table Item macros to the Reporting Table View macro body

In the following step we will add and then configure the Reporting Table Item macros, which contain all the needed information about the report columns.

Reporting Table Item macro holds the parameters for a single reporting table column. You need to add one Reporting Table Item macro for each column, so for our 6-column report we will add 6 macros.

Click on the Reporting Table View macro body to put the focus on it. Open the Insert macro menu, enter “Reporting” and click on the Reporting Table Item macro.

 

Do it seven (not six!) times. One Reporting Table Item is an extra one, which will help us to configure a report. If the Reporting Table Item macro is added not to the Reporting Table View body, just drag and drop it there.

Note that the order of Reporting Table Item macros makes sense – the columns will be created in the same order.

  

Step 3.0: Configuring a helper Reporting Table Item macro instance

In the following step we will create a helper column, which will help us to see what is really returned from the data provider. This step may be skipped, as it is actually a trick, which helps a lot in configuring reports. We will configure a macro, which will show the content of the JSON objects, returned by the data provider.

Select the last Reporting Table Item macro and click the edit button.

Set the following parameter values:

  • Header content type = Text only (default)
    • We will not add any formula to this column header.
  • Header content = Helper
    • We specify the header name just to see it works.
  • Header output type = Plain text (default)
    • The header will be a standard header with plain text, no formatting.
  • Body content type = Single expression (default)
    • We will specify a formula here, which will create a HTML code.
  • Body content = CurrentItem
    • This is the main thing here – we configure the Reporting Table Item to show the whole CurrentItem variable contents, which is a JSON object. It will help us to find the paths to the variables, which values we need to show.
  • Body output type = Plain text (default)
    • JSON objects should be returned as a Plain text.
  • Footer content type = Text only (default)
    • We will not add any formula to this column footer.
  • Footer content = Empty string (default)
    • If you leave empty text for all Reporting Table Item’s footers, the footer row will not be shown at all.
  • Footer output type = Plain text (default)
    • It does not matter in this case, actually.

Publish/Save the page. You will see one column five (as we did not configure the first five yet) with JSON objects’ content.

  

You may copy one cell content to your favorite text editor for further usage. When the JSON object is copied to another editor, open this page editor and remove this Reporting Table Item macro, as we do not need it any more.

Step 3.1: Configuring Reporting Table Item macro for the “Key” column

In the following step we will configure the column, which will show the issue keys and it will be the links to the issues themselves.

Open the page editor.

Select the first Reporting Table Item macro and click the edit button.

Set the following parameter values:

  • Header content type = Text only (default)
    • We will not add any formula to this column header.
  • Header content = Key
    • We will show issue keys, which will be the links to issues themselves.
  • Header output type = Plain text (default)
    • The header will be a standard header with plain text, no formatting.
  • Body content type = Single expression (default)
    • We will specify a formula here, which will create a HTML code.
  • Body content = "<a href='https://{yourSiteName}.atlassian.net/browse/" & CurrentItem.key & "'>" & CurrentItem.key & "</a>"
    • This body content example is for Confluence Cloud. The example for the Server/DC version is the same.
    • CurrentItem is a predefined variable, which contains a current row JSON object. Reporting Table macro iterates through the array of JSON objects, returned by the data provider to build table rows and CurrentItem is a JSON object for the current row.
    • After CurrentItem variable name, the path to the JSON field is following. You may specify any JSON object by this path. The path may contain object names (containing letters, digits and “_” symbol only) and JSON array indexes. In this example the path is pretty simple – the key object is a child object of the root
    • As you may see, there is a difference in Cloud and Server/DC server paths.
    • Moreover, the Server/DC version JSON object contains the most of its parameters in content That’s why we advise you to add a dummy column during report configuration – you will see what is really returned and what you may work with.
    • You may apply any function to these variables, which is the most powerful feature of this product.
  • Body output type = HTML
    • It is convenient sometimes to set “Plain Text” here during configuring the report first. In this case you will see the result HTML as a plain text and ensure that it is correct. After that you may switch to HTML.
  • Footer content type = Single expression
    • We will add a formula to the footer to show the number of elements in the table.
  • Footer content = "Count: " & JSONARRAYLENGTH(AllItems)
    • This formula concatenates the “Count: ” string with the number of items in AllItems
    • AllItems is a predefined variable, which contains an array of all JSON objects in the data provider output.
  • Footer output type = Plain text (default)
    • We will not make a HTML text here.

Save/Publish the page. You should see the “Key” column with the keys of the issues, which are the links to the appropriate issues. The footer will contain the number of the issues.

Step 3.2: Configuring Reporting Table Item macro for the “Summary” column

In the following step we will configure the column, which will show the issue summary, which will be the link to the issue itself.

Open the page editor.

Select the second Reporting Table Item macro and click the edit button.

Set the following parameter values:

  • Header content type = Text only (default)
    • We will not add any formula to this column header.
  • Header content = Summary
    • We will show issue summary field values.
  • Header output type = Plain text (default)
    • The header will be a standard header with plain text, no formatting.
  • Body content type = Single expression (default)
    • We will specify a formula here, which will create a HTML code.
  • Body content = "<a href='https://{yourSiteName}.atlassian.net/browse/" & CurrentItem.key & "'>" & CurrentItem.fields.summary & "</a>"
    • This body content example is for Confluence Cloud. The example for the Server/DC version is the same.
  • Body output type = HTML
    • The body will be a plain text, no formatting.
  • Footer content type = Text only (default)
    • The footer will be a standard footer with no text.
  • Footer content = Empty string (default)
    • No value is specified here.
  • Footer output type = Plain text (default)
    • We will not make any text here.

Save/Publish the page. You should see the “Summary” column with the summaries of the issues, which are the links to the issues themselves.

Step 3.3: Configuring Reporting Table Item macro for the “P” (Priority) column

In the following step we will configure the column, which will show the images of the priorities of the issues.

Open the page editor.

Select the third Reporting Table Item macro and click the edit button.

Set the following parameter values:

  • Header content type = Text only (default)
    • We will not add any formula to this column header.
  • Header content = P
    • We will show icons of the priorities, which are pretty small. That’s why we make the column name shorter.
  • Header output type = Plain text (default)
    • The header will be a standard header with plain text, no formatting.
  • Body content type = Single expression (default)
    • We will specify a formula here, which will create a plain text value.
  • Body content = "<img src='" & CurrentItem.fields.priority.iconUrl & "' width='16' height='16'/>"
    • This body content example is for Confluence Cloud. The example for the Server/DC version is the same.
  • Body output type = HTML
    • The body will be a HTML with IMG tag.
  • Footer content type = Text only (default)
    • The footer will be a standard footer with no text.
  • Footer content = Empty string (default)
    • No value is specified here.
  • Footer output type = Plain text (default)
    • We will not make any text here.

Save/Publish the page. You should see the “P” column with the images of the issues priorities.

Step 3.4: Configuring Reporting Table Item macro for the “Status” column

In the following step we will configure the column, which will show the statuses of the issues. We will use the standard Status macro to show the statuses in the similar way with Jira.

Open the page editor.

Select the fourth Reporting Table Item macro and click the edit button.

Set the following parameter values:

  • Header content type = Text only (default)
    • We will not add any formula to this column header.
  • Header content = Status
    • We will show the status of the issue as a standard Status macro with appropriate value.
  • Header output type = Plain text (default)
    • The header will be a standard header with plain text, no formatting.
  • Body content type = Single expression (default)
    • We will specify a formula here, which will create a HTML code.
  • Body content = "<span class='status-macro aui-lozenge conf-macro aui-lozenge-" & SWITCH(CurrentItem.fields.status.name, "To Do", "default", "In Progress", "current", "Done", "success", "default") & " output-inline' data-hasbody='false' data-macro-name='status'>" & CurrentItem.fields.status.name & "</span>"
    • This body content example is for Confluence Cloud. The example for the Server/DC version is the same.
    • In this column we create a HTML code for adding a Status macro, which has the color value based on the issue status value. We use the SWITCH function to choose the color.
  • Body output type = HTML
    • The body will be a HTML code.
  • Footer content type = Text only (default)
    • The footer will be a standard footer with no text.
  • Footer content = Empty string (default)
    • No value is specified here.
  • Footer output type = Plain text (default)
    • We will not make any text here.

Save/Publish the page. You should see the “Status” column with the issue statuses.

Step 3.5: Configuring Reporting Table Item macro for the “Updated” column

In the following step we will configure the column, which will show the latest issue update date.

Open the page editor.

Select the fifth Reporting Table Item macro and click the edit button.

Set the following parameter values:

  • Header content type = Text only (default)
    • We will not add any formula to this column header.
  • Header content = Updated
    • We will show the date as a standard Confluence date.
  • Header output type = Plain text (default)
    • The header will be a standard header with plain text, no formatting.
  • Body content type = Single expression (default)
    • We will specify a formula here, which will create a HTML code.
  • Body content = "<time datetime='" & TEXT(DATEVALUE(CurrentItem.fields.updated), "YYYY-MM-DD") & "' class='date-past'></time>"
    • This body content example is for Confluence Cloud. The example for the Server/DC version is the same.
    • In this column we create a HTML code for adding a date just like the date is specified in Confluence.
  • Body output type = HTML
    • The body will be a plain text, no formatting.
  • Footer content type = Text only (default)
    • The footer will be a standard footer with no text.
  • Footer content = Empty string (default)
    • No value is specified here.
  • Footer output type = Plain text (default)
    • We will not make any text here.

Save/Publish the page. You should see the “Created” column with the page creation dates.

Step 3.6: Configuring Reporting Table Item macro for the “Resolution” column

In the following step we will configure the column, which will show the resolutions of the issues.

Open the page editor.

Select the sixth Reporting Table Item macro and click the edit button.

Set the following parameter values:

  • Header content type = Text only (default)
    • We will not add any formula to this column header.
  • Header content = Resolution
    • We will show the issue resolutions.
  • Header output type = Plain text (default)
    • The header will be a standard header with plain text, no formatting.
  • Body content type = Single expression (default)
    • We will specify a formula here, which will create a plain text value.
  • Body content = SWITCH(CurrentItem.fields.resolution, "", "None", CurrentItem.fields.resolution.name)
    • This body content example is for Confluence Cloud. The example for the Server/DC version is the same.
    • In this column use SWITCH function to process the case, when there is no resolution in the JSON object.
  • Body output type = Plain text (default)
    • The body will be a plain text, no formatting.
  • Footer content type = Text only (default)
    • The footer will be a standard footer with no text.
  • Footer content = Empty string (default)
    • No value is specified here.
  • Footer output type = Plain text (default)
    • We will not make any text here.

Save/Publish the page. You should see the “Resolutions” column with the resolutions of the issues.

Result

As a result, we’ve got a report on Jira issues. It shows the limited number of issues. For each issue it shows its key, summary, priority icon, status, last updated date and resolution. The footer contains the calculated number of issues.