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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.