Wednesday, September 2, 2009

SSRS 2005 Custom functions show #error

I created a custom function to convert UTC date times to the Local DateTime. When doing this I found that the if the field was nothing the textbox expression returned #error.

example expressions that produced error in text box:

Orginal Code with Error:
=Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value)

Tried checking if it is a Date first:
=IIF(IsDate(Fields!completed_dttm.Value), Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value), nothing)

Tried checking if the value was nothing and using string values for each output:
=IIF(IsNothing(Fields!completed_dttm.Value), "", Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value.ToString())

I looked for solutions on the internet but didn't find my exact issue.

So, I decided to just try another approach and I within the DataSet, I Added a new Field by right clicking on the dataset and choosing Add...

Name the field, choose Calclulated and enter the original expression from above:
=Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value)

Deploying a custom assembly: http://msdn.microsoft.com/en-us/library/ms155034%28v=sql.105%29.aspx
Referencing custom assemblies: http://msdn.microsoft.com/en-us/library/ms155974%28v=sql.105%29.aspx
and: http://msdn.microsoft.com/en-us/library/ms159238%28v=sql.105%29.aspx#Custom

Custom ReportFunctions Class:

using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;

namespace RWF.Report.Custom
{
    public class ReportFunctions
    {
        ///


        /// enum of Time Zone Names
        ///

        public enum TimeZoneName
        {
            Pacific = 1,
            Eastern,
            Central,
            Mountain,
            Arizona
        }
        ///
        /// used to translate to local time zones
        ///

        /// Short Name of time Zone: Pacific, Mountain, Arizona, Central, Eastern
        /// UTC Date/Time
        /// Local Date/Time
        public DateTime ToLocalTime(TimeZoneName timeZone, DateTime UtcDateTime)
        {

            switch (timeZone)
            {
                case TimeZoneName.Pacific:
                    return ToLocalTimeFromOffset(UtcDateTime, -8.0, true);
                case TimeZoneName.Eastern:
                    return ToLocalTimeFromOffset(UtcDateTime, -5.0, true);
                case TimeZoneName.Central:
                    return ToLocalTimeFromOffset(UtcDateTime, -6.0, true);
                case TimeZoneName.Mountain:
                    return ToLocalTimeFromOffset(UtcDateTime, -7.0, true);
                case TimeZoneName.Arizona:
                    return ToLocalTimeFromOffset(UtcDateTime, -7.0, false);
                default:
                    return ToLocalTimeFromOffset(UtcDateTime, -8.0, true);
            }
        }

        ///
        /// used to translate Local Time to UTC Date Time.
        ///

        /// Short Name of time Zone: Pacific, Mountain, Arizona, Central, Eastern
        /// Local Date Time
        /// UTC Date Time
        public DateTime ToUtcTime(TimeZoneName timeZone, DateTime LocalDateTime)
        {

            switch (timeZone)
            {
                case TimeZoneName.Pacific:
                    return ToUtcTimeFromOffset(LocalDateTime, -8.0, true);
                case TimeZoneName.Eastern:
                    return ToUtcTimeFromOffset(LocalDateTime, -5.0, true);
                case TimeZoneName.Central:
                    return ToUtcTimeFromOffset(LocalDateTime, -6.0, true);
                case TimeZoneName.Mountain:
                    return ToUtcTimeFromOffset(LocalDateTime, -7.0, true);
                case TimeZoneName.Arizona:
                    return ToUtcTimeFromOffset(LocalDateTime, -7.0, false);
                default:
                    return ToUtcTimeFromOffset(LocalDateTime, -8.0, true);
            }
        }

        ///
        /// Convert Date/Time to UTC from Local Date/Time, given the offset and observance of daylight savings time
        ///

        /// Local Date Time
        /// UTC Offset for time zone
        /// Time zone observers daylight savings time (DST)
        ///
        public DateTime ToLocalTimeFromOffset(DateTime UtcDateTime, Double Offset, bool ObservesDST)
        {
            DateTime LocalDateTime;
            bool bDateIsInDST;
            LocalDateTime = UtcDateTime.AddHours(Offset);

            // add 1 to offset if the date is in daylight savings time and it is observed
            if (ObservesDST)
            {
                bDateIsInDST = IsInDaylightSavingsTime(LocalDateTime);
                if (bDateIsInDST)
                    LocalDateTime = LocalDateTime.AddHours(1.0);
            }


            return LocalDateTime;
        }

        ///
        /// Convert Date/Time to UTC from Local Date/Time, given the offset and observance of daylight savings time
        ///

        /// Local Date Time
        /// UTC Offset for time zone
        /// Time zone observers daylight savings time (DST)
        ///
 
       public DateTime ToUtcTimeFromOffset(DateTime LocalDateTime, Double Offset, bool ObservesDST)
        {
            DateTime UtcDateTime;
            bool bDateIsInDST;

            // add 1 to offset if the date is in daylight savings time and it is observed
            if (ObservesDST)
            {
                bDateIsInDST = IsInDaylightSavingsTime(LocalDateTime);
                if (bDateIsInDST)
                    Offset = Offset + 1.0;
            }

            UtcDateTime = LocalDateTime.AddHours(-Offset);

            return UtcDateTime;
        }


        ///
        /// Returns true if the date falls within DaylightSavingsTime
        ///

        /// Datetime to check
        /// true/false
        public bool IsInDaylightSavingsTime(DateTime dateTime)
        {
            int year = dateTime.Year;
            DateTime startDate = GetDaylightBeginDate(year);
            DateTime endDate = GetDaylightEndDate(year);

            //TimeSpan delta = new TimeSpan(startDate.Ticks - endDate.Ticks);
            //DaylightTime daylightTime = new DaylightTime(startDate, endDate, delta);
            bool isDST;

            //isDST = TimeZone.IsDaylightSavingTime(dateTime, daylightTime);

            if ((dateTime > startDate) && (dateTime < endDate))
                isDST = true;
            else
                isDST = false;

            return isDST;
        }

        private DateTime GetDaylightEndDate(int year)
        {
            if (year < 2007)
            {
                //last Sunday in October
                return GetNextSunday(new DateTime(year, 10, 25, 2, 0, 0));
            }
            else
            {
                //first Sunday in November
                return GetNextSunday(new DateTime(year, 11, 1, 2, 0, 0));
            }
        }

        private DateTime GetDaylightBeginDate(int year)
        {
            if (year < 2007)
            {
                //first Sunday in April
                return GetNextSunday(new DateTime(year, 4, 1, 2, 0, 0));
            }
            else
            {
                //second Sunday in March
                return GetNextSunday(new DateTime(year, 3, 8, 2, 0, 0));
            }
        }

        private DateTime GetNextSunday(DateTime dt)
        {
            double dow = Convert.ToDouble(dt.DayOfWeek);
            if (dow == 0)
            {
                return dt;
            }
            else
            {
                return dt.AddDays(7.0 - dow);
            }
        }

        public DateTime GetUtcDate()
        {
            return DateTime.UtcNow;
        }
    }
}