How to generate choice values from one table and display results from another using Run Self Service Reports

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 Rules. Users are allowed to select the zone_id from the Zone table and display all the rows associated with zone_id in the Rules tables. Here, zone_id is the primary key in the Zone table and Demog is the foreign key in the Rules table. Please see below the structure of Zones and Rules tables.

Zone

Zone_ID	Zone
200	Stanislaus
201	Brower
202	Alton

Rules

Demog	Rules
200	IsDisabled=True
200	isRequired=False
201	IsDisabled=False
201	isRequired=True

Instructions

This example makes use of the apps below:

The groovy code below 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 that is displayed upon 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 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.

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

Modify the SQL data source, table names, and columns as per your instance.