How to use SQL case statement to improve the visual appeal

Description

Most databases support some flavor of the SQL case statement making it easy to modify what appears in a column. This article illustrates the process of combining a case statement with Confluence capabilities like macros to enhance the way information is displayed.

Using emoticons

This example uses emoticons to visually indicate some aspects of the data imported to the viewers.

• Use a standard SQL Query macro to access your data source that contains the data you want to query.
• Use wiki for the Output format parameter.
• SQL utilizes a case statement. In this example, we use Postgres but other databases can require similar case statements. 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

Using macros

This example uses a Confluence macro to add visual pointers.

• Use a standard SQL Query macro to access your data source that contains the data you want to query.
• Use wiki for the Output format parameter.
• SQL utilizes a case statement. In this example, we use Postgres but other databases can require similar case statements. 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