Making Date Based Reports Sizzle

One of the most effective ways to better understand an organization to visualize events over time to see how they are trending and how they compare to one another. Here's an example that illustrates a multi-year comparison. This could be used to tell a story of what has happened and help decide a plan of action.

Orders trends from Northwind sample database

The Challenges Reporting From Existing Databases

Most databases are optimized for quickly saving and retrieving individual records that drive an application. This can create some challenges when it comes to reporting in a way that delivers insight into the data. Now there are many complex technologies like ETL data warehousing and analysis cubes that try to solve this problem, but often require far more effort and overhead than it's worth. Many of the things these complex tools do can actually be done with a simple view.

Often you may have the same type of information, such as events, scattered across multiple fields and even multiple tables. This makes it difficult create good looking reports, charts and pivots. The solution is to combine these different data sources into one. This post covers a simple way to do just that.

Creating Views

A view is essentially a virtual table that's based on a query. We can easily create these with the following statement.

Create View Events AS
Select * From Table

The power of UNION

The JOIN statement is intended to link databases when the objects are related. Unfortunately this does not work when you have unrelated events. Instead, you can use UNION to combine them together, but to do so you must alias your fields into a common schema.

Let's say we want to create a single view off all events. We'll use the Northwind database as our sample and combine order dates, ship dates and birthdays into a single view.

SELECT OrderDate AS Date, 'Order' AS Event, ShipCountry AS Country FROM Orders UNION
SELECT ShippedDate AS Date, 'Shipment' AS Event, ShipCountry AS Country FROM Orders UNION
SELECT HireDate AS Date, 'Hire' AS Event, Country AS Country FROM Employees

Breaking raw dates into components with DATEPART()

While dates are stored as linear values, they are often more useful when thought of categorically and broken down into subcomponents like months and years. This is especially important for filtering and pivoting.

To get the year an event took place in, we would do this.

SELECT DatePart(m,Date)

Multi-Level Views

As the union statement is rather complex on it's own, adding date part of each part of the statements could get tedious. Instead we will put an outer query around our existing query to add the date subcomponents to it.

SELECT DatePart(m,Date) as Month, DatePart(yyyy,Date) as YEAR, * FROM ( SELECT OrderDate AS Date, 'Order' AS Event, ShipCountry AS Country FROM Orders UNION SELECT ShippedDate AS Date, 'Shipment' AS Event, ShipCountry AS Country FROM Orders UNION SELECT HireDate AS Date, 'Hire' AS Event, Country AS Country FROM Employees ) AS IQ

This view adds the Month and Year to our view.

Using CASE Statements to support friendlier categorical values

While people are used to thinking in terms of calendar months, most databases are completely unaware of the month names. One way to add these names to your data is to setup a case that translates your date to the name of the calendar month. Finally, we can use reporting tools like excel or Izenda Reports generate a nice looking printable report.

SELECT
CASE DatePart(m, HireDate)
WHEN '1' THEN 'Jan'
WHEN '2' THEN 'Feb'
WHEN '3' THEN 'Mar'
WHEN '4' THEN 'Apr'
WHEN '5' THEN 'May'
WHEN '6' THEN 'Jun'
WHEN '7' THEN 'Jul'
WHEN '8' THEN 'Aug'
WHEN '9' THEN 'Sep'
WHEN '10' THEN 'Oct'
WHEN '11' THEN 'Nov'
WHEN '12' THEN 'Dec'
END AS MonthName
FROM Employees