Book Image

SQL Server 2016 Reporting Services Cookbook

By : Dinesh Priyankara, Robert Cain
Book Image

SQL Server 2016 Reporting Services Cookbook

By: Dinesh Priyankara, Robert Cain

Overview of this book

Microsoft SQL Server 2016 Reporting Services comes with many new features. It offers different types of reporting such as Production, Ad-hoc, Dashboard, Mash-up, and Analytical. SQL Server 2016 also has a surfeit of new features including Mobile Reporting, and Power BI integration. This book contains recipes that explore the new and advanced features added to SQL Server 2016. The first few chapters cover recipes on configuring components and how to explore these new features. You’ll learn to build your own reporting solution with data tools and report builder, along with learning techniques to create visually appealing reports. This book also has recipes for enhanced mobile reporting solutions, accessing these solutions effectively, and delivering interactive business intelligence solutions. Towards the end of the book, you’ll get to grips with running reporting services in SharePoint integrated mode and be able to administer, monitor, and secure your reporting solution. This book covers about the new offerings of Microsoft SQL Server 2016 Reporting Services in comprehensive detail and uses examples of real-world problem-solving business scenarios.

Related Content you might be interested in

Current Title:

Small book image
SQL Server 2016 Reporting Services Cookbook
Dinesh Priyankara | Robert Cain
Nov, 2016 | 596 pages
No titles found
Table of Contents (18 chapters)
SQL Server 2016 Reporting Services Cookbook
SQL Server 2016 Reporting Services Cookbook
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.Packtpub.com
www.Packtpub.com
Preface
Preface
Free Chapter
1
Getting It Ready – Configuring Reporting Services
Getting It Ready – Configuring Reporting Services
Introduction
Selecting the edition
Installing Reporting Services with default configurations
Installing Reporting Services-related tools
Accessing Reporting Services Configuration Manager
Configuring the service account of Reporting Services
Configuring Reporting Services URLs
Configuring the Reporting Services database
Executing post-installation tasks
2
Authoring Reports with SQL Server Data Tools
Authoring Reports with SQL Server Data Tools
Introduction
The designer tool - exploring SSDT
Visual Studio 2015 support
Creating reports with a table data region
Creating reports with a matrix data region
Creating reports with a chart data region
Managing and configuring parameters
3
Advanced Report Authoring with SQL Server Data Tools
Advanced Report Authoring with SQL Server Data Tools
Introduction
Creating reports with multiple axis charts
Creating reports with multiple data regions
Creating reports with nested data regions
Creating reports with a map data region
Creating reports with cascading parameters
Creating reports with dynamic datasets
How to publish reports
How to view the XML code related to reports
4
Authoring Reports with Report Builder
Authoring Reports with Report Builder
Introduction
Is this for a developer or business user?
Enhancements to Report Builder
Connecting Report Builder to Reporting Services
How to design reports with the wizard
How to design reports manually
Working with published data sources
Working with published datasets
How to publish reports to Reporting Services
Working with published report parts
Changing reports published in Reporting Services
5
Improving User Experience – New Designing and Visualization Enhancements
Improving User Experience – New Designing and Visualization Enhancements
Introduction
Positioning parameters
Visualizing data with modern styles
Visualizing data with the TreeMap data region
Visualizing data with the Sunburst data region
Integrating R with Reporting Services
6
Authoring Reports with the Mobile Report Publisher
Authoring Reports with the Mobile Report Publisher
Introduction
Understanding the new Mobile Report Publisher
How to create a mobile report
Using simulated data
Setting up data sources
How to use datasets for mobile reporting
Understanding mobile report graphical elements
Understanding the mobile report layout
Configuring navigators
Configuring parameters for drill-through reports
7
Consuming Reports – Report Access Enhancement
Consuming Reports – Report Access Enhancement
Introduction
Exploring the new Web Portal
Managing reports with the Web Portal
Viewing reports on any web browser
Caching the report
Refreshing the cache
Creating a KPI with predefined values
Creating a KPI with shared datasets
Customizing the look-and-feel of the Web Portal
Printing reports with the PDF rendering extension
Embedding reports in other applications
8
Reporting Solutions for BI – Integration
Reporting Solutions for BI – Integration
Introduction
Making Reporting Services as a part of BI
Creating a report using Multidimensional models
Getting formatting from the Multidimensional model
Getting aggregated values from the Multidimensional model
Creating a report using Tabular models
Connecting to the HDInsight cluster and getting unstructured data
9
SharePoint Integration
SharePoint Integration
Introduction
Configuring SharePoint for Reporting Services
Handling security with SharePoint
Creating dashboards using Reporting Services
Configuring e-mail with SharePoint
Configuring subscriptions
Configuring data alerts
10
Administering and Managing Reporting Services
Administering and Managing Reporting Services
Introduction
Managing data sources
Assigning configured data sources to published reports
Caching reports
Creating snapshots of reports
Backing up Reporting Services
Monitoring Reporting Services
11
Securing Reports in Reporting Services
Securing Reports in Reporting Services
Introduction
Authenticating users for Reporting Services
Becoming a Report Portal System Administrator
Authorizing users for accessing reports via SSRS roles
Creating new roles in Reporting Services
Editing existing roles in Reporting Services
Filtering data for the current user
Handling security on shared data sources
Securing the channel using certificates
12
Custom Programming and Integration to .NET Applications
Custom Programming and Integration to .NET Applications
Introduction
Adding embedded codes to reports
Referencing external .NET assemblies
Opening reports using URL access
Using ReportViewer control for accessing reports

Creating reports with a chart data region

Charts and graphs are an increasingly important component of reporting. They convey information in just a glance, with a simple picture. It is likely you will be asked to create many reports with graphs, combining them to create dashboards.

In this section, we'll start with a simple graph to convey sales month by month based on the invoice date.

Getting ready

In the WideWorldImportersDW database, the data will be coming from the sales table. The report will display the total, including tax, summed up by invoice month/year and then charted. We'll start with the following query:

SELECT s.[Invoice Date Key] 
     , SUM(s.[Total Including Tax]) AS TotalSalesWithTax 
  FROM [Fact].[Sale] s 
 GROUP BY s.[Invoice Date Key]

How to do it...

  1. Right-click on the Reports branch in the solution explorer.

  2. Do NOT click on the Add New Report option in the pop-up menu. This launches the wizard, which does not support charts. Instead, select Add | New...

Previous Section
End of Section 7
Next Section