SSRS interview questions with answers.

Are you preparing to take interview on SQL Server Reporting Services or are you preparing to interview a candidate for the position? If yes to any of the about questions then you might want to consider some of the questions below to ask your candidate to test their technical knowledge about SSRS.

1.What is SSRS?
SSRS stands for SQL Server Report Services. It is the component of Microsoft SQL Server 2008 that provides an enterprise-ready and extensible presentation layer for the Microsoft business intelligence platform to develop, manage and deliver reports.

2. What are the stages of the Report Life Cycle?
The stages of the Report Cycle include:
1. Authoring /Development
i. Development and creation of a report
2. Management
i. Optimization and Security
3. Delivery / User Access
i. Deployment and Delivery through Emails, Shared Folders, etc. Subscriptions.

3. What happens during Requirement Gathering?
Ask questions such as:
• What is the data source? OLTP? SSRS? Analytical DB?
• What kind of business questions do you want to answer?
• How should this data be presented? In what form?

4. What are the main components of SSRS?
The main components of SSRS include:
1. Report Designer
2. Report Builder
3. Report Server
4. Report Manager

5. What types of Reports does SSRS support?
The types of Reports include:
1. Managed Reports
2. Ad Hoc Reports
3. Embedded Reports

6. What are Managed Reports?
A Managed Report is for users who know how to use all of the aspects of the reporting tool.
They are Enterprise Reports that are managed by the SQL Developers up until the finished product.
These users know the structure of the underlying data and they know how to retrieve the data. These reports are code based for developers.You use the Report Designer to make Managed Reports.

7. What are Ad Hoc Reports?
Ad Hoc Reports are reports that use queries that are made on the fly via drag and drop functionalities.
They can be used by non-technical people who do not know how the data is structured or how to query. They are mostly GUI based. You use the Report Builder to make Ad Hoc Reports.

8. What are Embedded Reports?
Embedded Reports are reports which integrate different code such as Java or .NET in order to extend the functionality of the reports. They allow them to be more robust and interactive. The Report can be embedded into the application or the code can be embedded into the report. It works both ways.

9.What is the File Types of Reports?
The File Types of reports are .RDL, Report Definition Language.

10. What is a Report Server Project?
It is the project template used to create Managed Reports. It includes all of the functionalities that developers need in order to retrieve data, transform it using code, and create Managed Reports or embedded reports.

11. What is a Report Model Project?
It is the VS project template used to create Ad Hoc Reports. It allows the developer to create a basic model (.SMDL file) that is the input of the Report Builder. The model is a container for meta data.
The non-technical user can then use the Report Builder to make an Ad Hoc Report to show whichever data they want. Model → Report Builder → Ad Hoc Report

12. What are the Report Databases?
The Report Databases are: ReportServer and ReportServerTempDB

13. What is the Report Server?
Report Server is a web application that acts as a gateway to your underlying Report Server database. This way you can read the metadata of the reports. The URL is: http://localhost/ReportServer

14. What is the Report Manager?
Report Manager is a web interface which allows you to manage your reports on the report server. This allows you to manage the reports and their data by creating folders, applying security, optimization, etc. The URL is: http://localhost/Reports

15. What are the Deployment Modes?
The Report Deployment modes include:
1. Native Mode (Reporting Engine)
2. Sharepoint Integration Mode(Bi Data Centers which has Web UIs and Applications)

16. What are the different Service Account types?
The different Service Account types include:
1. Local System(Uses Windows Credentials)
2. Network Service(Uses the credentials supplied by your Network Connection)
3. Local Service(Uses the credentials specific to the Reporting Services engine)

17. What are Encryption Keys?
Encryption Keys are a symmetric key to encrypt credentials, connection strings, and other sensitive data that is stored in the report server database.
18. What happens to Encryption Keys when a Report DB is restored?
When a Report Database is restored, it gains a new encryption key. In order to fix this, you must backup your encryption keys in order to restore your encryption keys upon a Report DB restore.

19. What is a Report?
A Report is a structured arrangement of information. Typically, the information in a business report comes from data in a business application, although report information can be derived from a variety of sources. They are answers to Business Questions.

20.What is a Reporting Platform?
A Reporting Platform is an integrated set of applications that supports all required activities in a managed report environment, including report development, management, and viewing.

21.When was the first Reporting Services released?
The Reporting Services reporting platform was introduced in 2004 as an additional component of Microsoft SQL Server 2000. Since then, organizations small and large have migrated to Reporting Services to take advantage of the many benefits it has to offer.

22. What is a Managed Report?
A Managed Report is characterized by detailed operational data or summarized management information that is gathered from a variety of data sources and formally organized into a central repository.Also known as Static Reports. Often, managed reports must conform to specified formatting standards. They are created by technical people.

23. What is an Ad Hoc Report?
An Ad Hoc Report allows users with limited technical skills to produce new, simple reports on their own. Ad Hoc Reports are created from Report Models created by technical people. User can choose whether to save them privately or to share them with others by publishing to the Reporting Services centralized store.

24.How accessible are Ad Hoc Reports?
Because Ad Hoc reports usually don’t have the same layout requirements as managed reports and because Report Builder is easy to use, typically anyone with permission to see data used for reporting is allowed to create ad hoc reports.

25. What are Report Models for?
To support business users creating ad hoc reports, a more technical user can develop a report model that describes the tables and columns in a database in a user-friendly format for non technical users to easily drag and drop for their report creation.

26. What are the most important aspects of Reports for the end user?
Accuracy and Performance. Business decision makers want accurate reports so they can correctly analyze the data and get accurate answers to their questions.

27. What are Embedded Reports for?
SSRS allows you to develop and manage Embedded Reports for an organization’s portals or custom applications. They allow developers to integrate .NET, Java, C# codes into the report or embed the report into the codes. It is a development platform that can be used by in-house developers or third-party independent software vendors to create either Microsoft Windows or Web Reporting applications.

28. What is Rendering?
Rendering is a process of converting the report layout and report data into a specific file format.You can render reports into different formats such as HTML or Excel spreadsheets or PDF files.

29. What is the Reporting Life Cycle?
The Reporting Life Cycle is the sequence of activities associated with a report from creation to delivery.There are three phases of the reporting life cycle including report development, management of the report server, and report access by users.

30. What is Report Development?
Report Development is the process of developing reports whether they are Managed Reports or Ad Hoc Reports.

31. What is Report Administration?
Report Administration is the process of managing the technical environments for reports and their reporting platform. Administrators or Power Users have to manage the location, security, and execution properties of reports.

32. What is Report Access?
Report Access is the process of users accessing reports whether from the Report Server or a shared network folder. The most common way for users to access reports is to use a browser and navigate to a central report repository. As another alternative, you can create your own portal application with links to guide users to reports in Reporting Services.

33. What are the Preparation Tasks for installing Reporting Services?
Before you can begin installing Reporting Services, you must make several decisions and perform a number of preparation tasks:
1.Understand the features supported in each of the six editions so you can choose the one most appropriate for your reporting needs
2.Decide whether to implement Reporting Services in native mode or SharePoint integrated mode and whether you should deploy it in a single-server or multi-server topology.
3.Review the hardware and software requirements for Reporting Services to ensure that the installation completes successfully.
4.Consider whether you need to create Microsoft Windows accounts or whether you want to use build-in accounts for use as service accounts.

34. What editions of SQL Server 08 include Reporting Service?
Reporting Services is included in all editions of SQL Server 08 except for SQL Server 08 Express and SQL Server Compact.

35. Does Reporting Services vary from editions of SQL Server?
Each edition of SQL Server supports a different set of features to meet specific scalability, performance, and pricing requirements.

36. What are the Server Modes for Reporting Services?
Two server modes are available for Reporting Services: native mode and SharePoint integrated mode. The server mode you select is closely connected to the structure and usage of the report server databases so if you change modes you must create a new database.

37. What is SharePoint Integrated Mode?
In SharePoint integrated mode, the report server runs in a Windows Sharepoint Services or Office SharePoint Server server farm. With this, you can locate, view, manage, and secure reports in the library.

38. What are the benefits of SharePoint Mode?
Office SharePoint Server provides several additional business intelligence features that enable you to integrate reports into dashboard pages using a Report Viewer Web Part with SharePoint filter Web Parts.

39. What are the limitations of SharePoint Mode?
There are two important limitations with SharePoint integrated mode. It does not support linked reports nor does it support performing administrative tasks such as report deployment in batch mode using the RS utility.

40. What kinds of accounts can Reporting Service be run under?
The Report Server Service can be run under a built-in service account, a Windows account on your local computer, or in your network domain.

41. What is the Reporting Service Configuration Manager?
Reporting Services Configuration Managers allows you to manage ALL the different settings and configurations of your Reporting Services instance in one GUI based application.

42. What does the RSCM consist of?
The RSCM consists of many tabs:
· Report Server Status
· Service Account
· Web Service URL
· Database
· Report Manager URL
· E-Mail Settings
· Execution Account
· Encryption Keys
· Scale-Out Deployment
43. What is the Report Server Database Configuration Wizard?
The Report Server Database Configuration Wizard allows you to change the settings of the database that holds the Report Server. With it you connect to the database server with your credentials. Then, you can change the Database Name, language, and Report Server Mode (Native or SharePoint).

44. What is a Shared Data Source?
A Shared Data Source is a data source connection string that is shared by all the reports in the project.

45. What is a Data Set?
Query used to retrieve the data to be shown in the report.

46. What are Shared Data Sets?
Share Data Sets are data sets which can be used by multiple reports.

47. What are Parameterized Reports?
Parameterized Reports are reports that include parameters in them. These parameters can be applied to different functionalities. Such as using the parameters as a search predicate for your report table. Also, you can use a select statement to populate these parameters with “available values”. In the report viewer, the user will see the parameter as a dropdown box that pulls available values from the select statement.

48. What are Cascaded Parameterized Reports?
Cascaded Parameterized Reports are reports that use multiple parameters in them.
However, some parameters will depend on other parameters for their values or value sets. The order of parameters can change the optimization of the report. For example, if we have a customer parameter that has million results it can be filtered by first having the gender parameter, which will cut the customer parameter results in half. For example, we have a subcategory parameter but its values aren’t populated until the category parameter is chosen.

49. What are Drill Down Reports?
Drill Down Reports are reports that have hierarchies within their rows or columns. They take advantage of expanding pivot tables to give the user aggregated data that can be expanded to show greater detail.

50. What are Drill Through Reports?
Drill Through Reports are reports which link objects to specific actions.This makes the report more interactive because the user can click on an object such as a table field and open another report which elaborates on that specific value.

51. What are Sub Reports?
Sub Reports are reports that are embedded in another report. The parent report pulls the sub report and inputs any parameters if needed.

52. What are Map Reports?
Map Reports take spatial data and formats it into a visual map. For example, if we have data for sales based on state. We can make a map report which visualizes the sales by state on a US map and colors the states based on sales amount.

53. What are Cube Reports?
Cube Reports are reports where the data source is a SQL Server Analysis Services Cube. The source connection is made to the Analysis Engine and the data set is taken from a MDX query on the cube.

54. What are KPI Reports?
KPI Reports are reports that use KPIs (Key Performance Indicators) in order to give the user a visual representation of the progress of the business.

55. What are Report Models?
Report Models are the base structure of Ad Hoc reports. With report models you just choose a data source and a data source view (similar to the SSAS GUI). From this model the end users can create their own custom reports by using the Report Builder.

56. What can you do with Report Model’s Data Source Views?
The Data Source Views in Report Models work the same way as the DSVs in SSAS projects. With them, you can create derived columns based on stored columns. Create entirely new derived tables. All of these tables and columns can be referred to by the end user when using the model in Report Builder. But they are not stored in the database used as the data source.

57. Can you create Report Models from Cubes in BIDS?
You cannot generate a model from your OLAP systems in BIDS. But if you have to use your Cube as a source to your Ad Hoc reports you can use your Report Manager to generate a SMDL file that you will feed to the report builder to create Ad Hoc reports.

58. What is the difference between Report Server and Report Manager?
Report Server is the web service application that functions as your programmatic gateway to the SQL Reporting Services engine. When browsing to the Report Server, you will be shown the contents of your individual Reports directory.

Report Manager is a convenient web user interface to the Report Server that enables you to view and administer your reports, data sources, and report resources.

59. What are Roles?
Roles provide Report Item Level security. With roles, you can grant or deny access to specific functionalities of SSRS, Report Server, and Report Manager.

60. What are the different purposes of Roles?
We can assign roles to users with different levels of access.
Such as:
· Content Manager
· Browser (read only)
· Publisher
· Report Builder
· My Reports (manager for a specific folder)

61. What are the different Security levels?
The different server/system security levels include:
· Roles
· Administrator
· System User

62. What are Linked Reports?
Linked reports are just shortcuts to a stored report. They allow a user to view the report without accessing the underlying folder or files.

63. What are Report Snapshots? A report snapshot is a report that contains layout information and a dataset that is retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select them, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database, and shows the data and layout that were current for the report at the time the snapshot was created.

64. What is the process of Authorization?
After authentication, the process of authorization grants permissions to the user to access content on the report server and to perform specific actions. In native mode, Reporting Services uses a role-based authorization system to control what users can see and do on the report server.

65. What are Report Caches?
If the report has to retrieve a large amount of data or multiple users are accessing the report frequently then a Report cache would increase its efficiency. Caching reduces the burden on the server’s resources. We use the cache report functionality to store the report execution in the cache for a desired period of time and all the user requests will be addressed by the Cache. You would use a cache when you are dealing with an excessive amount of data or there are multiple users accessing the files.

66. What are things to consider before Caching, Snapshots, or Subscriptions?
Before you schedule any caches, snapshots, or subscriptions,
Make sure:
· SQL Server Agent is running
· There are proper Execution Accounts
· Account Credentials are stored in the report server
· The schedule properly follows the requirements

67. What are the different ways to deliver Reports?
There are many different ways to deliver a report such as via email, shared folders, or subscriptions.

68. What are the differences between Push and Pull Deliver Methods?
With the Pull delivery method, the user is accessing the file from the Report Manager themselves. With the Push delivery method, the reports are automatically delivered to the user.

69. What are Subscriptions?
Subscriptions are a functionality with SSRS that pushes or delivers reports to multiple users on an automatic and scheduled basis.

70. What are the types of Subscriptions?
The types of subscriptions include:
· Standard Subscriptions-The report is delivered to multiple users in a single rendering format and the recipient information is hardcoded
· Data Driven Subscriptions- The report is delivered to multiple users in multiple rendering formats and the recipient information is not readily available. The recipient information is in the data source where it must be retrieved.

71. How do you change the security of Rendering File Types?
You can allow or deny users to use a specific rendering file type.
You can do this manually by opening this config xml file:
C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config. And changing the render file types to VISIBLE=”FALSE”

72. What is the purpose of ReportServer Database?
It stores report metadata and snapshort reports.

73. What is the purpose of the ReportServerTempDb?
It stores cached reports

74. What is involved in report Authoring/Development?
In report authoring/development, you gather the requirement for the report, get the source of data and how to retrieve it, Then implement or create the report requested.

75. What is involved in management of reports?
In report management, different optimization techniques are employed to speed the performance of the report, security is provided to ensure safety of the report and access level control and deployment techniques are laidd out to clearly state how the report will be moved to the environment where it will be accessible to users or other developers.

76. What is involved in report delivery?
Report delivery is the method of making report accessible to the clients. The delivery can be in different formats such as via email, subscriptions or storing the report on a shared folder on the network accessible to the clients.

77. Which component is used in creating a managed report, Adhoc Reports and Embedded Reports?
Managed Reports are created using Report Server Projects in BIDs, Ad hoc Reports are created with Report Model Project in BIDS and Embedded Reports are created with Report Server Project in BIDs.

78. What is a Report Builder?
It is a tool that comes with SQL Server with a Microsoft office like interface that allows non technical or technical people to quickly create ad hoc reports from a report model. The report model is created in BIDs using the report model project and deployed to the report server. Users can then load the report model into the Report Builder tool from the report manager web application interface and create custom reports, which can either be stored on the local machine or saved on the report server.

79. What is the difference between report server, report manager, report designer and report builder?
Report server is the gateway to the ReportServer database to graphically see the reports in a web from. A report manager is a gateway to the Report engine to manage and administer the reports such as providing security, delivery and subscriptions.

80. What is a report?
It is a structured arrangement of information that helps business and organizations to quickly view activities for better decision making.

81. What is report life cycle?
The report life cycle is the sequence of activities associated with a report from creation to delivery. Reporting Services fully supports the three phases of the report life cycle, which include report development, management of the report server, and report access by users.

82. What is report development?
You develop reports on your own computer by selecting data for the report, organizing the report layout, and enhancing the report with formatting and, optionally, interactive features. At any time during report development, you can preview the report to test its appearance and any interactive features you added. If you’re building a managed report, you can deploy the report from the authoring tool to the report server or to a Microsoft Office SharePoint Server 2007 Web site. If you’re building an ad hoc report instead, you can choose to store it on your computer, but you also have the option to deploy it to the report server or a SharePoint site.

83. What is Report Administration?
Administrators manage the technical environment for the reporting platform. Before reports can be deployed to the report server and accessed by users, an administrator must configure the report server and optionally integrate the report server with SharePoint. Occasionally, an administrator might reconfigure the report server to fine tune its performance. Administrators also manage the location, security, and execution properties of reports, although you might also delegate these responsibilities to some power users. Report developers can deploy reports directly to the report server if given permission to do so, or they can provide reports to an administrator to upload directly to the report server or to deploy in batch using a script utility. After a report is deployed to the report server, as an administrator, you can place the report in a folder with other related reports. You can then apply security to the report or the folder containing the report to control who can view the report and who can modify report properties.

84. How do we access the reports?
The most common way for users to access reports is to use a browser and navigate to a central report repository. As another alternative, you can create your own portal application with links to guide users to reports in Reporting Services. A user can also optionally store a selection of reports in a personal folder for easy access or can create a subscription to a report to receive it on a scheduled basis in an e-mail inbox, a network file share, or a SharePoint document library. By default, you view a report rendered in HTML format, but you can instead render it to a PDF or Tagged Image File Format (TIFF) format to share your report in printed form or to Comma Separated Values (CSV) or Extensible Markup Language (XML) format to import the report data into other applications. You can also request a specific format for subscription delivery of reports. Another way that you might access Reporting Services reports is through corporate applications. Reports might be embedded into applications developed by your organization or by third-party vendors

85. What consists of the data tier in SSRS?
The data tier in the Reporting Services architecture consists of a pair of databases to support the reporting platform. The ReportServer database is the primary database for permanent storage of reports, report models, and other data related to the management of the report server. The ReportServerTempDB database stores session cache information and cached instances of reports for improved report delivery performance. In a scale-out deployment of Reporting Services across multiple report servers, these two databases in the data tier are the only requirements. These databases do not need to be on the same server as the report server.

86. What is a Report Manager?
To view a report on a server running in native mode, you use a Web application called Report Manager to locate and render the desired report for online viewing. You can page through a large report, search for text within a report, zoom in or out to resize a report, render a report to a new format, print the report, and change report parameters using a special toolbar provided in the report viewer.

87. Report Viewers
Reporting Services provides three ways to view reports: Report Manager, SharePoint, or a programmatic interface. Within a single instance of Reporting Services, you can use either Report Manager or SharePoint as a standard user interface, but you cannot use both tools in the same instance. Whether Reporting Services runs in native mode or SharePoint mode, you can use a programmatic interface in addition to or instead of the standard user interface.

88. How does Sharepoint report viewer work?
In SharePoint Integrated mode, you can navigate to a SharePoint document library to locate and render a report much as you can when using Report Manager. A Web Part is also available for embedding a report into a SharePoint Web page, such as a Whether you open the report in a document library or in a Web Part, the same capabilities to page, search, zoom, render, print, and select parameters available in Report Manager are available in SharePoint.

89. How does programmatic report view work?
You can integrate report viewer functionality into a custom application by using the Reporting Services API or by accessing reports using URL endpoints. You can also extend standard functionality by customizing security, data processing, rendering, or delivery options.

90. What are the processor components of a report server?
Activity on the report server is managed by two processor components: the Report Processor and the Scheduling and Delivery Processor. The Report Processor receives all requests that require execution and rendering of reports. The Scheduling and Delivery Processor receives all requests for scheduled events such as snapshots and subscriptions.
91. What is a Server Tier in Reporting Services?
The server tier is the central layer of the Reporting Services architecture. Within this tier lie the processor components that respond to and process requests to the report server. These components delegate certain functions to subcomponents called server extensions, which are simply processors that perform very specific functions. These components are implemented as a Windows service.

92. What are the Server Extensions in reporting services?
The server extensions in Reporting Services are the processor components that perform very specific tasks. This modular approach within the server tier allows you to disable an out-of-the- box extension or add your own extension, whether developed in-house or by a third party. Reporting Services includes five types of server extensions: authentication, data processing, report processing, rendering, and delivery.

93. What are drill through and drill down reports?
Drill through reports are reports that have links which when clicked will navigate to a location with detailed information about the link clicked. Drilled Down reports are type of reports that give grouped view of the report initially and the user have the option to expand the grouped view to get to detail view of each grouping.

94. How is security assured on the server?
Security in SSRS is assigned through a set of roles which has a set of tasks assigned to each role. Security can be provided at two levels; System Level Security (System Administrator)& Item level security(Browser, Content Manager, My Report, Publisher, Report Builder)

95. Describe the task for each security type
Browser:- Can view reports, folders and subscribe to reports
Content Manager:-May manage content in the report server. This includes folders, reports and resources.
My Reports:- May publish reports and linked reports, manage folders, reports and resources in a users my report folder.
Publisher – may publish reports and linked reports to the report server
Report builder – may view report definitions in report builder

96. What are the rendering formats supported by ssrs?
Rendering Format:PDF,XML,CSV,Word,MHTML,TIff

What are report subscription types?
Standard Subscription:- The receipient information is available and all users will receive a single rendering format of a report either in email or windows file share.
Data Driven Subscription
The recipient information is not readily available. The reciepient information would be in the underlying database and we can deliver multiple reports in multiple reindering formats to various users.

97. What are the Report delivery types?
Report Delivery(Push and Pull Methods)
In pull delivery, the report is deployed to the centralized report server and users access the reports on demand.