1. Producing executive reports
Now that you know how to format a date, rank rows, and pivot, you can produce an executive report. Before doing that, though, let's go over some general report guidelines.
2. Readability
Reports are often read by managers and C-level executives, so readability is crucial in producing good reports. Here are some general readability guidelines when producing a report.
With dates, use a human-readable formats, not the default format. Figure out a format string that works for your company's standards and use it in TO_CHAR throughout your reports.
With values, round them to the second decimal at most. Long strings of numbers are distracting. Only keep decimal places if they're relevant in a report.
When your table is "long", meaning that it has many rows and few columns, see if you can reshape your table into a wide table, a table with more columns and fewer rows. When possible, select the date column as the pivot column, since scanning by dates horizontally becomes very easy. Make sure the date column is sorted so the new columns are too!
Finally, don't forget to sort! Unsorted data is harder to scan. Make sure to pick which columns to sort by sensibly.
3. Executive report - query
Now, let's build an executive report. This one's gonna be the ranking of Delivr's partner eateries by their count of orders in the last three months of 2018.
The first step is to get a table of the counts of orders by eatery and month. Each meal's eatery is in the meals table, which is joined to the orders table to get the count of orders for each eatery.
The format string in TO_CHAR formats the dates as the month number, the month's abbreviated name, and the year. The month number is included to preserve the sort order of the months.
Here are the first couple of rows that result from this query.
4. Executive report (II)
The second step is to assign ranks to each eatery based on its count of orders. The previous query is wrapped in a CTE, then the eatery and the month are selected. Then, RANK is used, ordering by the count of orders and partitioning by month.
Partitioning by month resets the ranking for every month, whereas ordering by the count of orders in descending order guarantees that the top ranks are given to the highest-order eateries.
The rank is cast to int to avoid a type mismatch error when using CROSSTAB.
5. Executive report (III)
Finally, wrap the previous query with the double dollar signs between CROSSTAB's parentheses. The ellipsis is for formatting purposes, but replace it with the previous query in your mind.
Then, pivot the table using CROSSTAB. Since you're pivoting by month here, select the last 3 months, being careful to name them according to what you formatted them.
Don't forget to enable CROSSTAB by creating (or importing) the tablefunc extension. Without it, calling CROSSTAB will throw an error.
6. Executive report - result
This is the result, a ranking of eateries by the count of orders they've received. The lower the rank, the higher the count of orders -- rank 1 goes to the eatery with the highest count, and so on.
It's clean and easy to interpret - the Moon Wok is dominant; Life of Pie is lagging behind, while the others vie for the middle ranks. It could even be passed to a visualization tool to produce a bump chart.
7. Producing executive reports
With that, you've completed the course. Practice producing executive reports, then head to the course recap video to take stock of what you've learned.