In this blog post we will look at SSRS 2016/2017 with SharePoint 2016 in detail and in particular:
- What SSRS is and how it fits into the Microsoft BI stack
- The various components that make up the SSRS toolset
- What’s new in SSRS 2016/2016
- How to set-up SSRS to be used with SharePoint 2016
- How to write a SharePoint 2016 Report using the OOB connectivity
- How to configure SSRS 2016 SharePoint integrated mode with SharePoint 2016
- Using SSRS to report on SharePoint Online and SharePoint 2016 data.
- Viewing SQL Server Reporting Services (SSRS) Reports in SharePoint online
What is SQL Server Reporting Services (SSRS)?
SQL Server Reporting Services, part of the Microsoft BI stack, is a Server-based report builder software provided by Microsoft which allow us to create and manage a range of reports and dashboards from different data sources with a large variety of data visualizations
The SSRS service provides an interface in Microsoft Visual Studio as SQL Server Data Tools so that developers and SQL Administrator can connect to SQL databases and use SSDT tool to implement SQL reports.
Nontechnical users can use Report Builder tool provided by SSRS to format SQL less complex reports. You can extend reporting functionality by means of C# and VB as programming languages.
Users van either interact directly with Report Server Web Service (RSWS) or use Report Manager (A Web-based application that interacts with the Report Server Manager).
Users can view, subscribe to, organize, and preserve data sources and Security Settings.
All these reports can be viewed through a browser.
RDL defines Reports which can be downloaded to various format like Excel, PDF, CVS, XML and HTML Web Archive.
Report Manager provides functionality to send SQL reports by e-mail, also the user can place them into file share.
SSRS provides a host of security features, to control, who sees which reports etc.
SSRS provides a role-based security model, Security can be assigned to an entire site, folders of items, individual report or data source.
By default, Security roles are inherited
and it can be overloaded.
SSRS administered by a web interface, which can be used to produce variety of interactive and printed reports
RDL reports can be viewed by Standalone Report Server associated with Microsoft SQL Server, or by using ASP.NET Report viewer control, or report viewer (Windows Forms control).
SQL Server Reporting Service solution customers deploy on their own premises for organizing and publishing reports. These reports can be sent to the particular user in numerous ways. These reports can view on all platform like the web browser, mobile or email.
The ad-hoc reports also supported by SSRS, the designer can develop a report and deploy the same on the reporting server, while the user can choose applicable fields/data to generate reports.
This architecture contains following types of components and tools.
- Report Builder
- Report Designer
- Report Manager
- Report Server
- Report server database
- Data sources
Report Builder
Report Builder is Standalone development tool to create Reports outside of SQL Server Development Tools (SSDT Visual Studio).
At the time of Report designing, the user needs to specify “Where to get Data“, “Which Data to get“, and “How to display Data“.
While running the report, the report processor uses all the information user have specified, retrieve associated data and combines it with Report Layout to generate the report.
This tool can be used by the non-technical user to authoring the report as its easier to use.
Report Designer
Report Designer tool used to create Simple as well as complex report using SQL Server Development Tools (SSDT Visual Studio)
Its provide the graphical interface in which user can define “data sources“, “datasets“, “queries“.
The user can also define “Report layout positions for data regions and fields“.
The user can also define interactive features like “Parameters” and “Sets of reports” that work together.
Report Manager
Report Manager is a Web application to access and configure reports.
Report Manager can be accessed over the remote location with HTTP connection.
User can use Report Manager to access the folders and search reports with its general property along with historical copies of report that are saved in “report history.”
Report Manager is only working with the report server that runs in Native mode, it’s not supported by SharePoint integration mode.
User can open Report Manager with http://ServerName/Reports (“ServerName is name of the server where Report Manager is deployed“)
Report Server
A Stateless Server uses the SQL Server Database Engine to store Metadata and Object Definitions.
Report server database
Native Mode Report Service Installation use two databases to separate persistent data storage from Temp Storage requirement.
Those databases are created simultaneously and bound by their name.
Name of Databases are
- ReportServer
- ReportServerTempdb
Report Server Database
It’s stores
- Reports, Shared Data Sources, folders etc.
- Execution logs
- Encrypted connections and symmetric keys.
- Credential for data sources.
Report Server Temp Db
It’s store
For SharePoint integration mode, a user needs to configure the “Manage reporting Service Application” service from central Administration.
Data sources
Reporting services can consume data from relational as well as multidimensional data sources.
Why would it be used?
The company has lots of data collected from various location and various sources.
With this plain textual data company not able to get insights of data and failed to decide due to the same.
That is where SSRS comes into the picture, SSRS consume data from various data sources and create analytics chart based on parameters and filters which help end users to get proper insights of data and based on insights company will take require the decision.
The company can experience various advantages of SSRS, some of are:
Ease of Deployment & centralized reporting
Reports are stored in the centralized server since report are centralized, end users can get all the reports from one place and which makes overall deployment process easier.
Easy export to multiple file format
The user can able to access and share reports in HTML, CSV, XML, PDF, and images (TIFF), SSRS also offer complete rendering in MS Word and Excel. Since reports are fully editable in MS office suite hence end, user can create the custom document based on their requirement.
Develop centralized Reporting Portal
Organization can bring together all structure and non-structure data from across the company at one centralized location which help end users to see business performance at one glance
Make the report based on schedule by Report Subscription option
SSRS support both standard and data-driven subscription; one can share SSRS report via Email or file share to the particular user. The user also gets reports based on a schedule and report parameter value.
Sorting & Drill down capability
Users can apply sorting functionality on any column in ascending or descending manner also they can easily drill through any composition of the data pattern in the database.
Security
SSRS implements roles based security model to protect reports; this means every employee of organization can only allow access to report based on their authorization.
What is new with SSRS in SQL 2016/17: –
2016 SSRS: –
Microsoft released many new features in Reporting Services with the SQL 2016 release:
- Paginated reports
- Mobile reports
- Web portal
Paginated reports
This report is ideal for fixed layout documents used for printing, for example, PDF.
Users can create the report with minimalist modern style.
Users can arrange parameter however they want.
users can create hybrid POWER BI experience, hence instead of recreating POWER BI in an on-premise server, they can reuse some of the components from those reports to Power BI.
Mobile reports
Microsoft has added new report type i.e. Mobile report which incorporated with responsive layout.
Hence the user can create the report in Mobile Report Publisher and PowerBI Apps for the mobile device for Windows 10, IOS, Android, and HTML5.
A user can access data which is hosted in POWER BI Cloud and on-premise SQL 2016 Reporting Service.
As user create visualization, Mobile Report Publisher automatically generates Data for each visualization
Web portal
The Web portal is the presentation layer for all end users of native mode Reporting services, which can be view in any latest browser
The user can have access to all Reporting Service mobile, Paginated reports, KPIs and POWER BI desktop reports in the Web portal.
The user can write Custom KPI directly in Web portal also can apply custom branding.
The web portal is more like a Report Manager with the Single page, HTML 5 app.
2017 SSRS: –
From SSRS 2017, Microsoft has separated the SSRS installation package from SQL Server installer
If an organization has SQL Server Reporting Database (from 2008 to 2017), they can easily download & install reporting service.
Now since this installer is separated from SQL Server installer, hence the user can install the latest version of Reporting Service and it has zero impact on SQL Server Databases or features.
REST API
REST API gives programmatical access to the many objects of reports server catalog like
- Folders
- Reports
- KPIs
- Data sources
- Datasets
- Refresh plan
- Subscription and more
Using REST API, user can
Microsoft has built OpenAPI Specification (Swagger Specification). This SwaggerHub helps the user to generate Client library in JavaScript or typescript, Java, C#, Ruby and many more. Hence the user can choose whatever language they prefer to generate client library.
Report Viewer for SharePoint & Dot net
If the developer has ASP.Net Web Forms App with Report Viewer Control to integrate Paginated RDL reports then Developers can download latest NuGet package for Dot Net & integrate Cross browser printing, modern Browser support with the modern look.
Developers also integrate Report viewer web part into SharePoint 2013 – 2016 and get benefits like Show or hide particular toolbar buttons, override report parameter values and connect Filter web part with report parameter and this all can be achieved without SharePoint Integration mode.
Report Comments
Comments facility has been enabled for reports, hence end user can start the discussion about a particular report.
Attachment functionality also integrated with comments.
The administrator can manage permission about comments like who have authorized to read or write comments at the folder level or report level.
POWER BI Report Server
POWER BI report server includes all capability of SSRS and administrator can migrate all SSRS report into POWER BI with entire report server database.
How to set-up SSRS to be used with SharePoint 2016: –
The Reporting Services report server can be configured in SharePoint mode to run in the SharePoint Environment.
Report Server can use reporting service content types in SharePoint.
Appropriate version of Reporting Service is required to installed in SharePoint environment
There are Two types of installation mode of Reporting Service: “Native” mode and “SharePoint-integrated” mode.
Native mode can be installed on a Standalone machine, and a user wants to take advantage of SharePoint features like “Power View” they can configure Native report into SharePoint Integration mode.
SharePoint Administrator support is requiring for configuring reports in SharePoint Integration mode to implement changes in Central Administrator and published the SSRS reports into SharePoint page.
Native mode offers full features of reporting service like the paginated report, web portal, KPIs and many more.
SharePoint 2016 mini roles were attempted to offload SharePoint from some functionality.
Configuration of SSRS 2016 integrated mode with SharePoint 2016
SQL Server Reporting service has changed a lot in SQL 2016. The process to set up SSRS in SharePoint 2016 is change a little.
Process involves:
- Installation in the SharePoint
- Create and configure Service Application
- Deploy solutions
- Document library configuration to store reports.
Installing SSRS on SharePoint Servers
While running in integration mode, SSRS needs to install on any server within SharePoint farm as SSRS deployed as SharePoint service application.
Min role is relatively new to SharePoint 2016 and SSRS does not support any role than the custom role.
If server is not running the custom role, SSRS installation can be done but SSRS will shut down by role engine during next maintenances windows
User can check server roles using:
Central Administration => System Settings => Servers => Convert Server role in the system
This will redirect to role conversion dialog
If the role is set to custom, its fine else user needs to change with “New Role” drop down.
Once role is properly configured, SSRS can be installed,
SSRS integration mode can be found in “Shared Features” collection.
It contains two options:
After this, click “next” and “finish”, SSRS will be installed on a server.
Create and configure SSRS Service Application in Central Administrator
Verify user account which is creating Service application is the member of Farm Administrator group for the farm.
Follow be steps to create SSRS Service Application
Central Administrator => Application Management => Manage Service Application
On “Manage Service Application” page click “New” and click “SQL Server Reporting Services Service Application”
A configuration dialog will be open where the user can enter “Name of Service” and another relevant parameter.
Its good practice to change Database name as default name contains GUID.
There will be 3 databases created for SSRS services, one is mention above and two other databases which will use above Database name as the base.
You can also active SSRS feature in all Site collections if required, the same can be done from particular site collection as well.
Report Library creation
The user can create document library from SharePoint server. Don’t select “Report” or “Report Document Library” template while creating Document library as it has no connection with SSRS. Simply select a Simple document library.
Add SSRS content types to the library
Please note: Don’t use “Report Builder Model” content types if it’s not required as it is depreciated and used with backward compatibility
After this, SharePoint Administrator can remove “Documents” content types and create new reports or data source in this document library.
Now library can be used to store reports.
How to write an SSRS using SharePoint 2016 list data
Microsoft has declared that from SSRS 2017, there will be one installation mode for Reporting Service which is “Native” mode.
It will be Stand Alone Business Intelligence Solution on SharePoint.
As mentioned in above section What’s new in SSRS 2017, there are many new features introduced with SSRS 2016/17
- Web Portal
- Paginated Report
- Mobile Reports and many more.
Native Mode Reporting service integration with SharePoint gives benefits:
Embedded reports in SharePoint Page: embed all reports types in a Page Viewer Web part using the rs: Embed=true URL parameter.
Reporting on data in SharePoint lists: the Native connector is used with PowerBI and Report Builder to query SharePoint data and visualize in reports.
Place reports to SharePoint Library: SharePoint delivery extension for native mode available soon to delivery reports in Document Library.
Install SSRS in Native Mode
While installation SQL Server set up, Administrator needs to install “SharePoint Add-in” only to use for SharePoint integration mode
Configure SSRS 2016
Configure Web Service URL
Once the require fields are filled, user needs to click on “Apply” button to configure Web Service
Configure Database
In this section, Database Administrator needs to create or change Reporting Server Database name.
Here Database Administrator can write Database name, by clicking next, two new databases will be created.
- ReportServer
- ReportServerTempDB
In this section, we will demonstrate how to use SharePoint List data as Data Source in SSRS 2017 and create paginated report and deployed on Reporting Server.
Create Paginated Report using SharePoint List Data as Data Source
Below are the steps which give you information about how to use SharePoint Data as a Source and create Paginated Report.
Connect to Report Server
Go to the Report Server Url and click on +(Add) sign and choose Paginated Report, it will open Report builder.
New Report & dataset
Once Report builder open, the user needs to create Report & Dataset, here we have selected Chart Wizard.
Choose Dataset
After selecting Chart type (in our case it is Chart Wizard), the user will redirect to Choose Dataset screen. Here we have Selected Create a Dataset option.
Create Data Source
Once you selected “Create a Dataset”, you will be redirected to “Create Data Source” screen.
Here, you can give “Name of the Data source“, “Select connection type” and Connection string.
The user needs to choose “Microsoft SharePoint List” in a Select Connection Type option in order to establish the connection between SharePoint & SSRS and to fetch data from SharePoint List.
The user needs to enter “Site Name” in a Connection String Textbox, to establish particular SharePoint site connection to SharePoint.
Specify Credential in Data Source Properties
Here, we have selected “Use Current Windows user. Kerberos delegation might be required” option, as we are using Windows Authentication and the current user has administrator privileges in SharePoint and SQL.
Design Query & Select List
Once connection establishes successfully, when users click on Next, then they will redirect to “Design a Query” Screen.
Here the user can choose particular SharePoint List and its fields, also user can apply additional filters, here we have selected “UserList“
list.
Select Chart Type
Once users click “Next” from “Design a Query” screen, they will redirect to “Choose a Chart Type” screen.
Here users can select any chart types from available default chart type. We have selected column Chart type.
Arrange Chart Fields
Once users have selected particular chart type, they will redirect to “Arrange Chart Fields” from here users can select any field to configure into Categories, Series, and values column based on their requirement.
Preview
Once finished with “Arranging chart field”, users will redirect to preview section.
Once finish, users will able to see chart design in Report Builder
Run the Report
Check the report in Report Server URL
Users also able to see the report in “Report Server URL” and run the same.
Integrating the SSRS report with SharePoint 2016
Native mode comes with a pair of web parts that allow SSRS web parts to embedded into a SharePoint page.
Administrator can find RSWebparts.cab file for web parts from “C:\Program Files (x86) \Microsoft SQL Server\130\Tools\Reporting Services\SharePoint”
Copy this file from SQL to SharePoint farm and install the same
Install-SPWebPartPack -LiteralPath “H:\Himanshu\RSWebParts.cab” -GlobalInstall
Use of the report viewer Web part
Once both web parts are installed “Report Viewer” and “Report Browser” will display under the miscellaneous section while new web part is added.
Report browser used to display the report in Server.
Report viewer used to render those report.
Use SQList to synch SharePoint data to SQL Server and report using SSRS2016/2017
Use SQList Product to export SharePoint Data into SQL
First of all, we are assuming you have installed license version of SQList on your computer.
Please follow below instruction to Export SharePoint data into SQL.
Choose a SharePoint site for Export.
You need to write Site Name, Table Prefix, Site URL, Authentication mode, Username and Password to establish the connection between SQList to SharePoint Portal.
Choose a destination server
This section enables us to establish database connection which the SharePoint site will be exported.
You need to write SQL server name with an instance, Database Name, Database Engine, Authentication mode, Username and Password to establish SQL test connection.
Choose Export Type
In this section, you need to choose an export type, you can choose Export List and library from this site only.
Select which list to export
In this section, you can select Particular list or library which you want to export from SharePoint to SQL.
Done
This section confirms that all your changes have been completed, post-finish SQList service will be started.
SQList Service is running
This screen will be display post finish on the last screen, you need to click on refresh to check logs of events.
This section displays all details logs of export SharePoint data into SQL.
This SQL table display all your data successfully exported from SharePoint site to SQL Table.
Now since data is exported into SQL, we shall use this data as Data source in SSRS and create Report
Use exported SQL data as Data source in SSRS and create Reports.
This section indicates how to use SQL Data as a data source and create SSRS Reports.
Configure Reporting Service using Report Server Configuration Manager
In order to host SSRS reports, Administrator needs to configure Reporting Service in the server where reporting service instance is installed.
Below are steps to properly configure Reporting Service on the server.
Report Server Configuration Connection
When user open Reporting Service Configuration Manager, he/she will redirect to “Reporting Service Configuration Connection” screen.
In this Server name and Report Server Instance name populated automatically, users need to connect the same to establish the connection.
Service Account
Once connected, the user will be redirected to Service Account Screen, in which User has rights to choose “Built-in Accounts” or any accounts with relevant authorization, a user will Sysadmin right will work.
After writing relevant credential, the user needs to click “Apply” button, to authenticate.
Web Service URL
This screen will help users to configure Web Service URL for Reporting Service. Default values will be auto-populated for each field, the user can update the same if required, after confirming all correct values, User needs to click “Apply” button to enable Web Service URL.
Note: If user faces any error while clicking on this URL, Kindly check reporting Database status.
The User clicks the URL to given screenshot:-
Database
The user can create or change Reporting Server Database from this screen, if a user configures reporting service for the first time, he/she needs to click on “Change Database” button and create Reporting Server Databases.
Two databases will be created after this configuration.
- ReportServer
- ReportServerTempDB
Once all process has been done, press “Apply” button.
Database Creation.
List of Databases
.
Report Manager URL
The user needs to click “Apply” button to enable default “Report Manager URL”. Once its enable then user can able to click URL and will redirect to Report Manager URL
Report Server Manager Screen.
Create SSRS Report using Report Builder
Once SSRS Service properly configures, now it’s time to use Exported data and create Chart using Report Builder.
Choose Project Types:
The developer can select any particular service Types to build SSRS Reports.
Add and Configuration Data Source and Configuration
Once the SSRS Configuration completed, A Developer needs to add the data source to the reports. It can be done using “NEW/ Data Source” property of Shared Data Sources.
In this screen, a user can write “Name of the Data Source“, select “Type” and Configure “Connection Strings“.
Add Data Set
The Developer creates Dataset using the Report Builder. It can be done using “NEW/ Data Set” to create and add dataset.
The developer needs to Data Source to create a dataset.
How to add data source to create new Data Set:-
Above the Screenshot, a click link “Browser other data sources” to add the Data source.
After the click link, the new page will be open, a new page screenshot given be below.
After the choose Data Source, the developer press an “Open” your data source is added.
After click open, the below screenshot view your added data source.
Query Designer
The user can configure Query using Query Designer which will be used in Dataset.
Add Reports
Once Dataset is ready, User can create the report using Dataset.
They are mainly two types of Report:-
- Paginated Report
- Mobile Report
How To Create Paginated Report:-
A data set will be created completely after a developer starts to create a paginated report.
The developer uses Report Builder to create a Report. A developer chooses the Paginated Report to create the below page opened.
Report Wizard
This is the very first screen after adding a new report, this screen information about how to configure Reports using Report Wizard.
Choose Data Set
This screen is display information about Shared Dataset, the user can select any shared dataset or can create the new data set one based on requirement.
Choose Chart Type:-
Here, the developer can choose a chart type, which displays your data.
They are mainly five type of chart type:-
- Column
- Bar
- Line
- Pie
- Area
Design the Table
From this screen, User can decide, how the report will be generated, where the user has all the fields from Query and configures those fields in Pages if grouping functionality is required then the user can configure particular field into Groups sections.
Complete the wizard
After completing the design of table and press “Next“, the user can give the name of the reports and click “Finish” to complete report building procedure.
After click “Finish” the developer are also change font, schema, color, and style.
NOTE: – the developer saves the report in this section.
Paginated Report Output
Once finish, Report will be generated and the user can run the report also see the preview of this report from “Preview” section in SQL server reporting service.
How to create Mobile Report:-
A Report Builder is also used to create a “Mobile Report“.
The developer also chooses the “NEW/Mobile Report” to create the Mobile Report.
After they choose the Mobile Report, the below page is displayed.
After the page Opened, The developer is select any type of Navigator, Gauges, like a developer
Drag and drop the item in given Block. The below page has displayed the navigator and gauges.
The Developer has also changed the report name.
Report data
The report data is data view a Navigators and gauges. The Mobile Report
Import the data into the dataset. Below page display Mobile report data.
Mobile Report Output:-
In this section, the developer click the “Preview” to see the Mobile Report.
NOTE: – Save the Report in this section.
After, saving the report the Report is run on Reporting Service.
Published SSRS Report in Native Mode
Once Report is built, now it’s time to deploy the report to the Report server and configure permission so every user in the organization can review the reports from Report Server.
Report Manager Page after and Publish reports.
User needs to Save the report in Report Server, once user publish the Reports, Report Manager will be looked like this.
List of reports
Run the report on Report Server
The user can run any report from “Report List” in Report Manager.
Paginated Report Run on report server:-
Mobile Report Run on Report server:-
Setting the permission in Report Manager
Report Manager provides facility to manage permission of any folders, Datasets, Data Source and reports.
Follow the procedure to manage permission in Report Manager.
User clicks on “Manage Folder” from Report Manager Page.
Once click on Manage Folder option, below page will be displayed where user can select “Security”
This is by default security group, the user can click on “Edit ” option to edit the permission.
The user can write Group name and Username and select particular types of roles.
Displaying SQL Server Reporting Services Reports in SharePoint Online pages
The lack of out of the box support for SQL Server Reporting Services with Office365 has left a gap in functionality for organisations who need to utilise paginated reports against their data.
We’ve put together this blog post that explains how to make SQL Server Reporting Service (SSRS) reports available in SharePoint online as well as a simple way to build SSRS reports on SharePoint Online data.
It covers:
- Options for using SQL Server Reporting Services in the cloud
- How to display a SSRS report in a SharePoint Online page
- How to report on SharePoint Online data in SSRS and display in a SharePoint Online page.
READ NOW: Display SQL Server Reporting Services Reports in SharePoint Online