On this page
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 will 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 will 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