0 comments
Business Intelligence 
ReportBuilder makes it possible to create ad-hoc reports. A build-in calendar can be used to create a date parameter. When we look at the SQL that is created by de ReportBuilder engine, the date used will be converted to the UTC time like : CONVERT(DATETIME, CONVERT(CHAR(10), [Date].[Date], 102), 102) = CONVERT(DATETIME,‘2010-01-01 00:00:00.000’, 121). Because of the convert of the date, no index will be used.
However the end-user can have influence on the SQL that is created. By default the DATEONLY([Date]) = parameter:date is created. The DATEONLY() function creates the CONVERT(). When the DATAONLY() function is deleted [Date] = parameter:date, the result will be [Date].[Date] = CONVERT(DATETIME,‘2010-01-01 00:00:00.000’, 121). Now the index is used.