Reporting with Microsoft SQL Server 2012

5 (1 reviews total)
By James Serra , Bill Anton
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies

About this book

Reporting with Microsoft SQL Server 2012 will cover all the features of SSRS and Power View and will provide a step-by-step lab activity to help you develop reports very quickly.

Starting with the difference between standard and self- service reporting, this book covers the main features and functionality offered in SQL Server Reporting Services 2012 including a breakdown of the report components, development experience, extensibility, and security. You will also learn to set up and use Power View within Excel and SharePoint and connect to a tabular model as well as a multidimensional model. The book provides real-life reporting scenarios that help to clarify when those scenarios are discussing standard reporting, in which case SSRS is the best choice, and when they are discussing self-service reporting, in which case Power View is the best choice.

This book will have you creating reports in SSRS and Power View in no time!

Publication date:
March 2014
Publisher
Packt
Pages
142
ISBN
9781782171720

 

Chapter 1. Getting Started with Reporting

Every business needs reports and usually lots of different types of reports. In the Microsoft realm, there is not just one product that creates all of these reports. While having multiple tools gives you many advantages, it leads to confusion about which is the best tool to use for a specific task. There are numerous reporting tools, and we will talk about two of them: the oldest and most widely used—SQL Server Reporting Services (SSRS) and the new kid on the block—Power View. We will explain both of these tools so that you can quickly use them and have clarity on the best use of each tool.

 

Standard reporting with SSRS


SSRS has been around for quite a long time. It was first released in 2004 as an add-on to SQL Server 2000. Over the years, it has seen many improvements, making it by far the most widely used Microsoft reporting tool.

Despite the multitude of new reporting tools with their fancy and cool features, there is still a huge demand for standard corporate reports, also called canned reports or operational reports, which SSRS is ideal for.

SSRS is a feature-rich, flexible, and scalable reporting platform that can satisfy the needs of everyone from small businesses to fortune 500 companies. The following screenshot shows an SSRS report in the design mode using Visual Studio 2012:

An SSRS project in Visual Studio 2012

SSRS supports dozens of data sources including SQL Server, SQL Azure, SSAS multidimensional models and tabular models, Parallel Data Warehouse, OLE DB, ODBC and, a SharePoint list. It is a powerful report-authoring and management environment that allows the creation of static and parameterized reports. The reports are built using Visual Studio or SQL Server Data Tools for Business Intelligence (SSDT-BI), which provides a drag-and-drop interface as well as wizards that generate a Report Definition Language (RDL) file based on XML. This allows for most of the reports to be built without having to write any code.

There is also a simpler-to-use and more streamlined reporting tool called Report Builder 3.0 that looks and feels more like an Office application than the project-centric development tool SSRS. It is a popular tool that also creates an RDL file that can be used by SSRS. Report Builder has about 80 percent of the features and capabilities of SSRS, so it is geared more toward a person who needs a very easy-to-use tool and is willing to sacrifice some flexibility (nevertheless, the RDL file created in Report Builder can always be opened in SSRS to add any missing functionality).

SSRS offers two modes of installation and operation: Native mode and SharePoint Integrated mode. Native mode provides a standalone report server called Report Manager, which offers report viewing, administration, security, processing, and delivery. SharePoint Integrated mode provides the report server through a SharePoint server and almost all the features that are present in Native mode. However, certain features, such as security and storage, are integrated within SharePoint. Most companies select SharePoint Integrated mode as it provides a unified portal to store and present all documents and reports.

The previously mentioned RDL files are uploaded to the Report Manager or the SharePoint document library. All the reports can be accessed through either of the portals and can be automatically generated and distributed. Users can perform analysis using parameters, filters, drill-down, and drill-through. Reports can be scheduled and distributed via the portal, a file share, e-mail, or a printer, and can be rendered as PDF, Excel, XML, comma delimited text file, various image types (TIFF, BMP, GIF, JPEG, EMF, PNG, WMF), HTML, or Microsoft Word formats. The following screenshot shows an output of an SSRS report in Report Manager:

Running a report in Report Manager

 

Self-service reporting with Power View


While SSRS is a great solution, companies frequently need to go beyond these static reports to visualize their data in different ways to help them make better business decisions. This is where Power View comes into the picture. Power View is an easy-to-use solution that allows users to quickly create highly-interactive and visual reports that can be accessed in a variety of different ways and from different devices. The reporting experience is greatly simplified as there are no setting properties on objects, no design mode, no creating of relationships with the data, and no connecting items together for filtering. The following screenshot shows a report being created in Power View:

Creating a report in Power View

Introduced with SQL Server 2012 and integrated with the SharePoint Enterprise edition, Power View has become very popular due to its ease of use and the ability to generate very useful and cool looking reports. It was first available only through SharePoint Server (Enterprise edition), but now it is available as an Excel 2013 add-on in the Office 2013 ProPlus edition.

Power View in SharePoint can connect to Excel PowerPivot workbooks and SSAS multidimensional models and tabular models (also known as cubes). An Excel PowerPivot workbook can use many different data sources such as SQL Server, SQL Azure, text file, and Microsoft Access.

Power View in Excel 2013 uses as the basis of a report either an internal data model or an external data model such as another workbook or a SQL Server Analysis Services (SSAS) tabular model. Note that Power View in Excel 2013 does not support a multidimensional model. The internal data model can use many different data sources such as SQL Server, SQL Azure, text file, and Microsoft Access.

However, Power View is not a replacement for SSRS as both tools have their place: Reporting Services is an ideal solution for corporate reporting, and Power View is better suited for self-service reporting with established data models. Power View was developed by the Microsoft Reporting Services product team who saw it as part of the total SSRS offering.

Power View should be the tool of choice for self-service reporting for the following reasons:

  • Power View is easier to use than Report Builder 3.0, which is Microsoft's other self-service tool. While Power View does not have all the features of Report Builder as yet, it is much quicker and simpler to use. On the other hand, there are a lot of features in Power View that are not in Report Builder.

  • Because it is an Excel add-in, it allows for an easy transition from pulling in and modifying data in an Excel-hosted PowerPivot model to generating a report of that data in Power View.

  • A Power View report can be embedded in PowerPoint (SharePoint 2010 or 2013 and Enterprise edition only). It's as simple as saving your Power View report as a PowerPoint presentation. You can even filter, slice, and explore your Power View report within PowerPoint if you have an active connection to the SharePoint server.

  • It is very interactive with no design or preview mode, which you would otherwise have to switch between. The only mode it has is What You See Is What You Get (WYSIWYG). It is very easy to add fields, filters, and aggregations. Everything is done instantly thanks to the use of Silverlight.

 

Power View limitations


While you will have some SSRS reports that can be replaced with Power View reports, be aware of its limitations that can prevent the replacement of other reports. The limitations are as follows:

  • Power View does not have nearly as many customization options as SSRS. With SSRS, you can change just about every property of a report item, but Power View is very limited in terms of the properties you can change.

  • The current implementation of Power View requires the installation of a Silverlight browser plugin. This can add complications that you generally don't have with SSRS because it does not require any browser plugins. In addition, Silverlight is a dying technology and is being replaced with HTML 5 (Microsoft is working on a HTML 5 Power View client).

  • Power View in SharePoint requires SharePoint Enterprise and the installation of Reporting Services in SharePoint Integrated mode. SSRS will work with the Standard edition of SharePoint and has a Native mode that does not require SharePoint. Power View also works with Excel 2013 (ProPlus edition only), but many companies have not yet upgraded to it and are still using Excel 2010. Additionally, if you are using Excel 2013, it is likely that you still want to use SharePoint Enterprise to share the reports instead of sharing the workbook.

  • Power View does not support the passing of parameters, whereas SSRS does.

  • Power View does not have any means of scheduling reports or automatic report delivery. On the other hand, SSRS has a sophisticated way of scheduling and delivering reports using subscriptions.

  • Power View does not support custom code, so there is no way to extend its features. SSRS is very flexible in allowing you to extend its code, so you can do just about anything with it.

 

Reporting scenarios


Which is the best reporting tool for the job? The following are various reporting scenarios you may encounter and our suggestions on the best tool to use along with the reasons why:

Scenario 1

You want the reports to be created automatically and delivered via e-mail to certain users every morning. For such reports, you will pass in certain parameters such as country codes and the beginning and ending dates.

Solution: Since Power View does not support scheduled rendering and delivery of reports or parameter passing, SSRS is the reporting tool to use in this situation. In fact, this is what SSRS excels at thanks to the sophisticated subscription feature. This feature allows you to register with a publisher to get any report on a scheduled/reoccurring basis. A subscription includes parameters and a recipient list, rendering format, delivery schedule, and delivery method.

There are two types of subscriptions: standard subscription and data-driven subscription. With a standard subscription, report parameters are provided at the time of subscription and cannot be changed at runtime. A data-driven subscription allows parameter values to be returned from a query during the execution of the report.

Scenario 2

You need to create a report but are not sure what you want it to look like. Also, you want to explore the data and build a report on the fly.

Solution: Power View is ideal for users who don't know up front how they will combine and analyze the data. Moreover, they don't know what question to ask. Instead, they want to discover the meaning in the data and slice and dice the data. Power View's main purpose is just that type of ad hoc situation, whereas SSRS requires you to have a clear idea of what the report should look like and what data should be used.

Scenario 3

You need to create a simple report quickly.

Solution: While SSRS was originally created for technical users (developers), it has evolved into a more self-service tool for power business users. However, it still has a steep learning curve. On the other hand, Power View is very easy to use and extremely intuitive. Although you may sacrifice certain features by using Power View, you can generate a report very quickly, even if you have never used Power View before.

Scenario 4

You want to generate a report that has a list of all the orders from the last week.

Solution: SSRS is the tool that you use when generating a report that contains details at the lowest grain, such as a list of orders or a customer list. Power View can generate details but is much more suited for viewing higher-level summaries of data.

Scenario 5

You need to export your reports to Microsoft Word.

Solution: SSRS allows you to export to Microsoft Word, whereas Power View does not. Power View in Excel 2013 exports to PDF, Excel, XML, HTML, and comma delimited text file. In the SharePoint version of Power View, it can only export to PowerPoint. With SSRS, you can export to PDF, Excel, XML, comma delimited text file, TIFF image, HTML, and Microsoft Word formats.

Scenario 6

You want to create a report where you can perform a drill-down and drill-through of various summaries of the data.

Solution: Drill-down allows you to go from a general view of the data to a more specific one at the click of a mouse (for example, going from the sales of a state to sales of the cities in that state). A drill-through action allows you to jump to another report that is relevant to the data being analyzed in the current report, also at the click of a mouse (that is, going from showing sales by state in a tabular form to sales by state in a country map). In SSRS, a drill-through requires manually creating a drill-through action in the main report and passing parameters to other reports, which you must create. For drill-down, you must manually define the groups and detail rows or columns and then hide them, which are then accessed with a plus sign that the user clicks on. However, these can be time-consuming tasks that require a lot of coding. In Power View, it is much easier: drill-down requires you to create a hierarchy and add it to a report or create a matrix report and enable drilling down on rows. There is no additional coding as drill-down support is performed automatically; drilling down is just a matter of double-clicking on the row or column you want to drill into. Drill-through is done in Power View by simply clicking on the various chart types in the Switch Visualization section of the design ribbon and the chart is automatically changed.

Scenario 7

You need to do a lot of chart formatting for you report.

Solution: SSRS allows you to have finely detailed control over many of the individual elements in any chart you want to display. While in Power View, there is a very limited number of customization options. So while it is much quicker to build a chart in Power View than in SSRS, if you need a lot of customization for your chart, SSRS is the tool to use.

Scenario 8

You need to create a map, display data on it, and create lot customizations for the map and the data.

Solution: SSRS and Power View both include mapping capabilities. But like the previous answer in which SSRS allows for more customization of charts than Power View, SSRS also allows for much greater customization of the maps. So if you need to create a map quickly and don't need much customization, go with Power View. But if you need a lot of customizations of the map, go with SSRS.

Scenario 9

You want to create a report that animates the progression of data over time.

Solution: SSRS does not have an option that supports this, but Power View does. It accomplishes this through scatter and bubble charts. To view changes in data over time in Power View, add a time dimension to the scatter and bubble charts and a play axis. When you click on the play button, the bubbles travel, grow, and shrink to show how the values change based on the play axis.

Scenario 10

You want to integrate the report with other custom applications.

Solution: SSRS is an open and extensible reporting platform that provides developers with a large set of APIs for developing solutions. There are three options when integrating SSRS into custom applications: the Report Server Web service (also known as the Reporting Services SOAP API), the ReportViewer control for Microsoft Visual Studio, and URL access. Power View has no such option for integrating the reports with applications, other than embedding a Power View report into an HTML frame. Moreover, there is little control over sizing and toolbar options.

Scenario 11

You want to create a dashboard.

Solution: You can create dashboards in both products. With Power View, you can quickly create a very slick-looking dashboard that has a lot of visual impact; however, the customization of the dashboard is limited. With SSRS, there are more customizations when creating a dashboard, but the dashboards won't look nearly as slick as Power View, and it could take a lot of coding to obtain the same functionality that you have with Power View; this is especially the case if you want to have a lot of interactivity.

Scenario 12

You want to create monthly management reports that are mostly static and want users to be able to subscribe to these reports.

Solution: SSRS is the perfect choice for this situation. It excels in allowing you to create mostly static reports, where you just have a few filtering options. Also, users can easily subscribe to any report and choose when and how they want the report delivered. On the other hand, Power View is geared more toward non-static, heavily interactive types of reports and does not have an option to subscribe to them.

Scenario 13

Your manager wants you to dig into the data and find out why a particular store is underperforming. You are trying to answer a single, specific business question.

Solution: A typical scenario is that a manager sees a SSRS report that indicates a trouble spot, such as a store that is underperforming, based on some predefined threshold. The SSRS report has no ability to slice and dice the report to find out what is causing the store to underperform, so the manager asks you to try and find out why. This is where Power View comes into the picture. It's the perfect tool to pull in data for the underperforming store and slice and dice it to find out the underlying issue. Maybe when you dig into the details, you will find it's a particular product that is the problem and action can be taken to improve the sales of that one product.

 

Summary


In this chapter, we learned the difference between standard reporting and self-service reporting and how SSRS is ideal for the former and Power View for the latter. We got a brief overview of these two products and the data sources they use, and discussed Power View's limitations, which prevent it from being the tool used for all reporting. Finally, we saw certain reporting scenarios and discussed the best tool to use and why.

In the next chapter, we will start using SSRS and cover it in more detail.

About the Authors

  • James Serra

    James Serra is an independent consultant with the title Data Warehouse/Business Intelligence Architect. He is a Microsoft SQL Server MVP with over 25 years of IT experience. He started his career as a software developer, then was a DBA for 12 years, and for the last seven years, he has been working extensively with Business Intelligence using the SQL Server BI stack. At different times, he has been a permanent employee, consultant, contractor, and owner of his own business. All these experiences, along with continuous learning, have helped him to develop many successful data warehouse and BI projects. He is a noted blogger and speaker, having presented at the PASS Summit and the PASS Business Analytics conference. His blog is at JamesSerra.com.

    He has earned the MSCE: SQL Server 2012 Business Intelligence, MSCE: SQL Server 2012 Data Platform, MCITP: SQL Server 2008 Business Intelligence Developer, MCITP: SQL Server 2008 Database Administrator, and MCITP: SQL Server 2008 Database. He has a Bachelor of Science degree in Computer Engineering from UNLV.

    James resides in Houston, TX, with his wife Mary and three children: Lauren, RaeAnn, and James.

    Browse publications by this author
  • Bill Anton

    Bill Anton is an independent consultant whose primary focus is on designing and developing data warehouses and Business Intelligence solutions using the Microsoft BI stack. He has over 10 years of experience in the industry, and enjoys working closely with clients to overcome their data-related challenges. Bill is also an active member in the SQL Server community and enjoys sharing knowledge and helping others. When he's not working with the clients, he can usually be found answering questions on the MSDN forums, attending SQL PASS meetings, or writing blog posts at http://byoBI.com.

    Browse publications by this author

Latest Reviews

(1 reviews total)
Reporting with Microsoft SQL Server 2012
Unlock this book and the full library for FREE
Start free trial