09
DecThe objectives of this fundamental guide of SQL Server Reporting Services (SSRS) is to make you familiar with the concepts like-
To learn SSRS, there is no need to have advance knowledge of the database. Learn some basic of SQL Server, especially SELECT statement.
To pursue further to take up this tutorial, your system requires the following components installed: -
SQL Server Reporting Services is a feature given by MS SQL Server which is used to design, develop, test, and deploy reports. It is a server-based report generating system given by Microsoft which is a part of a suite of Microsoft SQL Server Services. The system is used to prepare and deliver a variety of interactive and printed reports. With the help of SSRS, MS Visual Studio get an interface so that developers and SQL administrators may connect to SQL databases and use SSRS tools to format SQL reports in many complex ways. With SSRS, you can create formatted reports with the help of tables, graphs, and charts. SSRS reports are hosted on a server which can be executed anytime by using parameters.
Primarily, there are three types of reporting services.
To make the effective business decisions, users across the company need to have easy access to the informative and instinctual reports that combine data from many sources throughout the organization; this is where SSRS help the organization with the conjunction of SQL Server database. If your organization already has an Enterprise, Standard or Express Edition, then you already have SSRS access to you. So, there is no need to shell out extra money for purchasing SSRS.
SSRS collaborates benefits of a centrally-managed reporting system with the flexibility and on-demand nature of reporting requirements including managed enterprise reporting, ad-hoc reporting, embedded reporting, and web-based reporting to allow organizations to deliver compatible information wherever it is required throughout the entire organization.
Below are the amazing benefits of using SSRS, which will insist you to use SSRS for your organization: -
SSRS is an inclusive reporting platform where reports are stored on a centralized web server. Reports in SSRS are centralized; that’s why users can run reports from one place. Due to centralized reports, installation of the report is very simple.
Users can access and share reports in the format which they are familiar, which includes formats like- HTML, PDF, CSV, XML, and TIFF. SSRS reports are also rendered using MS Office Word and MS Office Excel. The reports rendered using MS Office package are editable and enable users to create customized documents based on these reports.
SSRS enables organizations to embed reports directly into business applications and web portals by allowing users to consume reports within the context of their business process. Here, organizations are capable of bringing overall business-critical data, which can be structured as well as unstructured across the company. The data is centralized at one proper location and provide one common experience for the access of information, which helps users to see the performance of key business information at a glance.
SSRS supports standard as well as data-driven subscriptions that enable automatic delivery of reports to the right users by pushing them directly to each user through email. Users can use standard subscriptions to have reports delivery based on the schedule of a customer and specified values of the parameters of the report.
By implementing sort capabilities to a report, users become capable of sorting data by any of the columns of the report in either ascending or descending order. For example, a user viewing a report that contains a list of sales which might sort the data first by date, and then by sales amount.
With SSRS, users can seamlessly drill through any combination of data in the database without any programming or customization.
Every user has a different expectation and requirement while viewing and interacting with the reports. For this, a report format that works well for one kind of data might not be appropriate for other types of data. SSRS supports a wide range of display options which enables to create reports that display data in the most appropriate layout for the users including a list, chart, table, or matrix format.
Security of data is mandatory in today’s world. SSRS provides enough security measures to safeguard the safety of the company’s data. SSRS implements a flexible and role-based security model to protect reporting resources. This fortifies that every employee is allowed to access data based on the role which the user is entitled.
The first thing to do is to create a report server project because we will need this later for saving report definition files and any other files that you need for creating SSRS reports.
The first step is to open SQL Server Data Tools. Click on the File menu, find New and then Project. After this, you need to click on Business Intelligence. Click on the Reporting Services and then Report Server Project. If you want to display the Report test project to get you started, you can type Report test in Name. The last step is to click OK to finish.
In the View menu find the Solution Explorer and then right-click on the Reports folder. In the Add section click New Item. In the window Add New Item, click Report. As you can see, Report Designer has two available views. In Design view, you define your report layout, and in the Preview view, you can run your report.
To retrieve data from a database or some other resource, you will need to define the data source. In the following section, you will see how you can define the data source. We will use AdventureWorks2012 database as an example.
In the View menu find and click Report Data, then New and after that Data Source. Type AdventureWorks2012 in the field Name. Select Embedded connection As Type select Microsoft SQL Server. In Connection string field type:
Data source=localhost; initial catalog=AdventureWorks2012
Read: Learn SQL Union All Query Operators with Examples
Note: If the database is not on the local computer, replace localhost with the name of your database server instance.
After this, click on the Credentials and then Use Windows Authentication. Click OK, and you’re done.
You will need some basic SQL knowledge to create a query and define what information you will need from your database. The query you will see is just an example, and for your own purposes and database, you will have to change that step.
In the Report Data pane find and click New, and then Dataset. In the Dataset Properties dialog box as Name type DataSet1, Make sure to check to Use a dataset embedded in my report. Then select AdventureWorks2012 as your data source, Check Text as a Type, and type this into the Query input:
SELECT
soh.OrderDate AS [Date],
soh.SalesOrderNumber AS [Order],
pps.Name AS [Subcat],
pp.Name as [Product],
SUM(sd.OrderQty) AS [Qty],
SUM(sd.LineTotal) AS [LineTotal]
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader AS soh
ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesOrderDetail AS sd
ON sd.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product AS pp
ON sd.ProductID = pp.ProductID
INNER JOIN Production.ProductSubcategory AS pps
ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS ppc
ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, pps.Name, pp.Name,soh.SalesPersonID
HAVING ppc.Name = 'Clothing'
Note: soh, pps, sd, pp, PPC are just shorter names for tables that we have in AdventureWorks2012 database.
After finishing with previous technical details, we can start with more interesting things, like designing your first SSRS report. This part is easy because instead of writing code, you can drag-and-drop graphic icons into the report format. This section will show you how to add Table and Fields to your report.
In the View menu click Toolbox, then find and click Table and drag the mouse to the design area. In the left pane, you can expand the dataset Dataset1 to see all the fields. Drag one of the fields (e.g., field Date) from Report Data to the column in the table. You can continue adding fields; table will automatically add more columns.
Preview report
If you want to preview your report to see how it all looks, to correct errors, to correct issues, or to verify design and data connection, click tab Preview.
Tips and tricks
An easier way to add a table is to right-click on the design surface, click Insert, and then click Table.
If you want to format Date field to show only the date, follow the steps below:
In the Design tab, right-click the desired cell, then click Text Box Properties. Find and click Number, then in the field Category click Date. As Type select date format you want, and click OK to finish.
If in your table you have a field that holds information about currency, but you only see ordinary numbers, you can format that field to display a number as currency:
In the Design tab, right-click the desired cell, then click Text Box Properties. Find and click Number, then in the field Category click Currency. In accordance with your needs, you can change defaults. Click OK to finish.
You also have an option to change text style (font, size, etc.), and to change the column width. Column width you can change by simply dragging the columns to the desired size.
Text style you can change by clicking the Format menu. In Format menu find Font, then click whatever you need (bold, italic, etc.)
If you want to make data set in your SSRS report, do the following:
Click the Design tab, and then choose pane Row Groups. Drag the field you want to group to the pane Row Groups. From your report pane drag some other field you want to group. Delete the old columns to the double line. If new columns need to be format, just right-click the cell and then click Text Box Properties, the next steps are the same like in the formatting report section.
Tips and tricks
You can do the same by right-clicking on the surface and clicking View, and then Grouping.
Total is the sum of numeric, non-null data in the data region, and if you want to add totals for a group, you can do that by clicking Add Total for the group in the Grouping pane, and if you want to add totals for an individual cell just click Add Total for the cell.
Read: How to Download and Install Microsoft SQL Server Data Tools?
Add a daily total and grand total
Right-click the cell [Order] and choose to Add total, then click After.
Type Daily to format a new name, Daily Total
After that, select the new cell [Daily Total], two Sum cells, and the empty cell you see between them.
In the Format menu, choose a background color. We chose the color orange.
Right-click the cell [Date] and choose to Add total, and then After.Type Grand to format a new name, Grand Total. Select the new cell [Grand Total], the two [Sum] cells and the empty cell you see between them. In the Format menu, choose a background color. We chose the color light-blue.
Tips and tricks
After you add total, you can change the default function Sum. There is a list of different function you can use (avg, count, etc.).
Finally, when you finished with creating your first SSRS report, you may want to publish the report:
In the Project, menu click Test report Properties. Put your report server’s name in the field TargetServerURL, then click OK. In the Build, menu, click Deploy Test report. You will get a message that indicates whether you have successful or unsuccessful deployment.
Note: A problem will have occurred if you don’t have permissions on the report server or if you have been using SSDT with administrator privileges.
The most common problem in publishing the report to the report server is configuring the target server URL.
The first step that you need to do in the configuration is to launch the SQL Server Reporting ServiceConfiguration Manager and connect to the Reporting Service.
After that in section Web Service URL, you can find URL to the report server.
In section Report Manager URL, you can see URL for viewing and managing reports.
In the Project menu open the Test report Properties.
Note: Prefix “Test report” is the name of your report.
Final step in configure target server URL is to fill the Web Service URL in the TargerServerURL property with correct URL that you find in Report Manager URL.
Congratulations, you have successfully created your first SSRS report!
Following are the features available in SSRS based upon the versions:
| Feature | Enterprise | Standard | Web | Express with Advanced Services | Developer |
| Mobile reports and analytics | Yes | Yes | |||
| Supported catalog database SQL Server edition | Standard or higher | Standard or higher | Web | Express | Standard or higher |
| Supported data source SQL Server edition | All SQL Server editions | All SQL Server editions | Web | Express | All SQL Server editions |
| Report server | Yes | Yes | Yes | Yes | Yes |
| Report designer | Yes | Yes | Yes | Yes | Yes |
| Report designer web portal | Yes | Yes | Yes | Yes | Yes |
| Role-based security | Yes | Yes | Yes | Yes | Yes |
| Export to Excel, PowerPoint, Word, PDF, and images | Yes | Yes | Yes | Yes | Yes |
| Enhanced gauges and charting | Yes | Yes | Yes | Yes | Yes |
| Pin report items to Power BI dashboards | Yes | Yes | Yes | Yes | Yes |
| Custom authentication | Yes | Yes | Yes | Yes | |
| Report as data feeds | Yes | Yes | Yes | Yes | Yes |
| Model support | Yes | Yes | Yes | Yes | |
| Create custom roles for role-based security | Yes | Yes | Yes | ||
| Model item security | Yes | Yes | Yes | ||
| Infinite click through | Yes | Yes | Yes | ||
| Shared-component library | Yes | Yes | Yes | ||
| Email and file share subscriptions and scheduling | Yes | Yes | Yes | ||
| Report history, execution snapshots, and caching | Yes | Yes | Yes | ||
| SharePoint integration | Yes | Yes | Yes | ||
| Remote and non-SQL data source support | Yes | Yes | Yes | ||
| Data source, delivery, and rendering and RDCE extensibility | Yes | Yes | Yes | ||
| Custom branding | Yes | Yes | |||
| Data-driven report subscription | Yes | Yes | |||
| Scale-out deployment (web farms) | Yes | Yes | |||
| Alerting (SSRS 2016) | Yes | Yes | |||
| Power view (SSRS 2016) | Yes | Yes | |||
| Comments | Yes | Yes | Yes | Yes | Yes |
Read: What is SQL Formatter? Features of SQL Formatter
SSRS has six primary components:
http://<server>/reports
It is abbreviated as Report Definition Language. It is an XML representation of SSRS report definition and is composed of XML elements that match an XML grammar created for reporting services. In short, RDL is-
There is a total of 9 types of SSRS reports: -
1). Parameterized Reports
These types of reports use input values to complete the report. With these reports, you may differentiate the output of a report based on values that are positioned when the report runs. These reports are frequently used for drillthrough reports, linked reports, and subreports for connecting and filtering reports with related data.
2). Linked Reports
It is a report server item that provides an access point to an existing report. Ideally, it is similar to a program shortcut that you use to run a program. When you install a software program in your system or create a Java application, you don’t open the whole folder, but you will only run the ‘.exe’ file to run the application, whether it is a software program or a Java application. Linked reports are the same. Linked reports can be created on the report server when you want to create an additional version of an existing report. For example, a single regional sales report can be used to create region-based reports for all your sales enclaves. You can create linked reports whenever you want to install an existing report with different settings.
3). Snapshot Reports
It is a kind of report that contains information of the report layout and query results that were redeemed at a specific point in time. Snapshots report are processed on a planned schedule and then it is saved on a report server database and shows the current layout and data of the report at the time the snapshot was created.
There is no specific rendering format of the snapshot reports but the final view of the report is viewed after the request of user in the following formats-
Snapshot reports serve three purposes: -
4). Cached Reports
It allows you to create a copy of the processed reports. It is used to improve performance by minimizing the number of processing requests to the report processor and by minimizing the time required to retrieve large reports. These reports have a compulsory expiration time which is usually in minutes.
5). Ad-Hoc Reports
These reports can be created from an existing report model using report builder. These reports refer to specifically to the report builder reports. These reports leverage report models and pre-defined templates to enable information workers to easily and quickly explore business data using familiar technology and data structures that have been implemented in the report model.
6). Clickthrough Reports
This report displays related data from a report model when you click interactive data carried within your model-based report. This is generated by the report server based on the information carried within the report model. These field settings cannot be modified in the report authoring tools. Clickthrough reports are autogenerated, but you can create customized reports as an alternative report to the model for interactive data items that are displayed on the screen. The customized report is a standard reporting services report.
7). Drilldown Reports
This report initially hides complexity and enable the user to toggle conditionally hidden report items to control the quantity of detailed data which they want to see. This report retrieves all possible data that can be shown in the report. [Note: for reports with a large amount of data, it is better to consider drillthrough reports]
8). Drillthrough Reports
This kind of reports is accessed through a hyperlink on a text box in the original report. The report works with the main report and is the target of a drillthrough action for a report item such as placeholder text or a chart. The main report outputs summary information in the form of a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display larger details based on the aggregate in the main report. These reports can be filtered by using parameters. These are customized reports that are saved on the report server and opens separately.
9). Subreports
This kind of report displays another report inside the body of the main report. Ideally, a subreport is similar to a frame in a webpage. As its name suggests, it is embedded within a report. Any report can be used as a subreport. It uses different data sources as compared to the main report. The subreport is stored on a report server usually in the same folder as the parent report. You may setup subreport to pass parameters to the subreport. [Note: For reports with many instances of subreports, consider using drillthrough reports]
Find below several advantages of using SSRS: -
Find below several disadvantages of using SSRS: -
Read: What Is SQL Candidate Key? Difference between Primary Key & Candidate Key
JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.
AWS
DevOps
Data Science
Hadoop
Salesforce
Course for testing
QA
Business Analyst
SQL Server
Search Posts
Trending Posts
Related Posts
How to Download and Install Microsoft SQL Server Data Tools? 471.9k
Which SQL Server Role you Need to Practice for the Highest Salary in 2020? 173
What Is A Data Warehouse, And Why You Need It? 214
How to Create Database in Microsoft SQL Server? 193.2k
What is Update Query in SQL? How to Update (Column Name, Table, Statement, Values) 318.1k
Receive Latest Materials and Offers on SQL Server Course
Interviews
