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_Wordsas 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_Redas 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_Wordsas the variable and then click on OK. - Select the Quantity column and attach
Show_Redto the Style Variable property. - Now from Conditional Explorer, iterate through every condition for the different values of
Convert_To_Wordsand set corresponding text for the Quarter column, that is, set First Quarter for value 1, and so on. - For
Show_Redas 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.