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: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience 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 bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.