Extract values from page titles using regex

Scenario

You can extract certain values from page titles and then show them in different columns by manipulating Reporting outputs using regular expressions (regex).

In this scenario, we are going to use Page Supplier, Text Supplier, and Match Supplier supplier key and regular expressions (regex) to extract country name, code, and phone code from page titles.

The following example was implemented based on a page with the following structure:

As shown in the above screenshot, page titles have a format of "Country Name (Country Code-Phone Code)" and we will separate each of them by using regex and show them in different columns.

Result

 

Recipe

Apps

Reporting for Confluence Server & Data Center

Apps

Reporting for Confluence Server & Data Center

Level

Intermediate

Estimated time

20 minutes

Macros

Local Reporter, Report Column, Report Info, Report Table

Suppliers

Match Supplier, Page Supplier, Text Supplier

Storage format

You can copy and paste this code into the Confluence Source Editor:

<p class="auto-cursor-target"> <br /> </p> <ac:structured-macro ac:macro-id="a5cba1e5-c4b8-4862-b166-2647928ce198" ac:name="report-table" ac:schema-version="1"> <ac:rich-text-body> <p class="auto-cursor-target"> <br /> </p> <ac:structured-macro ac:macro-id="437878cf-aa05-4e77-b9e6-61a5f964169d" ac:name="local-reporter" ac:schema-version="1"> <ac:parameter ac:name="">page:children</ac:parameter> <ac:rich-text-body> <p> <br /> </p> </ac:rich-text-body> </ac:structured-macro> <p class="auto-cursor-target"> <br /> </p> <ac:structured-macro ac:macro-id="b58a39ec-31bc-4720-a16d-73fb73061669" ac:name="report-column" ac:schema-version="1"> <ac:parameter ac:name="title">Page Title</ac:parameter> <ac:rich-text-body> <p> <ac:structured-macro ac:macro-id="76f9c3b0-1b6c-4815-a217-944a20c1e174" ac:name="report-info" ac:schema-version="1"> <ac:parameter ac:name="link">true</ac:parameter> <ac:parameter ac:name="">page:title</ac:parameter> </ac:structured-macro> </p> </ac:rich-text-body> </ac:structured-macro> <p class="auto-cursor-target"> <br /> </p> <ac:structured-macro ac:macro-id="b6dc99fc-61ae-4f8f-8e3f-1138b39700f6" ac:name="report-column" ac:schema-version="1"> <ac:parameter ac:name="title">Country</ac:parameter> <ac:rich-text-body> <p> <ac:structured-macro ac:macro-id="5edbc5e9-2608-47a5-917c-a1d86f7e96c8" ac:name="report-info" ac:schema-version="1"> <ac:parameter ac:name="">page:title&gt;match "(.*?)\\((.*?)-(.*?)\\)"&gt;group 1</ac:parameter> </ac:structured-macro> </p> </ac:rich-text-body> </ac:structured-macro> <p class="auto-cursor-target"> <br /> </p> <ac:structured-macro ac:macro-id="916564d5-beef-4522-8178-bdcca66f1d55" ac:name="report-column" ac:schema-version="1"> <ac:parameter ac:name="title">Country Code</ac:parameter> <ac:rich-text-body> <p class="auto-cursor-target"> <ac:structured-macro ac:macro-id="3a8d45d9-23cd-4eb1-ac04-5e1a264eda5f" ac:name="report-info" ac:schema-version="1"> <ac:parameter ac:name="">page:title&gt;match "(.*?)\\((.*?)-(.*?)\\)"&gt;group 2</ac:parameter> </ac:structured-macro> </p> </ac:rich-text-body> </ac:structured-macro> <p class="auto-cursor-target"> <br /> </p> <ac:structured-macro ac:macro-id="e31cf1e5-4f88-4c0d-884d-cd71e3ae4235" ac:name="report-column" ac:schema-version="1"> <ac:parameter ac:name="title">Phone Code</ac:parameter> <ac:rich-text-body> <p class="auto-cursor-target"> <ac:structured-macro ac:macro-id="17eae913-5333-444e-882d-97f361108bfd" ac:name="report-info" ac:schema-version="1"> <ac:parameter ac:name="">page:title&gt;match "(.*?)\\((.*?)-(.*?)\\)"&gt;group 3</ac:parameter> </ac:structured-macro> </p> </ac:rich-text-body> </ac:structured-macro> <p class="auto-cursor-target"> <br /> </p> </ac:rich-text-body> </ac:structured-macro> <p class="auto-cursor-target"> <br /> </p>

Macro structure

You can recreate the example in the editor view:

Steps

  1. Add a Report Table macro and within it add one Local Reporter macro and three Report Column macros.

  2. Set the Local Reporter macro's Key parameter to "page:children".

  3. Set the first Report Column macro's Title to "Page Title" and add a Report Info macro within it.
    Set the Report Info macro's Key to "page:title" and tick its Link to item parameter.

  4. Set the second Report Column macro's Title to "Country" and add a Report Info macro within it.
    Set the Report Info macro's Key to "page:title>match "(.*?)\\((.*?)-(.*?)\\)">group 1". 

  5. Set the second Report Column macro's Title to "Country Code" and add a Report Info macro within it.
    Set the Report Info macro's Key to "page:title>match "(.*?)\\((.*?)-(.*?)\\)">group 2".

  6. Set the third Report Column macro's Title to "Phone Code" and add a Report Info macro within it.
    Set the Report Info macro's Key to "page:title>match "(.*?)\\((.*?)-(.*?)\\)">group 3".

Regex explanation

(.*?)\\((.*?)-(.*?)\\) regex is used to group page titles output into three groups:

Group 1: (.*?)

To select all values until opening bracket '(' character 

Skipped: \\(

Opening bracket '(' character which has been excluded from groups

Group 2: (.*?)

To select all characters after opening bracket and before first dash '-' character 

Skipped: -

Dash '-' character which has been excluded from groups

Group 3: (.*?)

To select all values after first dash '-' character and before closing bracket ')'

Skipped:\\)

Closing bracket ')' character which has been excluded from groups

Â