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.
cast([Sales (query)].[Time (ship date)].[Day key (ship date)], date)
.[Unit price]/[Unit cost]
.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:
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.