Thursday, September 4, 2014

Dealing with 1/1/1900 with GP and SQL Reports

If you have created reports with SQL Reporting Services for GP, you have inevitably encountered 1/1/1900 showing up on your reports.  Maybe as the inactive date for an employee?  Or the due date on a  payables transaction?

In some cases, you may not mind that the dates display that way.  However, eventually, a client will ask...why do those dates show that way?  Can you hide them if they are blank?  Well, sadly, they aren't actually blank so it's not that easy.  Right?
But is actually pretty easy using an expression for the field, like this...

=IIF(Fields!THISISYOURDATE.value="1/1/1900 12:00:00AM","",Fields!THISISYOURDATE.Value)

So, in this example when my field THISISYOURDATE is 1/1/1900 the field will display as blank, otherwise it will show the date.

Easy easy :)

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

No comments: