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 beginners and those ones who have knowledge about the BI tools.
You can also download the printable PDF of this SSRS cheat sheet
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.
Watch this SSIS Tutorial Full Course for Beginners video
Component of SSRS:
It is a full-featured application that is used to design, develop, test and deploy 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 that 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 deploy 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 a number of projects such as report projects, SSIS projects, web projects, etc.
Watch this MSBI Tutorial full course for Beginners video
SQL Server Data Tools (SSDT):
It is a Visual Studio based Microsoft application configured to use for MSBI line of products such as SSIS, SSRS, and SSAS
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
Data Sources:
Data sources hold the details of the database server
Datasets:
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
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 used 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
SSRS Expressions:
| Statement |
Expressions |
| 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)
or
=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
=DateAdd(“ww”,-1, Today)
For month over month
=DateAdd(“m”,-1,Today)
For year over year
=DateAdd(“yyyy”,-1, Today) |
| Return current month name |
=MonthName(Month(Today())) |
| Uppercase fields |
=UCASE(Fields!FieldName.Value) |
| 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)) |
| Security number |
=Replace(Fields!EmailAddress.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 interact 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 to 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, which 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 Business Intelligence Reports.