SSRS Interview Questions And Answers Pdf
1. What is a Managed Report?
Answer: 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.
2. What is Subreport?
Answer: A subreport is a report that displays another report inside the body of the main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.
Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with the main report as a briefing book or as a container for a collection of related reports. For reports with many instances of subreports, consider using drillthrough reports instead.
3. What is Tablix?
Answer: A Tablix can be seen as control with combined capabilities of a table and a matrix, which facilitates asymmetric and flexible, row and column level, static and dynamic groupings.
4. What is an Ad Hoc Report?
Answer: 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. Users can choose whether to save them privately or to share them with others by publishing to the Reporting Services centralized store.
5. What is Report Access?
Answer: 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.
6. What are the most important aspects of Reports for the end-user?
Answer: Accuracy and Performance. Business decision makers want accurate reports so they can correctly analyze the data and get accurate answers to their questions.
7. What editions of SQL Server 08 include Reporting Service?
Answer: Reporting Services is included in all editions of SQL Server 08 except for SQL Server 08 Express and SQL Server Compact.
8. Explain what is a sub-report?
Answer: Sub-report are inserted into the main part, just like the main report, you also pass queries and parameters to it. In other words, a sub-report can be considered as an extension to your main report, but it consists of a different data set. For example, you can prepare a report of customers and then use a sub-report to show a list of orders for each customer.
9. Can you create Report Models from Cubes in BIDS?
Answer: 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 an SMDL file that you will feed to the report builder to create Ad Hoc reports.
10. You Want To Configure An Amount To Display The Value In A Currency Format. Which Property Do You Use?
Answer: To configure an amount to display a value in a currency format, select the report item, and then set the format property to C or c.
11. What Are Data Regions?
Answer: Data regions are report items that display repeated rows of summarized information from datasets.
12. What is the Query parameter in SSRS?
Answer: Query parameters are mentioned in the query of the data sources that are to be included in the SQL script’s WHERE clause of the SQL that can accept parameters. Query parameters begin with the symbol @. The name should not contain spaces and can not begin with a numeral. For clarity, we use only letters.
13. What is the difference between the Report Server and Report Manager?
Answer: 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.
14. What new data source types were added in SSRS 2014?
Answer: In addition to the data source types available in SSRS (SQL Server, Oracle, ODBC, OLE DB), the following have been added in SSRS 2012: SQL Server Analysis Services SQL Server Integration Services SQL Server Report Builder Models XML (through URL and Web services)
15. Explain SSRS Architecture?
Answer: Reporting services architecture comprises of integrated components. It is a multi-tiered, included with application, server and data layers. This architecture is scalable and modular. A single installation can be used across multiple computers. It includes the following components: –
Report Manager, Reporting Designer, Browser Types Supported by Reporting Services, Report server, Report server command-line utilities, Report Server Database, Reporting Services Extensibility, Data sources that are supported by Reporting Services.
16. How does the report manager work in SSRS?
Answer: The report manager is a web application. In SSRS it is accessed by a URL. The interface of this Report manager depends on the permissions of the user. This means to access any functionality or perform any task, the user must be assigned a role. A user with the role of full permissions can entire all the features and menus of the report. To configure the report manager, a URL needs to be defined.
17. What are the Roles?
Answer: Roles provide Report Item Level security. With roles, you can grant or deny access to specific functionalities of SSRS, Report Server, and Report Manager.
18. What is Report models?
Answer: 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.
19. What are ReportServer and ReportServerTempDB?
Answer: Reporting Services uses two SQL Server databases for storage by default, the databases are named ReportServer and ReportServerTempdb.
ReportServer is the main database, which stores all internal configuration and reports metadata whereas ReportServerTempdb is used to store temporary
data, session information, and cached reports.
20. What are the different purposes of Roles?
We can assign roles to users with different levels of access.
- Content Manager
- Browser (read only)
- Report Builder
- My Reports (manager for a specific folder)
21. What are the different Security levels?
The different server/system security levels include:
- System User
22. What are the drawbacks of reporting in SSRS?
Answer: For many years, Microsoft had no direct solution for reporting with the SQL Server besides Crystal Reports. Now, they have SQL Server Reporting Services, but it does have several drawbacks. It is still complex to understand the complete functionality and structure of this new component, and many users are still relying on the reporting application they are more familiar with, which is Crystal Reports. Also, components in SSRS like Report Builder and Report Designer are meant for different users for different aspects of the reporting process, yet complete understanding and exposure to both is important to utilize both functions fully and extensively. There are also issues when exporting very large reports to Microsoft Excel, as it can lead to a loss of data. (E learning Portal)
23. How does Report Builder support Analysis Services cubes?
Answer: Report Builder supports relational SQL and Analysis Services data sources in SQL Server. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.
24. What are Click through Reports?
Answer: A click-through report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a click-through report is opened. These field settings cannot be changed in the report authoring tools. Click through reports are auto-generated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead. The custom report is a standard Reporting Services report.
25. What is Data Set in report ?
Answer: Data set is a set of data that we want to show in the report. A data source is the source of data from where we are getting this data (database server name, database name, connection string).
26. Are there issues when exporting SSRS reports into Microsoft Excel? When my users are trying to export an SSRS report into Microsoft Excel, one or two columns in the report appear to merge together. Why might this be?
Answer: Exporting from SSRS is not always perfect, even if you stay within the Microsoft range of products. If you have extra resources, you could splurge for an add-on that offers much better control over exporting to Excel, such as OfficeWriter. From my experience, though, it is usually headers or footers that cause exporting issues. If any of these headers or footers overlap with data columns in your report, you will find that the exported version of the report has merged cells. Also, check columns next to each other to make sure that there is no overlap, as well.
27. What are Linked Reports?
Answer: Linked reports are just shortcuts to a stored report. They allow a user to view the report without accessing the underlying folder or files.
28. 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.
29. What is the subreport?
Answer: Sub Reports are on kind of child report which opens in the main report when the main report loads. We can pass a parameter to the subreport.
Can sub report data source be different from that of the parent report?
30. What are the different types of data sources in SSRS?
SSRS uses a different data source. Some of them are listed below:
1. Microsoft SQL Server
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
31. What are Report Caches?
Answer: 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.
32. What is the Reporting Life Cycle?
Answer: 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.
33. Mention what are the reporting service components in SSRS?
The reporting service components in SSRS includes
Report Designer: It is a place where the report is created or designed
Report Server: It provides services for delivery of reports and for implementations
Report Manager: A web-based administration tool to manage the report server
34. What Programming Language Would You Use To Create Embedded Functions In Ssrs?
An SSRS report supports only visual Basic .nET embedded code.
35. What Is The Chart In Report?
Answer: Chart reports are for graphical representation. You can get pie charts columns harts and various other options. 3D charts are also available in reporting services.
36. Can We Use Data-grids For Our Report In Ssrs?
Answer: We have an ASP.NET project that populates a data-grid. Using data-grid as my data-source for my report using SQL Server Reporting Services.
37. Does Reporting Services vary from editions of SQL Server?
Answer: Each edition of SQL Server supports a different set of features to meet specific scalability, performance, and pricing requirements.
38. What are Drill Through Reports?
Answer: 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.
39. How to backup the encryption key?
Answer: Encryption Keys backup, use SQL Server Reporting Services Configuration tool to backup symmetric keys.
40. What are the key configuration files for SQL Server Reporting Services?
Mostly all Configuration files located at Install Directory:
Microsoft SQL ServerReporting ServicesReportServer and ReportManager
RSReportServer.config stores configuration settings for feature areas of the Report Server service: ReportManager, the Report Server Web service, and background processing.
RSSrvPolicy.config stores code access security policies for the server extensions.
RSMgrPolicy.config stores code access security policies for Report Manager
ReportingServicesService.exe.config stores configuration settings that specify the trace levels and logging options for the Report Server service.
RSReportDesigner.config contains settings for Report Designer and this file is located in the.Program FilesVisual Studio 9.0Common7IDEPrivateAssemblies
RSPreviewPolicy.config stores server extensions used during report preview and this file is located in…Program Files Microsoft SQL Server100Tools ReportDesigner.
41. What Is The Main Benefit Of Using Embedded Code In A Report?
Answer: The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.
42. Is SSRS support another database except for MS SQL Server?
Answer: Yes. SSRS can be building based on relational or multidimensional data sources like Oracle, OLEDB. ODBC etc
43. Can You Use Data Mining Models In Ssrs?
Answer: Yes, you can use the DMX Designer to create data mining queries for SSRS reports. However, do not forget to flatten the result set returned by the DMX query.
You Want Your Report To Display A Hyperlink That Will Take Users To Yours.
44. Would you store your query in an SSRS report or a Database server? State the reason why ?
Answer: Storing SQL queries directly in text format in the dataset, should be avoided. Ideally, it should be stored in a stored procedure in the database server. The benefit is that the SQL would be in a compiled format in an SP and brings all the benefits of using an SP compared to using an ad-hoc query from the report.
45. Have you used the Report Viewer control/web part? What are the limitations?
Answer: The report viewer control/web part is the basic control to integrate SSRS reports with external applications. Using these interface applications can link and display SSRS reports within the application. The parameters toolbar in these controls have a limited programmable interface to decorate and blend it with the theme of the application.
46. What are the different ways to deliver Reports?
Answer: There are many different ways to deliver a report such as via email, shared folders, or subscriptions.
47. What is the name of the reporting services config file and what’s it’s used for?
Answer: Reporting service config file is used for report configuration details. It contains the report format and also the report import types. Report service config resides at ISS.
48. What is Report Builder?
Answer: Report Builder is an ad-hoc report authoring tool primarily targeted to be used by business analysts to facilitate self-service report authoring. Report Builder 3.0 is the latest version available as of date.
49. List Box. How Should You Configure The Parameter?
Answer: You should create a data source that contains the possible values and then bind the data source to the parameter.
50. What is Linked Report?
Answer: A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.
A linked report is derived from an existing report and retains the original’s report definition. A linked report always inherits the report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.
You can create a linked report on the report server when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.
Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings.