Search icon
Subscription
0
Cart icon
Close icon
You have no products in your basket yet
Save more on your purchases!
Savings automatically calculated. No voucher code required
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
$9.99 | ALL EBOOKS & VIDEOS
Over 7,000 tech titles at $9.99 each with AI-powered learning assistants on new releases
Reporting with Microsoft SQL Server 2012
Reporting with Microsoft SQL Server 2012

Reporting with Microsoft SQL Server 2012: Learn to quickly create reports in SSRS and Power View as well as understand the best use of each reporting tool.

$22.99 $9.99
Book Mar 2014 142 pages 1st Edition
eBook
$22.99 $9.99
Print
$38.99
Subscription
$15.99 Monthly
eBook
$22.99 $9.99
Print
$38.99
Subscription
$15.99 Monthly

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Buy Now
Table of content icon View table of contents Preview book icon Preview Book

Reporting with Microsoft SQL Server 2012

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.

Left arrow icon Right arrow icon

Key benefits

What you will learn

Get to grips with reporting scenarios that describe the best reporting tool to use Follow the stepbystep exercises to build a report in SSRS and Power View Visualize and interact with data in bold new ways with Power View Generate reports quickly using intuitive interfaces Create selfservice reports with a wide variety of formats

Product Details

Country selected

Publication date : Mar 18, 2014
Length 142 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781782171720
Vendor :
Microsoft
Category :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Buy Now

Product Details


Publication date : Mar 18, 2014
Length 142 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781782171720
Vendor :
Microsoft
Category :

Table of Contents

12 Chapters
Reporting with Microsoft SQL Server 2012 Chevron down icon Chevron up icon
Credits Chevron down icon Chevron up icon
About the Authors Chevron down icon Chevron up icon
About the Reviewers Chevron down icon Chevron up icon
www.PacktPub.com Chevron down icon Chevron up icon
Preface Chevron down icon Chevron up icon
1. Getting Started with Reporting Chevron down icon Chevron up icon
2. SSRS – Standard Reporting Chevron down icon Chevron up icon
3. Development Activity with SSRS Chevron down icon Chevron up icon
4. Power View – Self-service Reporting Chevron down icon Chevron up icon
5. Development Activity with Power View Chevron down icon Chevron up icon
Index Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Empty star icon Empty star icon Empty star icon Empty star icon Empty star icon 0
(0 Ratings)
5 star 0%
4 star 0%
3 star 0%
2 star 0%
1 star 0%
Filter icon Filter
Top Reviews

Filter reviews by


No reviews found
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.