How to generate choice values using SQL

Links

• Release notes 2.0
• How to generate choice values using replace and render

Referenced Plugins

• Run Self-Service Reports for Confluence - release 2.0.0 or above
• SQL for Confluence
• Scripting for Confluence - release 3.3.0 or above
• Cache for Confluence

1. Design a page with the run macro as normal with a few dummy choices
2. Create a choices sql select statement that produces a set of rows where each row is a text field of the form: value:choice: - value is the replacement value that is needed and choice is the choice text that the user will see
3. Setup the sql-query macro on a page to run the choices sql
   • Use table=false
   • Verify the result of the sql-query macro is a colon separated string of values and choice text
4. Copy the groovy example and replace the run and sql portions with your definitions
   • Note that the $ in front of replacement variables used must be escaped in groovy by the **

{groovy:output=wiki}
import com.atlassian.renderer.v2.RenderMode

def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)

def choiceSql = "select distinct(year) || ':' || 'Report ' || year || ':' as choice from reports order by choice desc"

def choiceMacro = "{sql-query:datasource=testDS|table=false} ${choiceSql} {sql-query}"

def choices = subRenderer.render(choiceMacro, context, renderMode)

def runMacro = """
{run:id=dynamic|autorun=true|replace=report:2010::select::${choices}}
{sql-query:datasource=testDS}
select * from reports where year = \$report
{sql-query}
{run}
"""

out.println runMacro
{groovy}

If the choices only need to be updated occassionally, a non-dynamic approach might be best. Use the run and sql macro similar to above to run the choices sql and then manually cut and paste into the page with the results. This alternative does not need the groovy macro.

{run:titleRun=Generate choices}
{sql-query:datasource=testDS|table=false} select distinct(year) || ':' || 'Report ' || year || ':' as choice from reports order by choice desc {sql-query}
{run}

If the choices need to be more dynamic or the page is heavily used and you are concerned about dynamically using groovy on the page, then this is a better alternative. However, it does require a few more parts. The basic idea is to use the groovy code to generate a form page. The form page can be used directly or it can be included in another page. So, up to 3 pages are involved:

• form page that will be automatically updated
• generate page that administrators use to generate the form page
• user access page that includes the form page

Steps

1. Follow steps #1 - #5 as before
2. Create a form page with a title of your choosing
3. Create a generate page
   • Intended only for administrators or automation

   • Modify the following example script with your specifics including form page title

     {run2:titleRun=Update form page}
     
     {groovy}
     import com.atlassian.renderer.v2.RenderMode
     
     def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)
     
     def choiceSql = "select distinct(year) || ':' || 'Report ' || year || ':' as choice from reports order by choice desc"
     
     def choiceMacro = "{sql-query:datasource=testDS|table=false} ${choiceSql} {sql-query}"
     
     def choices = subRenderer.render(choiceMacro, context, renderMode)
     
     def runMacro = """
     {run:id=dynamic|autorun=true|replace=report:2010::select::${choices}}
     {sql-query:datasource=testDS}
     select * from reports where year = \$report
     {sql-query}
     {run}
     """
     
     def title = context.getPageTitle() + " - form"
     def spaceKey = context.getSpaceKey()
     
     def page = pageManager.getPage(spaceKey, title)
     
     if (page != null) {
     page.setContent(runMacro)
     println "Form page '${title}' updated."
     } else {
     println "Page '${title}' not found."
     }
     
     {groovy}
     
     {run2}
     

4. Use the generate form button to update the content of the form page
5. Optionally, add autoRun=true so automation can render the page on a schedule to keep the form current automatically