- IBM Cognos 10 Report Studio Cookbook(Second Edition)
- Ahmed Lashin Abhishek Sanghani
- 621字
- 2021-08-06 17:03:26
Adding conditional formatting
The business wants to see company sales figure by years and quarters. They want to highlight the entries where sales are below 5,000,000.
We will assume that database provides us the Quarter number and we need to convert that to words. We will use conditional formatting for that. Also, where sales is below 5 million, the cell will be shown in red using another conditional variable.
Getting ready
Create a simple list report with Year and Quarter (numeric) columns from the Sales / Time query subject.
Drag Quantity from Sales fact.
Group by Year and sort by Quarter.
How to do it...
Conditional formatting is used to enhance the report visualization. Here is how we can define conditional formatting in the created report:
- Go to Condition Explorer and click on Variables as shown in the following screenshot:
- Drag a new string variable from the Toolbox pane. Define the expression as
[Query1].[Quarter (numeric)]
. - Change the name of the variable to
Convert_To_Words
as shown in the following screenshot: - Add four values for the variable; the numbers 1 to 4.
- Now add a Boolean variable and define it as
[Query1].[Quantity]< < 5000000
. - Call this variable
Show_Red
as shown in the following screenshot: - Go to the report page and select the Quarter (numeric) column. For the Text Source Variable property, select
Convert_To_Words
as the variable and then click on OK. - Select the Quantity column and attach
Show_Red
to the Style Variable property. - Now from Conditional Explorer, iterate through every condition for the different values of
Convert_To_Words
and set corresponding text for the Quarter column, that is, set First Quarter for value 1, and so on. - For
Show_Red
as yes, select the Quantity column and change the background color to red. - Run the report to test the output as shown in the following screenshot:
How it works...
Here we are defining conditional variables to trap the specific conditions and perform required actions on corresponding rows. There are three types of conditional variables: String, Boolean, and the report language variable.
The String variable
The String type of variable allows you to define different possible values that the expression can be evaluated into. You only need to define the values for which you need to define specific style or text. The rest are taken care of by the Other condition.
The Boolean variable
This variable is useful when the expression only evaluates into true or false and you need to format the entries accordingly.
The report language variable
This type of variable returns the language in which report is run by the user. You don't need to define any expression for this type of variable. You simply need to choose the languages for which you want to perform certain actions (like display titles in the corresponding language, or show the respective country flag in header).
Here, we have used one variable of String type and one of Boolean type.
There's more...
There are some other important style variables to check out.
The style variable property
By assigning a variable to this property, we can control the styling aspect of the object which includes font, colors, data format, visibility, and so on.
The text source variable property
By assigning a variable to this property, we can control the text/values being shown for that object. We can provide static text or a report expression. We can also choose to show value or label of another data item in the selected object.
In this example, we used this property to display the appropriate quarter name. Please note that it was possible to achieve the same result by putting a CASE statement in the data expression. However, the purpose here is to highlight the function of text source variable.
- Flask Web全棧開發(fā)實戰(zhàn)
- DevOps:軟件架構(gòu)師行動指南
- Python程序設(shè)計(第3版)
- 樂學Web編程:網(wǎng)站制作不神秘
- Data Analysis with IBM SPSS Statistics
- Visual C++串口通信技術(shù)詳解(第2版)
- Instant QlikView 11 Application Development
- 概率成形編碼調(diào)制技術(shù)理論及應(yīng)用
- 深入淺出Serverless:技術(shù)原理與應(yīng)用實踐
- Mastering JavaScript High Performance
- Nginx實戰(zhàn):基于Lua語言的配置、開發(fā)與架構(gòu)詳解
- Android玩家必備
- Java程序員面試筆試寶典(第2版)
- 打開Go語言之門:入門、實戰(zhàn)與進階
- Hands-On JavaScript for Python Developers