How to display the data for a date range using Run and SQL macros

This article provides step-by-step instructions to generate and display a report for a specific date range using Run Self-Service Reports and SQL for Confluence apps.

Instructions

Follow these steps:

1. Log into the Confluence instance.

2. Navigate to the required page and click the Edit icon ().
3. Insert Run with a user form and parameters macro to build the form.
4. Click Edit to open the macro settings window.
   
5. In the Field settings, click +Add field to add additional fields to the form.
   a. Since we need a report for a date range, make sure you have at least two fields with Date type (choose Field type as Date).
   
   
   b. Enter the name of the button in the field: Run button text. A button with the given text will be displayed on the form to perform a specific action.
   
6. Click Save settings and Save the page.
   
7. Insert SQL Query macro under the Run with a user form and parameters macro.
   
8. Edit the SQL Query macro to open the settings pop-up.
9. Select the required Data source profile from the drop-down.
   

10. Enter the SQL statement based on the Field key mentioned in Step# 5.
    In the below query, used the CAST operator to convert the literal strings '2003-01-01' and '2003-12-31' to the DATE values.
    

    Select * from tabel_name where column_name BETWEEN CAST('$date2' AS DATE) AND CAST('$date1' AS DATE);

    
    

11. Click Save settings and Save the confluence page.
    
12. Enter the date range based on the date format mentioned in Step5 and click Show Data on the form.
    
    Data is shown as below:
    
    If you enter the incorrect date format, then below error message will be shown.