The many faces of Datetime

Image via Wikipedia

As report writing professionals we, on a daily basis, have Ugo Boncompagni aka Pope Gregory XIII  to thank for a consistent and repeating annoyance.  Popes over the years have been attributed to bringing many weird and wonderful revelations to the world, this one in particular landed us with our calendar.  Good old Ugo.

Given that it has been knocking around since 1582, it's not doing too bad a job.  In fact it behaved itself for around 388 years, which is when things went wrong.  Fast forward to the beginning of the 1970s, IBM, no doubt binding dark forces together, developed a new computer language called SQL (known as SEQUEL at the time - but had to change it to avoid trademark infringement).

As Mr Boncompagni's calendar was slowly adopted across the globe, there were some countries who just had to be different.  You know the type of countries - those who never tucked their school shirts in and wore their tie all wrong.  So today, although we are using the same calendar, we are left with a dozen ways to write a date depending on where you are stood.

'So - what has that got to do with me?' I hear you asking.

If you are working on one or more databases that handle dates which only handles data relevant for your own country, and the database environment has been installed with all date related default settings aligned correctly then you should (mostly) be fine and don't need to read further. However, if you find yourselves working on international data then you may find something of interest.

Randomly picking a date from the calendar, say 5th April, you can write it:

Type	Format
Long Date	5th, April 2010
Short Date	5th Apr 2010
British/French	5/4/2010
U.S.	4/5/2010
Japan	10/4/5

And a number of other ways also.

As you can see - a date can quickly lack any meaning depending on how it is presented.  As a preference I prefer using the long date, as it removes any question of doubt (defaulting to the short date if space is an issue).  However, opinions differ and more often than not you will need to present it as dd/mm/yy or yy/mm/dd etc.

In the aforementioned SQL, since MS SQL 2000, you are able to use the CONVERT function to handle your date formatting.

SYNTAX
======
CONVERT( {data_type} [ (length) ] , expression [ ,style ] )
ie
SELECT CONVERT(nvarchar(20) ,getdate() ,103)

The style argument is the one of interest to us.  The resultant data_type will have the date formatted as dictated by the style.  Using this function is a rather effective way to remove the time part from a datatime.

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100 (1,2)	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yyyy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106(1)	-	dd mon yy
7	107(1)	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 or 109 (1,2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 or 113 (1,2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
-	130 (1,2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM

There is also a CAST function, however you can not handle styles with it - so is not much use here.

So, say you have a datetime

SELECT CONVERT(datetime,'2010-06-17 01:23:45.000') as myDate

Gives you:

myDate
2010-06-17 01:23:45.000

However, you don't want the time and you want it in, say British/French format

SELECT CONVERT(nvarchar(20),CONVERT(datetime,'2010-06-17 00:00:00.000'),103) as myDate

Gives you:

myDate
17/06/2010

This proves particularly useful if you need to summarise by a date and have several times that need to be grouped together.

Another way to achieve the removal of a timestamp from datetime is :

select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

which is also a popular and fast way of doing it.

Sing a Song of Synonym

With the risk of sounding too much like a Wikipedia entry - 'Synonyms' are words who have identical or very similar meanings with different words.  And what does that have to do with a budding Crystal Report writer?  Portability - that's what.  Setting up a synonym in a database is like instead of pointing at a table or a stored procedure you set up a pointer A -> B, (where B is the table/SP) however in another database you can point the same thing elsewhere ie A -> C.  As long as both table/stored procedure returns the required fields everything will be fine.

Supposing you are part of a development environment and have the luxury of multiple state instances (ie Development, Test, UAT, Live etc) in MS SQL 2005 or higher.  You would have therefore have experience of moving reports from one environment to another.

Now, on occasion (and it does happen, believe me) you will need the report to point to different tables/store procedures within the local database depending on the environment.  For example, on the test and development environments you require locally held table data, whereas on the live box you have an independant replicated reporting server and you need to point to that instead.

Here enters the humble synonym.  It creates a very handy abstraction layer.

So in the above example 'syn_DATA' points to [SERVER1].[DEV].[dbo].[DATA] on the dev box, however on the live box [SERVER3].[LIVE].[dbo] it would point to [REPORTS].[LIVE].[dbo].[DATA].  All of this is set at SQL Server level, so is transparent to the reports themselves.

This means, from a Crystal Reports point of view, that all you will need to do, when moving from one environment to another, is just to re-point the server and the job's a good 'n.

Provided that the Synonyms are set up on the database first Crystal Reports will be able to select them from the GUI.

I won't go into setting up a Synonym as there are reams of pages out there already covering that particular topic.

Crosstab Running Off The Page

If you've started down the path of using the powerful, yet annoyingly limited power of the crosstab objects and are using subreports then you have no doubt ran head first into data getting orphaned off onto imaginary pages to the right (unless you are returning small amounts of data).

A bit like this:

The easiest way I found to defeat this particular beasty is to get the sql to work out columns and sections that the data belongs to using the ROWCOUNT() OVER function (new in SQL Server 2005). Once you've nailed this you can group by section numbers and voila - no more overrunning.

An example I here you cry!  

Let us imagine a database with sample testing information.  We select one at random, let's call this Dougal.  Dougal has 248 records.

select 
s.original_sample
, s.status
, s.sample_number
from 
sample s
where 
s.project = 'DOUGAL'
order by
s.original_sample
, s.status

This gives us:

Now let us work out the row count for each record.

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
from
sample s
inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
from
sample s1
where s1.project = 'DOUGAL' group by s1.original_sample ) as A on s.original_sample = A.original_sample
where s.project = 'DOUGAL'
order by s.original_sample , s.status

Which now produces :

Now we can work out which column each of the items will go in for each section (not really needed here - but may come in handy elsewhere).

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
, a.myColCount
from
sample s
inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) - 10 *((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as myColCount
from
sample s1
where
s1.project = 'DOUGAL'
group by
s1.original_sample
) as A on s.original_sample = A.original_sample
where
s.project = 'DOUGAL'
order by
s.original_sample
, s.status

Giving:

And finally we now derive the section numbers

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
, a.myColCount
, a.mySecCount
from
sample s inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) - 10 *((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as myColCount
, ((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as mySecCount
from
sample s1
where
s1.project = 'DOUGAL'
group by s1.original_sample ) as A on s.original_sample = A.original_sample
where s.project = 'DOUGAL'
order by s.original_sample , s.status

Giving us:

The rest is plain sailing from here on in.  Open up the Group Expert in the Reports menu and then set up 'SecCount' (or whatever you have called your sections) as a group.

Now add your crosstab to the footer of this group and with a minimal amount of tweaking you should have something like:

A nicely wrapping crosstab set to 10 columns.  Of course changing the figure changes the number of columns displayed, so you have flexibility over font, size and layouts.


Hope this helps someone!