I'm having trouble implementing what I feel should be a fairly basic report in SSRS (SQL Server 2014 and VS2013 Shell). I work for a company based in the UK and I want to show various data using the Maps functionality. To start with I simply want to take customer sales data and plot it over a basic UK map. To that end I have constructed a query which uses the customer postcode to obtain latitude and longitude values. I have then used STPointFromText to convert these to the Geography data type as follows:
geography::STPointFromText('POINT(' + CAST(longitude AS VARCHAR(20)) + ' ' +
CAST(latitude AS VARCHAR(20)) + ')', 4326) AS Geog
This dataset contains the sales figures, so the analytical and spatial data is all in one place (assuming I've understood this part correctly!).
When I query this data in SSMS and view the results under the Spatial tab, it looks quite promising, i.e. quite clearly plots the shape of the UK:
However, when I try to represent this in SSRS, it falls apart. Using the SQL Server spatial query option as my data source, and using the query I constructed and ran in SSMS previously, I keep getting a popup window which reads:
'Unable to determine the spatial data type in the specified dataset field: Geog'
From here nothing works. I don't see any maps or any points like I did in SSRS. I've been searching for hours and not really found a solution, although I did find this on MSDN:
https://msdn.microsoft.com/en-us/library/hh272532.aspx
'You cannot use the dataset designer in Visual Studio for queries that return SQL Server spatial types. The dataset designer does not support user-defined types (UDTs), to which category the SQL Server spatial types (SqlGeometry and SqlGeography) belong.'
Various articles I have read showed people simply building a query like mine and then adding it as a dataset, after which it detected the spatial column and type straight away. Many did appear to be SQL Server 2008 R2 though. The article above implies a load of messing around with files in the GAC and the creation of a Windows Form Application, which presumably requires VS Pro and additional fiddling.
If anyone could point out where I'm going wrong it would be much appreciated!
Thanks in advance.
