官术网_书友最值得收藏!

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 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.

How to do it...

  1. Go to Condition Explorer and click on Variables.
    How to do it...
  2. Drag a new string variable from Insertable Objects pane. Define the expression as: [Query1].[Current quarter].
  3. Change the name of variable to Convert_to_words.
    How to do it...
  4. Add four values for the variable; the numbers 1 to 4.
    How to do it...
  5. Now add a Boolean variable and define it as: [Query1].[Quantity]<5000000.
  6. Call this variable as Show_Red.
    How to do it...
  7. Go to report page and select the Current quarter column. For Text source variable property, select Convert_to_words as the variable.
  8. Select Quantity columns and attach Show_Red to the Style Variable property.
  9. Now from Conditional Explorer, iterate through every condition. For the different values of Convert_to_words and set corresponding text for the Current quarter column, that is, set to First Quarter for value 1, and so on.
  10. For Show_Red as yes, select Quantity column and change the background color to red.
  11. Run the report to test the output.
How to do it...

How it works...

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.

String 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.

Boolean variable

This variable is useful when the expression only evaluates into true or false and you need to format the entries accordingly.

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 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...

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.

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.

主站蜘蛛池模板: 论坛| 古丈县| 河北区| 将乐县| 正蓝旗| 公安县| 扶风县| 邵阳县| 北宁市| 汤原县| 上林县| 赤壁市| 桐庐县| 柯坪县| 宁津县| 清徐县| 都安| 蒲城县| 宁德市| 临海市| 兴城市| 佛冈县| 肇东市| 宝清县| 陆良县| 太湖县| 讷河市| 昌宁县| 晋宁县| 松溪县| 林西县| 阿拉尔市| 财经| 离岛区| 读书| 大埔县| 沧州市| 新和县| 土默特左旗| 凤台县| 肥西县|