SSRS User Handbook
If you are looking for a tool that can perform the designing, generation, and deployment of reports used to deliver a variety of interactive and printed reports using SQL Server Database. Then SSRS can be taken into consideration. This cheat sheet will guide you with the basic concepts which are required to get started with it. It is a handy reference sheet for the beginners and the one having knowledge about the BI tools.
Interested in learning MSBI? Enroll in our MSBI Training now!
SQL Server Reporting Services (SSRS):
SSRS is a feature included in the SQL server product. It is a server-based reporting platform used to create and manage a wide variety of reports and deliver them in a range of formats.
We can create basic reports containing tables and graphs, or more complex data reports using charts, maps, and sparklines. The reports can draw the data from the SQL Server database, a relational database such as Oracle and other types of multi-dimensional or XML based data sources such as SQL Server Analysis service, Teradata or parallel data warehouse. It is used to deliver a variety of interactive and printed reports.
Component of SSRS:
It is a full-featured application that is used to design, develop, test and deploy the reports. The main components of SSRS are:
- Databases: Reporting service uses two databases named ReportServer and ReportServerTempDB by default. The ReportServer database is used to store reports, data sources, snapshots, etc. ReportServerTempDB is used for temporary storage, and these two services are automatically created while configuring reporting services.
- Windows service: The windows service is implemented as the core of Reporting services which provides the following functionalities
- HTTP listener is a new feature implemented in the Reporting Services where internet information service (IIS) is not required
- Report Manager is an ASP.NET application which provides a browser-based interface for managing the Reporting Services
- The web service is also implemented as an ASP.NET application, which provides a programmatic interface for managing the reporting services
- Background processing is used to provide the core services for Reporting Services
- The Report Manager, Web Services and Background Processing are implemented as separate application domains
- Report Designer: It provides the capability t design, develop, test and deploys reports. It is a developer-centric tool called Business Intelligence Development Studio (BIDS).
BIDS (Business Intelligence Development Studio):
It is a tool used to develop reports. It has some enhancements to the user interface for designing, developing and testing reports included with the SQL server.
The BIDS paradigm is based on the concept of projects and solutions. A project is a container of one type of object such as SSRS reports, web pages, SSIS packages, etc. which can be used to organize things in a meaningful way. Whereas a solution is a container of one or more projects, it is a complete application that can be made with the number of projects such as report project, SSIS project, web project, etc.
Get familiar with the top MSBI Interview Questions to get a head start in your career!
Watch this MSBI Tutorial full course for Beginners video
SQL Server Data Tools (SSDT):
Report Definition Language (RDL):
It is an XML file that corresponds to XML grammar
Report Definition Language for Client (RDLC):
It is produced by the Visual Studio report definition that is (.rdlc) files in XML format to be used with ReportViewer control
A dataset stores the specific query that is used to fetch the data for a report. There are two types of Datasets:
It is a dataset published on a remote server and can be used by multiple reports
These datasets are defined in and used by a single report
Are you looking for a job change in BI? Check out this Intellipaat’s blog on Top jobs in Business Intelligence!
Architecture of SSRS:
It is a reporting tool that should have a fast user interface Primary Component. The following diagram shows the architecture of SSRS
Architecture of SSRS
The Architecture of SSRS mainly consists of the following components and tools:
- Report Builder: It is a component used to drag and drop and provides easy use of Report Design functionality
- Report Designer: It is a publishing tool hosted in Business Intelligence Development Studio (BIDS) or visual studio which can be used to develop simple and complex reports.
- Report Manager: Web-based reports can be easily accessed using this tool. The default URL is http://<server>/reports
- Report server: It is a server used to store metadata in a SQL Server database engine
- Report Server Database: It is a database that is sued to store metadata, report definitions, resources, security settings, delivery data, etc.
- Data Sources: It is used by Reporting Services to retrieve data from relational or multidimensional data sources
Interested in learning MSBI? Click here to learn more in this MSBI Training in Hyderabad!
|Return first day of the current week||=DateAdd(“d”,-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)|
|Returns first day of the current month||=DateAdd(“d”,-(Day(today)-1), Today)|
=DateSerial( year(today()), month(today()), 1)
|Return first day of current year||=DateAdd(“d”,-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)|
|Last day of current month:||=dateadd(“m”,1,dateserial(year(Today),month(Today),0))|
|Last day of previous month:||=dateadd(“m”,0,dateserial(year(Today),month(Today),0))|
|Last day of next month:||=dateadd(“m”,2,dateserial(year(Today),month(Today),0))|
|Return period over period||For week over week|
For month over month
For year over year
|Return current month name||=MonthName(Month(Today()))|
|Convert text to proper case||=StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)|
|To replace null with another value||=iif(Fields!FieldName.Value = nothing, “No Value”,Fields! FieldName.Value)|
|To alternate row color||=iif(RowNumber(Nothing) Mod 2 = 0, “Silver”, “White”)|
|Handling division by zero||=iif(Fields!DenominatorField.Value = 0, 0, Fields!NumeratorField.Value/|
iif(Fields!DenominatorField.Value = 0, 1, Fields! DenominatorField.Value))
Advantages of SSRS:
- It provides direct and efficient reporting access for information residing in databases such as Oracle and MS SQL Server
- Faster production of reports on relational and cube data
- It is used to create an easy to deploy centralized reporting infrastructure based on Microsoft Reporting services
- It provides better decision support for faster delivery of information to the business
- It provides the ability for the business to self-serve, edit and interacts with the information without relying on om IT resources
- The entire report and the data source files are stored as an XML file which is used by the reporting engine to render the reports
- It contains a simple pricing model for both entry-level and enterprise-level installations allowing the inexpensive provision of the BI tools
- XML based report definition allows to directly design the reports programmatically and render them
- The entire functionality is displayed as a single web service
- The role-based management for security is applied to folders as well as reports
- The reporting needs of the user can be managed by himself by accessing reports ad-hoc or by subscribing the reports
- The UI for the defined parameters is automatically generated
With this, we come to an end of the SSRS Cheatsheet. To get in-depth knowledge, check out our interactive, live-online SSRS certification Training here, that comes with 24*7 support to guide you throughout your learning period. Intellipaat’s SSRS online training will give you a complete understanding of designing, developing, testing and tuning of Business Intelligence Reports.