SQL Server Reporting Services (SSRS) has changed by a quantum leap from its 2000 version. From 2000 to 2012, it has gone through 2005, 2008, and 2008 R2 versions of SQL Server. It is not attempted to describe here all the changes that have taken place in details, as it would take volumes, but to highlight the major ones that have changed since the first edition of this book in 2008.

Visual Studio Business Intelligence (BI) has changed over to SQL Server Data Tools (SSDT), wherein the Visual Studio Shell is endowed with only BI project templates that are installed with SQL Server 2012. This translates to not needing a separate license for Visual Studio in order to create BI projects. Visual Studio 2012 (for example, the Ultimate edition) does not have templates of BI projects, but is used to create reports using Report Viewer Controls. This said, if you have both SQL Server 2012 and Visual Studio 2012, you will find the templates for BI projects in Visual Studio. SSDT installs when you install SQL Server 2012.

Major enhancements in SSRS 2012 are, taking ad-hoc reporting to the next level of experience, by leveraging the Business Intelligence Semantic Model (BISM) and tying it up with SharePoint Server to render, by the now famous, Power Views, and Data Alerts. Power View reports can be exported to PowerPoint maintaining the interactive features while connected to SharePoint Server, adding an extra dimension of interest to the stake holders.

The server-based SSRS in the SQL Server 2012 platform provides the following enhancements:

Data extensions built in SQL Server 2012 can work with the data sources shown, and with OLE DB and ODBC included; many other sources can be accessed as well:

Ad-hoc reporting gained popularity as it made it possible for non-programmer type, but business-wise information workers to create reports critical to the business. In SQL Server 2008, the Report Model Projects provided the underpinning data for creating ad-hoc reports using the Report Builder.

In SQL Server 2012, Microsoft took ad-hoc reporting to the next level in RS 2012, by creating the BISM as the core for all BI under Microsoft's umbrella. Ad-hoc reporting uses the BISM as its backbone, and uses it very effectively in creating Power View Reports in SharePoint. At the time of writing, there are two ways Power View reports can be authored—through the SharePoint with Reporting Services add-in using the BISM created with SQL Server Analysis Services, or through the Power Pivot add-in using Excel. In either case, Power View is not a substitute if one is after very complex queries on the underlying archived data, but for creating a quick and astonishingly flexible report connected to live data.

One of the biggest features new in SQL Server 2012 is Power View. Power View a browser based with Silverlight working in the background, addressing interactive data exploration and visualization features capable of presenting multiple views of data in a single report.

Although originally Power View relied on models created by what are called Analysis Services Tabular Models, Microsoft has quickly extended it to even multi-dimensional models, which are still in the Community Technology Preview stage. We will create tabular models, and using the Share Point site learn the basics of creating the Power View. In Chapter 6, Power View and Reporting Services, we will learn how to go about creating the model and using it to create Power Views.

Besides Power View for interactive data exploration, self-service Data Alerts is another important feature, which came about in SQL Server 2012. Data Alerts are alerts set up by users in a SharePoint site to alert (a third party) about changes that have occurred to a data in a report. This feature provides a pervasive insight to corporate data by being very proactive. The end user can easily configure and manage data-driven alerts by setting up what he/she wants to see and when, with the alert information being delivered by e-mails. In Chapter 7, Self-Service Data Alerts in SSRS 2012, the reader will learn about setting up this alert, managing it, and monitoring it.

Report Builder 3.0 is the reporting component of SQL Server 2008 R2 with visualizations such as maps, sparklines, and data bars, which were not a part of Report Builder 2 (introduced in SQL Server 2008). The report part gallery was also introduced in SQL Server 2008 R2 and continues in SQL Server 2012. The report part gallery enables users to re-use the existing parts of a report known as Report Parts, where the author can pick up the report part from the gallery and use it in his new report. Also, enhancements to performance while interacting with servers can be achieved. Bugs fixes are periodically applied, and the latest download of Report Builder 3.0 with SQL Server 2012 SP1 has the bugs of the previous build fixed. The enhancements to Report Builder 3.0 that started with SQL Server 2008 R2 can be summarized as follows: