Page Comparison

This article demonstrates the use case to generate choice values from a table using Run macro and display the results from another table as the output.

Use

...

case

The SQL database has two tables called "Zone" and " and Rules". Users are allowed to select a "the zone_id" from  from the Zone table and display all the rows associated with that with zone_id in the Rules tables. Here Here, "zone_id" is  is the primary key in in the Zone table and "Demog" is the table and Demog is the foreign key in the Rules table. Please see below the structure of Zones and Rules tables.

Zone 

Rules

Instructions

This example makes use of the apps below-given apps.:

• SQL for Confluence
• Scripting for Confluence

• Run Self Service Reports

The groovy code below groovy code provides users with a form that has a select list dropdown to select the user choice value of zone id. This zone id is then passed to the rules table which that is displayed upon hitting clicking the Run Button.

{groovy:output=wiki}
import com.atlassian.renderer.v2.RenderMode
def choiceSql = "select distinct concat(zone_id, ':', zone_id , ':') from zone"
 
def choiceMacro = "{sql-query:datasource=yoga-sql|table=false} ${choiceSql} {sql-query}"
 
def choices = subRenderer.render(choiceMacro, context, RenderMode.suppress(RenderMode.F_FIRST_PARA))
 
def runMacro = """
{run:id=dynamic|autorun=true|replace=report:2010::select::${choices.replaceAll(" ","::")}}
{sql-query:datasource=yoga-sql}
select * from rules where demog = \$report
{sql-query}
{run}
"""
 
out.println runMacro
{groovy}

The groovy code below groovy code adds row formatting on the SQL output using CSS in SQL for Confluence:

{groovy:output=wiki}
import com.atlassian.renderer.v2.RenderMode
def choiceSql = "select distinct concat( zone_id,':', zone_id,':') from zone ;"
 
def choiceMacro = "{sql-query:datasource=yoga-sql|table=false} ${choiceSql} {sql-query}"
 
def choices = subRenderer.render(choiceMacro, context, RenderMode.suppress(RenderMode.F_FIRST_PARA))
 
def runMacro = """
{run:id=dynamic|autorun=true|replace=report:2010::select::${choices.replaceAll(" ","::")} }
{sql-query:datasource=yoga-sql|rowStyles= ,background-color:lightblue,}

select * from rules where demog = '\$report';

{sql-query}
{run}
"""
 
out.println runMacro
{groovy}

The end result would be as shown in the screenshot below.

Image Modified

1. The customer is asked to select a zone Id from the drop-down.
2. Hit the Run button to generate the output
3. The output shows is displyed in the table results.