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.
Create a simple list report with Current year and Current quarter columns from the Sales | Time Dimension query subject.
Drag Quantity from Sales Fact.
Group by current year and sort by current quarter.
- Go to Condition Explorer and click on Variables.
- Drag a new string variable from Insertable Objects pane. Define the expression as:
[Query1].[Current quarter]. - Change the name of variable to
Convert_to_words.
- 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 as
Show_Red.
- Go to report page and select the Current quarter column. For Text source variable property, select
Convert_to_wordsas the variable. - Select Quantity columns 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 Current quarter column, that is, set to First Quarter for value 1, and so on. - For
Show_Redas yes, select Quantity column and change the background color to red. - Run the report to test the output.
Here we are defining 'Conditional variables' to trap the specific conditions and perform required action on corresponding rows. There are three types of conditional variables: String, Boolean, and Report language variable.
This 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.
This variable is useful when the expression only evaluates into true or false and you need to format the entries accordingly.
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 corresponding language, or show the respective country flag in header).
Here, we have used one variable of String type and one of Boolean type.
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.
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.