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

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.