Project Botticelli

Status message

Video playback issues? See tips for supported browsers. Clearing cookies and cache often helps.

Spatial Reporting with Microsoft SQL Server and SharePoint Server

29 December 2011 · 3 comments · 4855 views

Loading the video player ...

If this message stays on, please ensure JavaScript is enabled and that you are using a supported device and browser. Ad blockers may also cause this issue.

For more information please visit our device support page.

Bubble Map with SSRS, Report Builder & Bing

Spatial reports can be created using Report Builder, which comes with SQL Server. They execute in SQL Server Reporting Services (SSRS) and they can be hosted in SharePoint Server. This video shows the steps you need to take to create a simple bubble report that displays customer numbers, taken from a cube, on a map of the United States. You will also see how to share that report with co-workers using SharePoint.

Once the report has been created, it is saved as an RDL (Report Definition Langauge) file, which is an XML-based format used by SSRS. In this video, we save it to a running SharePoint server (03:29), so that the report is immediately available for everyone to see. A particularly attractive way to display a report in SharePoint uses its PowerPivot Gallery component, sometimes referred to as a Report Gallery. You can see its different views, including the Carousel, at the beginning of this video (00:28). 

SharePoint not only hosts the report, but it also enables its management (01:15), including setting permissions, compliance policies, makes it searchable, using FAST Search, permits social tagging, and it can even help you control different versions of the report. Whilst the video shows SQL Server 2008 R2 and SharePoint 2010, you may be interested to know that SQL Server 2012 Reporting Services (SSRS) adds new capabilites, especially in terms of its integration with SharePoint, bringing more features, such as a service application execution mode and user-controlled, data-driven alerting. Those innovations will be discussed elsewhere on this web site.

Another advantage of hosting a report in SharePoint is that it immediately becomes available for a further analysis, because its data can be accessed, and mashed-up, into Excel and PowerPivot, and any other analytical tools which support the OData format, like Tableau Software. You can see how to mash-up data from a report into PowerPivot in this video.

Report Builder (01:42) is aimed at power users and professional report writers, and it enables a good level of control over resulting reports. There are, however, other choices for reporting using SSRS: Report Designer, and in SQL Server 2012, Power View. Report Designer is aimed at a professional report developer, who requires a programmer-level of control over the report, and who is more familiar with Visual Studio. It requires the most amount of work to produce a report, but in return it gives the maximum level of creative control over it. Typically, however, that level of control is not needed for usual enterprise reporting, and Report Builder is a better choice.

Power View, on the other hand, brings a very innovative, self-service, instant-result way of building dynamic reports that can be built by non-professional report writers, even by casual knowledge workers. There are many exciting new features that Power View introduces, such as animated bubble charts (scatter plots), and a new way to build and access underlying data by means of BI Semantic Model (BISM)—you can see Power View and BISM in action in this video, and they are also discussed in this article. We expect that Power View reporting is likely to account for a lot of, if not even a majority of reports built in the future, especially those created for data exploration, and for self-service needs. At the same time, Report Builder will remain a key reporting tool for organisational, enterprise-wide use reports, which require more control and, perhaps, which warrant an additional effort.

If spatial reporting is of particular interest to you, bear in mind that there are other ways in which you could accomplish your goals. There are many companies, who offer bespoke solutions for this, like IDV Visual Fusion. You can do it all by yourself, too, with Bing Maps. Bing Map Silverlight DataConnector permits a very high level of interactivity in a spatial report, allowing users to interactively zoom-in and out of a live Bing Map, while looking at their own data points superimposed, dynamically, on it. We plan to upload a video showing this type of reporting, as part of our upcoming online course on Microsoft Business Intelligence.