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

Running difference: Negative values in brackets and red

Business owners need to see the sales figures by months and their month-on-month difference.

If the difference is negative (fall in sales) then it needs to be shown in red and values need to be in brackets.

Getting ready

Create a simple list report with Time Dimension | Current year, Time Dimension | Current month, and Sales fact | Quantity as columns.

Group Current year and sort Current month ascending.

How to do it...

  1. Add a new query calculation to the list. Define the expression as:
    running-difference([Quantity])
    

    Call this item as "Running Difference".

  2. Open the Data Format properties for this calculation from Property list.
  3. Set the Format type as Number and the Negative Sign Symbol as brackets ().
    How to do it...
  4. Now go to Condition Explorer and create a new condition variable of Boolean type. Define the condition as:
    [Query1].[Running Difference] < 0
    
    How to do it...
  5. Call the variable as Show_Red.
  6. Now go back to report page and select the Running Difference column. Assign Show_Red variable as Style Variable from the property list.
  7. Choose the Yes condition for Show_Red from the conditional explorer. Select 'Running Difference' column from the list and open its Font properties.
  8. Set the font foreground color to red.
    How to do it...
  9. Click the OK button. Double click on the green bar to come out of condition. Run the report to test.
    How to do it...

How it works...

Running difference

One purpose of this recipe is to introduce you to powerful aggregation functions provided by Cognos. The Running difference function returns difference between value in current row and previous row. You can also control the scope and level of aggregation.

In this example, we leave the scope and level of aggregation to default.

There are other such functions provided in Report Studio (for example, Running-maximum, Running-Count, Running-Total, and so on) which are useful in real life scenarios.

Showing negative values in red and brackets

MS Excel has traditionally been the most popular and widely-used tool for information access. It is easy to use and gives enough power for the business users to do their analysis. It readily allows you to display negative numbers in red and brackets, which is a popular choice in the finance world.

However, under the Data Format options of Report Studio, you can only choose to display the negative numbers in brackets. You cannot specify to show them in different colors. Hence, we have to create a conditional variable here and define the foreground color accordingly.

There was this big limitation in Report Studio up to version 8.2. Imagine that you have 15 numerical measures to be formatted in a similar way. In such case, you need to create 15 conditional variables and assign them to each column. This problem is solved with a new feature introduced in version 8.3 onwards. Please refer to the next recipe for this.

主站蜘蛛池模板: 洛南县| 丰台区| 临漳县| 积石山| 西青区| 扶绥县| 游戏| 介休市| 临颍县| 黄平县| 贵定县| 柘城县| 黑山县| 兴隆县| 新晃| 黔东| 九江市| 乐业县| 邵阳县| 横峰县| 澳门| 隆德县| 麻城市| 原阳县| 施秉县| 扎兰屯市| 太仓市| 霞浦县| 东明县| 花莲县| 河间市| 六枝特区| 招远市| 阜平县| 板桥市| 东方市| 抚远县| 舟曲县| 随州市| 株洲市| 昌黎县|