Description

Most databases support some flavor of the SQL case statement making it easy to condition what appears in a column. This will show how to combine this with Confluence capabilities like macros to improve how information is displayed.

Example Using Emoticons

This example uses emoticons to visually indicate some aspect of the data this is import to highlight to the viewers.

• Use a standard sql-query macro accessing your data source containing the data you want to query.
• Use wiki for the Output format parameter.
• The SQL will use a case statement. This example uses Postgres, but other database will be similar. Consult your database documentation's case statement for more details.

select 
     "Product",
     "Plan",     
     "Revenue",
     case 
         when "Revenue" >= "Plan" then '(/)'
         else '(x)'
     end as "Met Plan"
 from products

Example Using Macros

This example uses a Confluence macro to add visual indicators to the display.

• Use a standard sql-query macro accessing your data source containing the data you want to query.
• Use wiki for the Output format parameter.
• The SQL will use a case statement. This example uses Postgres, but other database will be similar. Consult your database documentation's case statement for more details.

select 
     "Product",
     "Plan",      
     case 
         when "Revenue" >= "Plan" then '{color:green}*' || "Revenue" || '*{color}'
         else '{color:red}*' || "Revenue" || '*{color}'
     end as "Revenue"
 from products