Virtually all the 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 percentage.
Create a simple list report with Product | Product line, Product | Product type and Time (Ship date) | Day key (Ship date) as columns from the Sales (query) namespace.
Also add Quantity, Unit Price, and Unit Cost from the Sales Fact Query Subject.
Create grouping on Product line and Product type.
We will start by adding a new calculation to convert the Shipment Day Key into a date and show in MONTH-YEAR format. So, add a new Query Calculation to the report from the toolbox.
Define the calculation as:
cast([Sales (query)].[Time (ship date)].[Day key (ship date)], date).Select this new column for the calculation and open Data Format from Properties pane. Open the Data Format dialog by clicking on the browse button next to Data Format property.
Choose the format type Date, set Date Style to Medium, and set Display Days to No.
Now select the Quantity column on the report. Choose Data Format from property and open the dialog again.
This time pick 'Number' as the type and set 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 report. For that, add a new query calculation and define it as:
[Unit price]/[Unit cost].Select this column and from Data Format property dialog, set it as Percent. Choose % as the percent 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 using the CAST function to convert a number to date. Also, we are checking how dates can be formatted to hide certain details (for example, days) and how to change the separator. Finally, we have tested formatting options for numbers and percentage.
The CAST function is used to convert data from one format to another. We specify the target format in second argument. Here, we are converting to date. It converts the date key which is in YYYYMMDD format to a date.
Later, we are setting the data format for this column as date for display purpose. We have set the display days to No as we only want to display MONTH-YEAR.
This is straightforward. The quantity column is displayed with two decimal points and negative numbers are displayed in brackets as this is what we have set the data formatting to.
Please note that ideally the warehouse stores a calendar table with a 'Date' type of field , or this is made available through Framework Model. So, we need not cast the key to date every time. However, this recipe is meant to show you the technique and introduce you to the casting function.
Also, we are assuming here that business needs to see the shipment month. So, they want to see MONTH YEAR format only and we are hiding the days.
Another way of achieving this is explained as follows:
Let us change the expression for Shipment Day Key column to this: [Sales (query)].[Time (ship date)].[Day key (ship date)]/10000
Now set the Data Format to Number, with the following options:
No of decimal places = 2
Decimal separator = hyphen (-)
Use thousand separator = 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 month part by a hyphen. This is not truly converted to MONTH YEAR but conveys the information.
The advantage here is that the numerical operation is faster than CAST to DATE. We can use similar techniques to cosmetically achieve the required result.