bing
Flat 10% & upto 50% off + 10% Cashback + Free additional Courses. Hurry up
×
UPTO
50%
OFF!
Intellipaat
Intellipaat

SSRS User Handbook

If you are looking for a tool which 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 is required to get started with it. It is a handy reference sheet for the beginners and the one having knowledge about the BI tools.

Watch this MSBI Tutorial full course for Beginners video

Learn for free ! Subscribe to our youtube Channel.

You can also download the printable PDF of this SSRS cheat sheet.

ssrs

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, relational database such as Oracle and other types of multi-dimensional or XML based data source 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 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 SQL server.

The BIDS paradigm is based on the concept of projects and solution. A project is a container of one type of objects 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 number of projects such as report project, SSIS project, web project etc.

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 holds 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:

  • Shared Datasets:

It is a dataset published on a remote server and can be used by multiple reports

  • Embedded datasets:

These datasets are defined in and used by a single report

Architecture of SSRS:

It is a reporting tool which should have fast user interface Primary Component. The following diagram shows the architecture of SSRS

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 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 which 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 multi-dimensional data sources

SSRS Expressions:

StatementExpressions
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 periodFor 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 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 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

Download a Printable PDF of this Cheat Sheet

With this, we come to an end of SSRS Cheat sheet. 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 complete understanding of designing, developing, testing and tuning of Business Intelligence Reports.

Previous Next

Download Interview Questions asked by top MNCs in 2019?

"0 Responses on SSRS Cheat Sheet"

    Leave a Message

    100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.
    top

    Sales Offer

    Sign Up or Login to view the Free SSRS Cheat Sheet.