Q1 : Explain SSRS Architecture?
A : 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 is supported by Reporting Services.
Q2 : What is SSRS?
A : SSRS or SQL Server Reporting Services is a server-based reporting platform that gives detailed reporting functionality for a variety of data sources. Reporting services include a complete set of tools to manage, create and deliver reports and APIs that allows developers to synchronize data and report process in custom application.
Q3 : Difference between Logical Page an Physical Page in SSRS.
A : Logical page breaks are page breaks that you insert before or after report items or groups. Page breaks help to determine how the content is fitted to a report page for optimal viewing when rendering or exporting the report. The following rules apply when rendering logical page breaks: Logical page breaks are ignored for report items that are constantly hidden and for report items where the visibility is controlled by clicking another report item. Logical page breaks are applied on conditionally visible items if they are currently visible at the time the report is rendered. Space is preserved between the report item with the logical page break and its peer report items. Logical page breaks that are inserted before a report item push the report item down to the next page. The report item is rendered at the top of the next page. Logical page breaks defined on items in table or matrix cells are not kept. This does not apply to items in lists.
Q4 : How does the report manager work in SSRS?
A : 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 a 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.
Q5 : What are the important architecture components of SSRS?
A : Important architecture components of SSRS includes
- Report Manager
- Report Designer
- Browser types supported by reporting services
- Report Server
- Report Server and Command line utilities
- Report Server Database
- Data Sources
Q6 : Can you import Microsoft Excel data to SSRS?
A : Reporting Services does not import data. It only queries data in whatever format it is stored in their native storage system. I will assume that you’re asking whether you can create reports and use Excel spreadsheets as data sources. The answer is Yes, Reporting Services supports a wide variety of data sources, including Excel files. You’ll get the best performance with the built-in native .NET providers but you should be able to connect to any ODBC or OLE-DB data source, whether it comes from Microsoft or a third-party company.
Q7 : Can we use data grids for our report in SSRS?
A : 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. Is this possible? The simple answer is no. However, nothing’s ever simple. A set of reporting controls was added in Visual Studio 2010 allows you to report in a dataset, on data that was supplied by you. So, if you retrieved your data into a dataset, bound the datagrid to the dataset so it had data to display, you could then use that dataset as the datasource for the reporting controls. These are then client-side reports, not server reports though.
Q8 : What are the different stages of Report Processing?
A : Different stages of Report Processing includes
- Compile: It analyzes expressions in the report definitions and saves the compiled intermediate format internally on the server
- Process: It run dataset queries and combines intermediate format with data and layout
- Render: It sends a processed report to a rendering extension to tell how much information fits on each page and create the page report
- Export: It exports the reports to a different file format
Q9 : What are the drawbacks of reporting in SSRS?
A : 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 report 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.
Q10 : How to send a SSRS report from SSIS?
A : Often there is a requirement to be able to send a SSRS report in Excel, PDF or another format to different users from a SSIS package one it has finished performing a data load. In order to do this, first you need to create a subscription to the report. You can create a SSRS report subscription from Report Manager. At the report subscription you can mention the report format and the email address of the recipient. When you create a schedule for the SSRS report, a SQL Server Agent Job will be created. From the SSIS, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.
Q11 : What is encryption key?
A : Encryption keys are used by the report server so that items such as connection strings are maintained securely. These keys are required in case you want to perform restoration of report server databases.
Q12 : Mention what are the new features in SSRS?
A : New features in SSRS includes
- Excel File Export: The files can be exported into Excel file formats, earlier only XLS files were only exported
- Data Alerts: The new data alerts allow to create alert threshold which are evaluated on a user defined schedule, also there is data alert manager for alerting administrators
- Power View: With the new RDLX file format, power view is a new interactive Business Intelligence feature
Q13 : What are the core components of SSRS?
A : The core components of SSRS includes
- A set of tool to View, Create and Manage report
- A report server component that hosts and processes reports in a different formats like PDF, HTML, Excel, CSV,
- An API, which enables developers to integrate with custom applications or to create custom tools to manage or build reports.
Q14 : Explain can you implement data mining in SSRS?
A : Yes, it is possible to implement data mining in SSRS by using DMX designer to create data mining queries for SSRS reports.
Q15 : What is the difference between Tabular and Matrix report?
A :
- Tabular Report: Tabular report is the most basic type of report. Each column relates to a column chosen from the database
- Matrix Report: A matrix report is a cross-tabulation of four groups of data.
Q16 : Explain how would you store your query in an SSRS report or a Database server?
A : Storing SQL queries directly in text format in the data should be avoided. Instead, it should be stored in a stored procedure in the database server. The advantage is that the SQL would be in a compiled format in an SP and gives all the benefits of SP compared to using an ad-hoc query from the report.
Q17 : What is Report Builder?
A : Report Builder is a business-user, ad-hoc report design client that allows users to design reports based on the business terms (Report Builder model) they are familiar with, but without needing to understand database schemas or how to write SQL or MDX queries. Report Builder works with both SQL Server and Analysis Services data sources.
Q18 : What new data source types were added in SSRS 2014?
A : 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)
Q19 : What method you can use to reduce the overhead of Reporting Services data sources?
A : Cached reports and Snapshots can be used to reduce the overhead of Reporting Services Sources.
Q20 : How you can configure a running aggregate in SSRS?
A : To configure a running aggregate in SSRS, you can use the Running Value function.
Q21 : How to finetune Reports?
A : To tune-up the Reporting Services, follow the below mentioned ways: – Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report application’s logic and characteristics can have a duplicate copy of data. – Replication of data continuously. Using nolock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable.
Q22 : What are Data Driven Subscriptions?
A : Reporting Services provides data-driven subscriptions so that you can customize the distribution of a report based on dynamic subscriber data. Data-driven subscriptions are intended for the following kinds of scenarios: Distributing reports to a large recipient pool whose membership may change from one distribution to the next. For example distribute a monthly report to all current customers. Distributing reports to a specific group of recipients based on predefined criteria. For example send a sales performance report to the top ten sales managers in an organization.
Q23 : Explain Reporting Life Cycle?
A : The Reporting Lifecycle includes – Report designing – The designing is done in Visual Studio Report Designer. It generates a class which embodies the Report Definition. – Report processing – The processing includes binging the report definition with data from the report data source. It performs on all grouping, sorting and filtering calculations. The expressions are evaluated except the page header, footer and section items. Later it fires the Binding event and Bound event. As a result of the processing, it produces Report Instance. Report instance may be persisted and stored which can be rendered at a later point of time. – Report Rendering: Report rendering starts by passing the Report Instance to a specific rendering extension (HTML or PDF formats). The instance of reports is paged if paging supported by output format. The expressions of items are evaluated in the page header and footer sections for every page. As a final step, the report is rendered to the specific output document.
Q24 : Explain can SSRS reports Cache results?
A : Caching reports enables users to access and view reports much faster. SSRS does allow Cache reports on reporting server.
Q25 : How do users use Report Builder with SQL Server data sources?
A : While models that provide access to SQL Server Analysis Services are automatically generated on the report server, the Report Builder Model Designer can be used to generate or modify the models that are built on top of SQL Server relational databases. These model-building projects are a new type of project within a Visual Studio–based development shell.