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 the Run Self-Service Reports and SQL for Confluence apps.

Instructions

1. Log into your Confluence instance.

2. Navigate to the required page and click .
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 Field settings, click +Add field to add fields to the form. Ensure to select Field type as Date for at least two fields so as to provide a date range.
   
6. Enter the name of the button in the Run button text field. This ensures that a button with the given text is displayed on the form.
   
7. Click Save settings and Save the page. The form is displayed as follows:
   
8. Insert SQL Query macro inside the Run with a user form and parameters macro.
   
9. Edit the SQL Query macro to open the settings pop-up.
10. Select the required Data source profile from the drop-down.
    

11. Enter the SQL statement based on the Field key mentioned in step 5.
    In the following query, the CAST operator is used to convert the literal strings'2003-01-01' and '2003-12-31' to DATE values.
    

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

    
    

12. Click Save settings and Save the page.
    
13. Enter the date range or select the dates based on the date format mentioned in step 5, and click Show Data on the form.
    

The given example displays the following information:

If the user entered a date in an incorrect format (that is, not in the format as specified in the Date format field in step 5), error messages are shown as: