Dates issue in Microsoft Dynamics CRM 4.0 Report

Dates issue in Microsoft Dynamics CRM 4.0 Report

I came across a problem today with a report I am writing in Microsoft Dynamics CRM using Report Builder 2.0

In my report I am taking user submitted Parameter of data type Date/Time and searching for transactions in CRM which are between the two dates specified using the following query

Declare @start datetime, @end datetime

Set @start = '2011-03-30'

Set @end = '2011-03-30'

SELECT

@start as StartDate ,@end as EndDate,

FilteredAp_transaction.ap_date,

FilteredContact.lastname,

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

FROM         FilteredAp_transaction INNER JOIN

FilteredContact ON FilteredAp_transaction.ap_contactnameid = FilteredContact.contactid

where

FilteredAp_transaction.ap_date

between @start and @end

What I found was that the report was not bringing in the expected results

In SQL dates are stored as 2011-01-13 10:08:20.000 in the format YYYYMMDD hh:mm:ss[.mmm]

Example

In the interest of simplicity let’s assume we are looking for all transactions on the same day

@Start = 2011/03/30

@End = 2011/03/30

Running the following Query gives 8 results.  However I knew this was incorrect.

After some investigation I realised it was the time portion of the Date/Time data type that was causing issues.

By adding the following line (Thank you www.bennadel.com)

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

I now have 9 results.  The reason for this is because the variables in the report are of type, Date/Time.  As well as the query comparing the date, it is also comparing the time value.  As I had a time value of 15:49:33.000 this was not being included in the results.  The above CAST statement strips the time value and replaces it with 00:00:00.000

We now go from a value of 2011-03-30 15:49:33.000 to 2011-03-30 00:00:00.000

Declare @start datetime, @end datetime

Set @start = '2011-03-30'

Set @end = '2011-03-30'

SELECT

@start as StartDate ,@end as EndDate,

FilteredAp_transaction.ap_date,

FilteredContact.lastname,

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

FROM         FilteredAp_transaction INNER JOIN

FilteredContact ON FilteredAp_transaction.ap_contactnameid = FilteredContact.contactid

where

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

between @start and @end

Adding a Null value in SQL / Report Expression

When adding two fields together in a Microsoft Dynamics CRM 4.0 SSRS Report I was getting an issue where if one of the values being added was NULL, I would get the message #Error with the Matrix.

Below is an example of the Expression used within my Report.  In tests this worked fine when both fields had values, but if just one field was NULL the row would not calculate

=Fields!ap_taxtobeclaimed.Value + Fields!ap_taxclaimed.Value

To resolve this issue required me to use a SQL Function called COALESCE()

COALESCE() can be used to take an expression from SQL  and a value to use in the event that the values from the expression are NULL

COALESCE ( expression [ ,...n ] )

SELECT

(COALESCE(FilteredAp_transaction.ap_taxtobeclaimed,0) + COALESCE(FilteredAp_transaction.ap_taxclaimed,0)) AS TaxSum

,FilteredAp_transaction.ap_taxtobeclaimed

,FilteredAp_transaction.ap_taxclaimed

,year(FilteredAp_transaction.ap_date) as year

FROM

FilteredAp_transaction inner join

FilteredContact AS CRMAF_FilteredContact

on CRMAF_FilteredContact.contactid = FilteredAp_transaction.ap_contactnameid

Using the Coalesce expression I was able to display the correct Tax calculation per year