Formatting negative values
Business owners need to see the sales figures by month 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 / Year, Time / Month (numeric), and Sales fact / Quantity as columns.
Group Year and sort Month (numeric) in ascending order.
How to do it...
Showing negative values in red and between brackets is a familiar practice for analysts in tools like Excel. Here are the steps to create the exact same formatting in your reports:
- 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 the Property list.
- Set the Format type as Number and the Negative Sign Symbol as brackets () as shown in the following screenshot:
- Now go to Condition Explorer and create a new condition variable of Boolean type. Define the condition as
[Query1].[Running Difference] < 0as shown in the following screenshot:
- Call the variable as
Show_Red. - Now go back to the report page and select the Running Difference column. Assign the
Show_Redvariable as Style Variable from the property list. - Choose the Yes condition for
Show_Redfrom the conditional explorer. Select the Running Difference column from the list and open its Font properties. - Set the font foreground color to red as shown in the following screenshot:
- Click on the OK button. Double-click on the green bar to come out of condition. Run the report to test it as shown in the following screenshot:
How it works...
One purpose of this recipe is to introduce you to the powerful aggregation functions provided by Cognos.
Running Difference
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.