Izenda's
Reporting Techniques
Version 1.0
|
How
To Have Great Dates... In Your Database
|
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
|
|
|
|
Did
You Know?
|
|
Izenda
Reports can improve time-to-market by over a year?
|
|
|
