Print

Returned a data type that is not valid

Thought I'd put a note on this error. Bearing in mind that this is a general data type error and not just because I tried to convert a date in SQL format to a Month name.
copyraw 
An error occurred during local report processing.
An error has occurred during report processing.
The Group expression used in grouping 'table1_month' returned a data type that is not valid.
  1.  An error occurred during local report processing. 
  2.  An error has occurred during report processing. 
  3.  The Group expression used in grouping 'table1_month' returned a data type that is not valid. 
My DataSet Query was as follows:
copyraw 
SELECT
	CONCAT(MONTHNAME(t1.Date), ' ', YEAR(t1.Date)) Month,
	SUM(IF(t1.VisitorBrowser='MSIE', 1, 0)) 'IE',
	SUM(IF(t1.VisitorBrowser='Firefox', 1, 0)) 'FF',
	SUM(IF(t1.VisitorBrowser='Safari', 1, 0)) 'CS',
	SUM(IF(t1.VisitorBrowser='Smartphone', 1, 0)) 'Smart',
	SUM(IF(t1.VisitorBrowser='Other', 1, 0)) 'Other',
	COUNT(t1.VisitorBrowser) 'Total'
FROM
(SELECT
	a.DateTimeStamp Date,
	CASE
		WHEN INSTR(a.VisitorUAgent, 'MSIE ') THEN 'MSIE'
		WHEN INSTR(a.VisitorUAgent, 'Firefox') THEN 'Firefox'
		WHEN INSTR(a.VisitorUAgent, 'Safari') THEN 'Safari'
		WHEN INSTR(a.VisitorUAgent, 'Chrome') THEN 'Safari'
		WHEN INSTR(a.VisitorUAgent, 'BlackBerry') THEN 'Smartphone'
		WHEN INSTR(a.VisitorUAgent, 'iPhone') THEN 'Smartphone'
		WHEN INSTR(a.VisitorUAgent, 'Android') THEN 'Smartphone'
		WHEN INSTR(a.VisitorUAgent, 'NetFront') THEN 'Smartphone'
		WHEN INSTR(a.VisitorUAgent, 'SonyEricsson') THEN 'Smartphone'
		ELSE 'Other'
	END VisitorBrowser
FROM
	custombu_stats_visits a
GROUP BY
	a.VisitorIP
ORDER BY
	a.DateTimeStamp DESC) t1
GROUP BY
	CONCAT(MONTH(t1.Date), ' ', YEAR(t1.Date))
  1.  SELECT 
  2.      CONCAT(MONTHNAME(t1.Date), ' ', YEAR(t1.Date)) Month, 
  3.      SUM(IF(t1.VisitorBrowser='MSIE', 1, 0)) 'IE', 
  4.      SUM(IF(t1.VisitorBrowser='Firefox', 1, 0)) 'FF', 
  5.      SUM(IF(t1.VisitorBrowser='Safari', 1, 0)) 'CS', 
  6.      SUM(IF(t1.VisitorBrowser='Smartphone', 1, 0)) 'Smart', 
  7.      SUM(IF(t1.VisitorBrowser='Other', 1, 0)) 'Other', 
  8.      COUNT(t1.VisitorBrowser) 'Total' 
  9.  FROM 
  10.  (SELECT 
  11.      a.DateTimeStamp Date, 
  12.      CASE 
  13.          WHEN INSTR(a.VisitorUAgent, 'MSIE ') THEN 'MSIE' 
  14.          WHEN INSTR(a.VisitorUAgent, 'Firefox') THEN 'Firefox' 
  15.          WHEN INSTR(a.VisitorUAgent, 'Safari') THEN 'Safari' 
  16.          WHEN INSTR(a.VisitorUAgent, 'Chrome') THEN 'Safari' 
  17.          WHEN INSTR(a.VisitorUAgent, 'BlackBerry') THEN 'Smartphone' 
  18.          WHEN INSTR(a.VisitorUAgent, 'iPhone') THEN 'Smartphone' 
  19.          WHEN INSTR(a.VisitorUAgent, 'Android') THEN 'Smartphone' 
  20.          WHEN INSTR(a.VisitorUAgent, 'NetFront') THEN 'Smartphone' 
  21.          WHEN INSTR(a.VisitorUAgent, 'SonyEricsson') THEN 'Smartphone' 
  22.          ELSE 'Other' 
  23.      END VisitorBrowser 
  24.  FROM 
  25.      custombu_stats_visits a 
  26.  GROUP BY 
  27.      a.VisitorIP 
  28.  ORDER BY 
  29.      a.DateTimeStamp DESC) t1 
  30.  GROUP BY 
  31.      CONCAT(MONTH(t1.Date), ' ', YEAR(t1.Date)) 
Caused by SQL statement returning first column date as
copyraw 
CONCAT(MONTHNAME(t1.Date), ' ', YEAR(t1.Date)) AS Month
  1.  CONCAT(MONTHNAME(t1.Date), ' ', YEAR(t1.Date)) AS Month 
Solved by changing SQL statement to
copyraw 
t1.Date AS Month
  1.  t1.Date AS Month 

This will return the SQL date but using your report builder or business intelligence development studio, you can specify in the report designer how to format the returned value to a more user-friendly date format (such as Month name then year - eg. November 2010).

I have been told that data types are a nightmare in SQL Server Reporting Services and that all data types should be kept in their original state and then queried in the same state.

Category: Transact-SQL :: Article: 313