Virtually all reports involve displaying numerical information. It is very important to correctly format the numbers. In this recipe, we will create a report which formats dates, numbers, and percentages.
Date transformation and formatting are important in business reports. We will see two ways of displaying MONTH-YEAR from the Shipment Date Key. We will apply some formatting to a numeric column and will also configure a ratio to be displayed as a percentage.
Create a simple list report with Products / Product line, Products / Product type, and Time (ship date) / Date (ship date) as columns from the Sales (query) namespace.
Also, add Quantity , Unit price, and Unit cost from the Sales fact Query Subject.
Create a grouping on Product line and Product type.
In this recipe, we will check how to apply different formats on the data items.
We will start by formatting the date column we have (check in Cognos 8).
Select the Time (ship date) / Date (ship date) column and open Data Format from the Properties pane. Open the Data Format dialog box by clicking on the Browse button next to the Data Format property.
Choose the format type Date, set Date Style to Medium, and set Display Days to No, as shown in the following screenshot:
Now select the Quantity column in the report. Choose Data Format from property and open the dialog box again.
This time select Number as the type and set the different properties as required. In our example recipe, we will set the Number of Decimal Points to 2 and use brackets () as a Negative Sign Symbol.
Finally, we will add the ratio calculation to the report. For that, add a new query calculation and define it as follows:
[Unit price]/[Unit cost]
Select this column and from the Data Format property dialog box, set it as Percent. Choose % as the Percentage Symbol and set the Number of Decimal Places to 2. Also, set the Divide by Zero Characters to N/A.
Run the report to test it.
In this recipe, we are trying multiple techniques. We are checking how dates can be formatted to hide certain details (for example, days) and how to change the separator. Also, we have tested formatting options for numbers and the percentage.
Here, we started by setting the data format for the Month Year column as date for display purposes. We have set the display days to No as we only want to display MONTH-YEAR.
Please note that ideally the warehouse stores a calendar table with a Date type of field; this is made available through the Framework model. Also, we are assuming here that you need to see the shipment month. So, you want to see the MONTH-YEAR format only and we are hiding the days.
Using the data format options, you can do a lot of things. Assume that you don't have a date field in your data source but instead you have just a date key and you want to display the year and month as we did in our recipe. For that, create a new query calculation and use the following expression:
[Sales (query)].[Time (ship date)].[Day key (ship date)]/10000
Now set the Data Format to Number with the following options:
Set the No of decimal places field to 2
Set the Decimal separator to -
Set Use thousand separator to No
Run the report to examine the output. You will see that we have gotten rid of the last two digits from the day key and the year part is separated from the month part by a hyphen. This is not truly converted to MONTH-YEAR, but conveys the information as shown in the following screenshot:
The advantage here is that the numerical operation is faster than converting the numerical key to DATE. We can use similar techniques to cosmetically achieve the required result.