SSRS Interview Questions and Answers

SSRS stands for SQL Server Reporting Service. It is the SQL server based software systems that generate reports developed by Microsoft and it is part of MSBI. It is used for preparing and delivering interactive and variety of printed reports. It is administered through an interface that is web based. Reporting services utilizes a web service interface for supporting and developing of customized reporting applications. It can be competed with Crystal Reports and other business intelligence tools.

Microsoft BI contains the following tools:

SQL Server Analysis Services (SSAS)

SQL Server Integration Services (SSIS)

SQL Server Reporting Services (SSRS)

Performance Point Services (PPS

Report Manager: A Web-based administration tool for managing the Report Server.

Report Server: Provides services for implementation and delivery of reports.

Report Designer: A place where we can create report.

Programmatic interfaces: Provides access to SSRS functionality through SOAP and HTTP requests.

Report Processor: Facilitates a set of report generation operations from data retrieval to rendering. Report Processor invokes other components, such as data extensions to assist with report generation.

Data-processing extensions: Retrieves report’s data from a data source. Developers can develop additional custom data-processing extensions.

Command-line utilities: Three utilities, designed to assist with scripting of administrative tasks, installed automatically during the Reporting Services install.

Rendering extensions: Transform the report’s intermediate format (a combination of report’s layout and data) into a device-specific format, such as HTML. Developers can create new rendering extensions.

Report Server database: Stores report definitions, report metadata, report history, cached reports, snapshots, resources, security settings, encrypted data, scheduling and delivery data, and more.

Report Server temporary database: Stores intermediate processing products, cache, and data.

Scheduling and Delivery Processor: Monitors for events (such as timed subscription), collaborates with Report Processor to render a report, and delivery extensions to deliver scheduled reports to a location specified in the subscription.

Delivery extensions: Deliver reports to specific devices, such as email or a file system. Developers can create new delivery extensions.

Report Manager: Provides web-based report access and management capabilities. Default URL that invokes Report Manager is http://<server>/reports.

Report Model Designer: Generates report models for use in Report Builder.

Report Builder: Provides drag-and-drop, easy-to-use report design functionality. Report Builder is an ad hoc end-user report authoring and publishing tool executed on a client computer.

Report Designer: Allows developers to develop complex reports. Report Designer is a comprehensive report authoring and publishing tool, hosted in Business Intelligence Development Studio or Visual Studio.

Security extensions: Enable authentication and authorization of users and groups. Developers can (excluding SQL Server Express edition) create new security extensions.

SQL Server Management Studio: Provides administrators with Windows form-based, integrated environment to manage SQL Server components including SSRS. From the report management perspective, Management Studio has similar functionality to Report Manager, but provides additional capabilities, such as consolidated web-farm management.

Reporting Services Configuration Tool: Provide administrators with functionality to start and stop Report Server Windows service and reconfigure report servers. This is a Windows forms application.

WMI provider: Provides a set of WMI interfaces to manage settings of a Report Server and assists with SSRS instance discovery on a network.

Performance monitoring object: Provide a view of SSRS Windows and web service performance.

Reporting Services provides different types of roles those can be used to grant access to different report operations. Each role is set of related tasks. You can assign groups or individual users to roles to provide access to report server operations.

Browser Role: If user or group would like to view folders, reports and subscribe to reports, you can add them to Browser role.

Content Manager Role: If user or group need to manage contents in Report Server you can add them to Content Manager Role. They will be able to manager folders, reports and other resources.

My Reports Role: If user or group is added to this role, they will be able to publish report, create linked reports, manager folders, reports and resources in user’s My Report Folder.

Publisher Role: If we want our user/group to have published permissions, we can add user/group to this role and they will be able to publish report and linked report to report server.

Report Builder Role: If we have users who often need to view the definition of reports by using Report Builder, we can add those users in this role.

A matrix is a data region related to a report set. Matrix permits us to create crosstab reports with the report variables showing on rows and columns. It allows us to drag.

Data set are the set of data which we want to show in report. Data creates on data source. Data source is the source of data from where we are receiving this data i.e. database server and database name joining string.

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.

Data regions are report items that display repeated rows of summarized information from datasets.

Below list of available data sources for SRS are:

• Microsoft SQL Server
• Microsoft SQL Server Analysis Services – multi-dimensional and tabular
• Microsoft Azure SQL Database
• Microsoft SharePoint List
• Oracle
• SAP BW
• Hyperion Essbase
• OLE DB
• ODBC
• Teradata
• XML

Some SSRS reports are:

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

Query parameters are the parts of an SQL query that allow you to filter results. Parameters are contained in the SQL’s “where” clause. These parameters tell the SQL server which records you want to update, select, or delete. In other words, if it wasn’t for the where clause parameters, you would affect all records in your tables.

Drill-down is a mechanism of decomposing summarized information to a detailed level. Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report.

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.

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

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.

C

A subreport is a report that displays another report inside the body of a 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.

A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, 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.

Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it. Deferred rendering makes a snapshot portable. The report can be rendered in the correct format for the requesting device or Web browser.

Report snapshots serve three purposes:

Report history: By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.

Consistency: Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.

Performance: By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

Free and Open Source Tools:

• BIRT (Business Intelligence Reporting Tools)
• Clic Data personal
• Jasper Reports
• SpagoBI
• Pentaho
• JFreeReport
• OpenReport
• DataVision
• KNIME
• Seal Report
• Report server

Non-Open Source Tools:

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

SSRS allow many ways of rendering the reports:

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

RDL files are like the “language” for SSRS servers. However, RDL files are created in XML, so really the “language” of SSRS servers is XML. RDL files contain the queries and layout format for your reports.

There are mainly three things, which should be backed up as part of reporting services backup

Report Server Databases, which can be backed up by SQL server backup and restore method.

SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.

Encryption Keys backup, use SQL Server Reporting Services Configuration tool to backup symmetric keys.

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.

• ‘Direct’ and efficient reporting access to information residing in both Oracle and MS SQL Server databases.
• Faster (and therefore cheaper) production of reports on both relational and cube data.
• An easy to deploy centralised reporting infrastructure based on Microsoft Reporting Services.
• Faster delivery of information to the business, providing better decision support.
• Ability for the business to self-serve, edit and interact with information without having to rely on IT or IS resources.
• Simple pricing model tailored for both entry and enterprise level installations, allowing for inexpensive provision of Business Intelligence for the Masses and democratisation of information.
• No need for expensive specialist skills.
• The beauty is that the entire report and data source definition is stored as a simple XML file. This is the file the reporting engine uses to render reports. The elements and attributes required for defining a report format are fully documented. Further, you can even add your custom elements if you want to enrich available functionality. Most report writers available today never provided this functionality.
• XML based report definition allows you to directly design reports programmatically and render them. This was very difficult to achieve in currently available report writers.
• The default report designer is integrated with Visual Studio .NET so that you can create application and its reports in the same environment.
• The report designer eliminates the traditional bands very effectively. It provides three types of elements—Table, Matrix and List. Table is equivalent to the traditional report with header, footer, detail and groups. You can have multiple tables rendering different data arranged side by side!
• For each type of reporting element, you have to attach a dataset to it. Dataset is based upon data source.

Visual Studio / SSDT / BI Data Tools (Standard and Enterprise editions) contains a set of freely redistributable Report Viewer controls that make it easy to embed Reporting Services functionality into custom applications. Two versions of the Report Viewer exist, one for rich Windows client applications and one for ASP.NET applications.

To Control Report Data: By selecting required parameters values to filter the report data.

To Control Report Appearance -:use parameters to change report appearance using expression-based properties, including conditionally hiding report items and conditionally changing text color.

To Connect to Other Reports -: use parameters to link to drill through reports, sub reports, and linked reports.

To Select Specific Data Columns: parameters can be used to select specific columns of a table/matrix at run time.

Query parameters are added to a dataset query by way of the query designers or the Dataset Properties dialog box. After you create a query with parameters, Reporting Services automatically links query parameters to report parameters with the same name.

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.

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.

Create a data-driven subscription that uses the null delivery provider. When you specify the null delivery provider as the method of delivery in the subscription, the report server targets the report server database as the delivery destination and uses a specialized rendering extension called the null rendering extension. in contrast with other delivery extensions, the null delivery provider does not have delivery settings that you can configure through a subscription definition.

A data source is the database and tables where your data set comes from. You must define the data source when you create your database connection. Every data set needs a source from which to pull the data. Your reporting server can be SQL Server 2000, 2005, 2008, 2012, 2014, 2016 and 2017.

You can use the RunningValue function to configure a running aggregate.

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.

SSRS report can be deployed in three ways

By Visual Studio: You can directly deploy the report in Visual Studios through solution explorer, by declaring the report server URL in project properties at Target Server URL.

By Report Server: By browsing the report from the disk location of the server you can deploy the report to report server

By creating the Utility: Another option is to create customized utility to deploy the report

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

A Tablix can be seen as a control with combined capabilities of a table and a matrix, which facilitates asymmetric and flexible, row and column level, static and dynamic groupings.

SSRS is compatible with several file formats. When the user runs the report, the default format prints to the web browser. You can also save or export to HTML, Excel, CSV, Image, PDF and XML formats. The SSRS reporting software has buttons at the top of each report that the user can use to export these files.

Yes, you can use a stored procedure to provide data to an SSRS report by configuring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.

As with most Microsoft tools, SSRS includes a wizard that helps you create reports. The wizard is great for people who are new to the product, but it’s not good if you can’t go beyond the tools and create a report from scratch. Before you go for your interviews, make sure you can create a report from scratch without any wizards or tools to create them for you. Most companies need someone who can customize reports, and wizards leave room for very little customizations

When you install SQL Server, you have the option to allow SQL Server to integrate with Windows or require users to have a separate SQL Server user name and password. While Windows integration is convenient, it’s not considered the most secure of the two security options. Instead, it’s better to require a separate SQL Server user name and password when logging in to the database server. Your reports will need their own user name and password to run reports from SSRS.

Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is called as cache and the process is known as caching.

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

The main purpose of a report parameter is to add interactivity to your reports, letting users

The three command line utilities include

RsConfig.exe: It is used to determine the connection properties from the SSRS instance to the Report Server database

RsKeyMgmet.exe: It executes scale out deployment set-up and encryption key operations

Rs.exe: It executes Report server Script files which can perform management operations and report deployment

To enhance the custom codes in report go to report tab on top then properties and there you will find the selections for custom code.

Reporting Service Web Service used in SSRS. By retrieving this web service we can access all report server section and also get the report organized on report server.