Common

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

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 Confluence Data Provider to the Reporting Table View macro body

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

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 to the Reporting Table View macro body. Otherwise drag and drop it there.

Specify the following parameter values:

• Query = space=CRD AND type=page
  • This CQL query specifies all the pages of CRD space. “CRD” is a space key.
  • Any other correct CQL query may be specified here.
• Expand = version,history.lastUpdated,children.page,children.comment
  • This list of expanded fields specifies which parts of data are required.
  • Expand list may be left empty, but not all page info is returned by default.
• Max results = 7
  • We are making a “Top 7 pages” report here, so we limit the number of pages 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 5-column report we will add 5 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 six (not five!) 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 “Page title” column

In the following step we will configure the column, which will show the page title and it will be a link to the page itself.

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 = Page title
  • We will show page titles, which will be the links to the appropriate pages.
• 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/wiki" & CurrentItem._links.webui & "'>" & CurrentItem.title & "</a>"
  • This example is for Confluence Cloud.
  • Here is an example for the Server/DC version: "<a href='http://{yourSiteName}/confluence" & CurrentItem.url & "'>" & CurrentItem.content.title & "</a>"
  • CurrentItem is a predefined variable, which contains a current row JSON object. Reporting Table macro iterates through the set 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 goes the path to the JSON field. 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 paths are pretty simple – title object is placed right after the root and webui object is a child object of the _links
  • 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 “Page title” column with the titles of the pages, which are the links to the appropriate pages. The footer will contain the number of the pages.

Step 3.2: Configuring Reporting Table Item macro for the “Ver.” (version) column

In the following step we will configure the column, which will show the page version.

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 =
  • We will show page versions, which are like “1” or “123”, so it’s pretty short labels, thus we do not specify a long “Version” word and replace it with “Ver.” to save column width.
• 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 = CurrentItem.version.number
  • This example is for Confluence Cloud.
  • Here is an example for the Server/DC version: CurrentItem.content.version.number
• 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 “Ver.” column with the versions of the pages. The footer will contain nothing .

Step 3.3: Configuring Reporting Table Item macro for the “Modified by” column

In the following step we will configure the column, which will show the last editor name.

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 = Modified by
  • We will show the user name, which is like “admin” or “CelesteCS”.
• 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 = CurrentItem.version.by.displayName
  • This example is for Confluence Cloud.
  • Here is an example for the Server/DC version: CurrentItem.content.version.by.username
• 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 “Modified by” column with the names of the latest editors. The footer will contain nothing.

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

In the following step we will configure the column, which will show the page creation date.

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 = Created
  • 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 plain text value.
• Body content = "<time datetime='" & TEXT(DATEVALUE(CurrentItem.history.createdDate), "YYYY-MM-DD") & "' class='date-past'></time>"
  • This example is for Confluence Cloud.
  • Here is an example for the Server/DC version: "<time datetime='" & TEXT(DATEVALUE(CurrentItem.content.history.createdDate), "YYYY-MM-DD") & "' class='date-past'></time>"
  • 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. The footer will contain nothing.

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

In the following step we will configure the column, which will show the number of the page subpages.

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 = Subpages
  • We will show the number of subpages as plain text.
• 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 = JSONARRAYLENGTH(CurrentItem.children.page.results)
  • This example is for Confluence Cloud.
  • Here is an example for the Server/DC version: JSONARRAYLENGTH(CurrentItem.content. children.page.results)
  • In this column we just calculate the number of subpages as the number of elements in children.page.results JSON array.
• Body output type = Plain text (default)
  • The body will be a plain text, no formatting.
• Footer content type = Single expression
  • The footer will be a calculated value.
• Footer content = "Max: " & MAX(JSONARRAYTOARRAY(JSONARRAYEXTRACTPATH(AllItems, "children.page.size", "0")))
  • This example is for Confluence Cloud.
  • Here is an example for the Server/DC version: "Max: " & MAX(JSONARRAYTOARRAY(JSONARRAYEXTRACTPATH(AllItems, "content.children.page.totalSize", "0")))
  • This formula looks tricky, but it is really simple: we just extract the values of all CurrentItem.page.size object values into an array (JSONARRAYEXTRACTPATH function), then convert it into a generic array (JSONARRAYTOARRAY function) and then take maximum value of this object (MAX function).
• Footer output type = Plain text (default)
  • We will show the result as a plain text.

Save/Publish the page. You should see the “Subpages” column with the numbers of subpages. The footer will contain the maximum number of pages.

Result

As a result we’ve got a report on Confluence pages. It shows the limited number of pages. For each page it shows its name with the link to it, version number, modifier name, creation date and the number of subpages. The footer contains the calculated number of shown pages and the maximum number of subpages.