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.
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.
- Add a new query calculation to the list. Define the expression as:
running-difference([Quantity])Call this item as "Running Difference".
- Open the Data Format properties for this calculation from Property list.
- Set the Format type as Number and the Negative Sign Symbol as brackets ().
- Now go to Condition Explorer and create a new condition variable of Boolean type. Define the condition as:
[Query1].[Running Difference] < 0
- Call the variable as
Show_Red. - Now go back to report page and select the Running Difference column. Assign
Show_Redvariable as Style Variable from the property list. - Choose the Yes condition for
Show_Redfrom the conditional explorer. Select 'Running Difference' column from the list and open its Font properties. - Set the font foreground color to red.
- Click the OK button. Double click on the green bar to come out of condition. Run the report to test.
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.
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.