SSRS Interview Questions and Answers

SSRS is one kind of reporting tools with some extra features. In SSRS report design and maintenance is very easy. It is so faster. This article describes some common important interview questions and answers in SSRS. Hope it will help you to build successful carrier.

What is SQL Server Reporting Services or SSRS?
SQL Server Reporting Services or SSRS is a server based report generation software system. It is developed by Microsoft Corporation. It is administered via a web interface. It is used to generate and deliver interactive and printed reports. We can use it instead of Crystal Reports and other reporting tools. The entire report and data source definition is stored as a simple XML file. The reporting engine uses this file to render reports.

What is the History of SSRS?
Microsoft released SSRS in 2004 for SQL Server 2000. The second version was released in November 2005 for SQL Server 2005. The latest version was released in April 2010 for SQL Server 2008 R2.

What are the Reporting Service components in SSRS?

• Report Designer -a place where the report is designed or created
• Report Server -provides services for implementation and delivery of reports
• Report Manager -a web-based administration tool to manage the Report Server

What are the core components of SSRS?
SSRS includes the following core components:

• A complete set of tools that can be used to create, view and manage report
• A Report Server component that hosts and processes reports in a variety of formats like HTML, PDF, TIFF, Excel, CSV, and more
• An API that allows developers to integrate in custom applications or to create custom tools to build or manage reports

What is Data Set in report?
Data set is a set of data which we want to show in report. Data source is the source of data from where we are getting this data (database server name, database name, connection string).

Is SSRS support other database except MS SQL Server?
Yes. SSRS can be building based on relational or multidimensional data source like Oracle, OLEDB. ODBC etc

What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.

• Microsoft SQL Server
• OLEDB
• Oracle
• ODBC
• SQL Server Analysis Service
• Report Server Model
• SAP Net weaver BI
• Hyperion
• Teradata
• XML

What are the Types of SSRS?
The types of SSRS are given below:

• Parameterized reports
• Linked reports
• Snapshot reports
• Cached reports
• Ad hoc reports
• Clickthrough reports
• Drilldown reports
• Drillthrough reports
• Subreports

What are Advantages of SSRS or why we should use SSRS?
The SQL Server Reporting Services or SSRS has some Advantages. Such:

• It is faster and cheaper
• Efficient reporting access to information residing in both Oracle and MS SQL Server databases
• No need for expensive specialist skills
• The default report designer is integrated with Visual Studio .NET so that we can create application and its reports in the same environment
• The security is managed in a role-based manner and can be applied to folders as well as reports
• Once parameters are defined, the UI for these parameters is automatically generated
• Subscription based reports are automatically sent by mail to the users

What are the limitations/drawbacks or SSRS 2008 R2?
The SSRS 2008 R2 has some limitations. Some limitations are given bellow:

• There is no print button. In order to print need to export excel, PDF or others format
• It is very hard to debug expression or custom code
• Its does not use page number or total pages in report body
• Don’t support rounding rectangle
• There is no way to pass values from sub-reports to main report
• It is not possible to insert a sub-report into the page header or page footer
• Page header creates extra spaces in the next pages

What are the Export Options of SSRS?
SSRS allow many ways of rendering the reports:

• HTML (MHTML)
• Excel
• Acrobat
• Tiff (image)
• XML
• CSV

Can we run Reporting Services with SQL Server express edition, which is a free version of SQL Server?
Yes we can. SQL Server Express Edition with Advanced Services support Reporting Services. These is the free version.

What are the limitations in SSRS on SQL Server express edition?
Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition. But it has the following limitations:

• Management Studio cannot be used to administer report server
• Report Models will not be available
• Report Builder is not available
• Caching, History and Delivery of Report is not available.
• SQL Server agent is not available
• No scheduling is possible
• Remote server database is not available for Report Data Source (Local SQL Server is a only option,)
• We cannot store the report server database on a remote server (it has to be local only)
• Reports can be rendered only in Excel, PDF, Image formats only
• Reporting Services will not be able to use more than 1 GB of RAM
• No Subscriptions (Standard and Data Driven) can be made
• Can not be integrated with Share Point
• Can not implement Role based security
• Only named instances is supported
• Scale-out Report Servers will not be available

What are the tools available in market as an Alternative to SQL Server Reporting Services?
Non-Open Source:

• Actuate
• Hyperion (BRIO)
• SIEBEL-CRM
• BusinessObjects
• Oracle Express OLAP
• Qlikview
• Cognos
• Informatica Power Analyzer
• Proclarity
• IntelliView
• Dundas Chart for .NET
• MS-Excel
• SAS
• MicroStrategies
• Pentaho

Open Source:

• Jasper Reports
• JFreeReport
• BIRT (Business Intelligence Reporting Tools)
• OpenReport
• DataVision
• Pentaho

What is reporting lifecycle?
Generally a Reporting Services has three mainly three phases:
Development of Reports (Developer) – at first reports need to be developed and it is done the developers.
Management of Reports (DBA) – When the Report is being developed DBA ensure the following things:

• Security –only authorized user should access the report
• Execution –how the report will be executed to optimize data sources performance
• Scheduling of Reports –so that report are executed on scheduled timings

Report Delivery (DBA + Developer) – When the report is being developed and executed the report is transferred to the business users. They use it and if any modification is required the report is go back to the development stage.

How to deploy the Report?
We can deploy SSRS report in three ways.

1. Using Visual Studio
   In visual studio we can directly deploy the report through Solution explorer by providing the report server URL in project properties at Target Server URL. As our choice this will deploy entire project or single report as.
2. Using Report Server
   We can directly go to the report server and deploy the report by browsing the report from the disk location of server.
3. Creating the Utility
   SQL server provides the facilities to Create a customize utility to deploy the report.

What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It is built in with the report server and can be customized.

What is the web service used for reporting services? What is Reporting Services Web service
The web service used in reporting service or SSRS is Reporting Services Web service. With this Web service SSRS provides a single entry point to the full functionality of the report serve. These Web service uses SOAP (Simple Object Access Protocol) over HTTP and acts as a communications interface between client programs and the report server. The Web service and its methods represent the functionality of the report server and allow us to create custom tools for any part of the report life cycle (from management to execution).

What are the new features of SQL Server 2008 R2 reporting service?
The SQL Server 2008 R2 has introduced a lot of new features. Some of them are given bellow:

• New Report Types – Table, Matrix, List, Chart, and Sub report
• Some New Tools is added to report designer Toolbox
• Report Data Panel – built in page numbers
• Report Builder 3.0

What are the new features of SQL Server 2012 reporting service?
The SQL Server 2012 has introduced a lot of new features. Some of them are given bellow:

• Power View – interactive data exploration
• SharePoint integration
• Introduction to Data Alerts
• SQL Server Data tool
• New rendering extensions (supports MS Office 2010)
• Project Crescent is being introduced

What is sub report?
Sub Reports is on kind of child report which opens in main report when main report loads. We can pass parameter to sub report.

Can sub report data source be different from that of the parent report?
YES.

What is report rendering?
To Exporting a report data with different type of file format is knows as Report rending. SSRS supports multiple rendering extensions like Word, Excel, CSV, PDF, HTML etc.

Can I disable or restrict SSRS export formats (rendering formats)?
YES.
We can control this using report services server configuration file which is available at “C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\Report Server\ rsreportserver.config”.

What is the RDL file?
RDL stands for Report Definition Language. When we save a report then than the file is saved as ReportName.rdl. It is a XML file. This RDL file is used for deploying report to report server.