SSRS interview questions and answers 👇

  1. SSRS General Interview Questions

SSRS General Interview Questions

Mention what are the important architecture components of SSRS?

View answer

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

Explain what data regions are and what are the different data regions?

View answer

Data regions are report items that show repeated rows of summarized information from datasets. Different data regions include:

  • Chart
  • Gauge
  • List
  • Matrix
  • Table

Explain what are the different stages of Report Processing?

View answer

Different stages of Report Processing includes:

Compile: It analyze expressions in the report definitions and save the compiled intermediate format internally on the server.

Process: It run dataset queries and combine intermediate format with data and layout.

Render: It sends 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.

Mention what are the new features in SSRS?

View answer

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.

These are the new features included, apart from these, if you are using SSR on SharePoint it gives additional benefits like drag and drop ad hoc reporting and sending e-mails when data changes.

Explain what is a sub-report?

View answer

Sub-report are inserted into the main part, just like a 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.

List out what other servers you can use with SSRS?

View answer

Most of the times companies use SQL server with SSRS, but there are other servers you can integrate:

  • Oracle
  • ODBC and OLEDB
  • Hyperion
  • Teradata
  • Flat XML files

Mention what are the core components of SSRS?

View answer

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.

Mention what are the different types of SSRS reports?

View answer

The different types of reports include:

  • Parameterized reports
  • Snapshot reports
  • Cached reports
  • Clickthrough reports
  • Drilldown reports
  • Drillthrough reports
  • Sub-reports
  • Linked reports
  • Ad-hoc reports

Name some of the open source software that you can use in alternative to SSR?

View answer

Some of the open source software alternative are

  • Jasper Reports
  • JFree Report
  • BIRT (Business Intelligence Reporting Tool)
  • OpenReport
  • DataVision, and so on.

Mention what are the reporting service components in SSRS?

View answer

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

Explain can you implement data mining in SSRS?

View answer

Yes, it is possible to implement data mining in SSRS by using DMX designer to create data mining queries for SSRS reports.

Explain how you can configure a running aggregate in SSRS?

View answer

To configure a running aggregate in SSRS, you can use the Running Value function.

Mention what is the main function of a query parameter?

View answer

The main function of a query parameter is to filter data in the data source.

Explain can SSRS reports Cache results?

View answer

Caching reports enables users to access and view reports much faster. SSRS does allow Cache reports on reporting server.

Mention what are the three command line utilities and what are their primary functions?

View answer

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

Explain how you can deploy an SSRS report?

View answer

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

Mention what method you can use to reduce the overhead of Reporting Services data sources?

View answer

Cached reports and Snapshots can be used to reduce the overhead of Reporting Services Sources.

Explain what is the difference between Tabular and Matrix report?

View answer

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.

Explain how would you store your query in an SSRS report or a Database server?

View answer

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.

How to send an SSRS report from SSIS?

View answer

Often there is a requirement to be able to send an SSRS report in Excel, PDF or another format to different users from an SSIS package once it has finished performing a data load. To do this, first, you need to create a subscription to the report. You can create an SSRS report subscription from the 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.

What are things to consider before Caching, Snapshots, or Subscriptions?

View answer

Before you schedule any (Selenium Training )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.

Can you import Microsoft Excel data to SSRS?

View answer

Reporting Services does not import data. It only queries data in whatever format it is stored in its 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 (Sap Fico Online Training )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.

What are the limitations in SSRS on Sql Server Express edition?

View answer

Microsoft offers reporting services free as part of SQL Server Express with Advanced 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 SharePoint.
  • Can not implement Role based security.
  • Only named instances is supported.
  • Scale-out Report Servers will not be available.

What are the export options of SSRS?

View answer

SSRS allow many ways of rendering the reports:

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

What are the different types of data sources in SSRS?

View answer

SSRS uses different data sources. Some of them are listed below:

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

What are the core components of SSRS?

View answer

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 Tablix?

View answer

In reporting services, the Tablix is a sum of tables with matrices. Every report we create using SSRS technology is based on the Tablix data region. In other words, It can be managed with the combined capabilities of a table and a matrix.

RDL files are written in which programming language?

View answer

It is an extension for XML used for SSRS reporting services. These files are written in XML (Extensible Markup Language).

What is the name of the Reporting Services Configuration file and where it exists?

View answer

Rsreportserver.config is the reporting services file and it can be found in settings in Report Manager or that used in Report Server Web Service and background processes.

What is an Ad Hoc Report?

View answer

Ad Hoc Reports enable users with limited technical skills to create new, easy to access reports on their own. Usually, these reports are created from report models. Also, users can select their priorities and requirements whether to save the reports to a personal server or to share with others by posting them to the reporting services center. Ad hoc reports can generate quick reports which are created to meet user needs and requirements. Users can modify these reports with a powerful analysis of report data.

Explain SSRS reports Cache results?

View answer

Cache results are based on the format of the report.SSRS allows cache reports on the reporting server, you will also find built-in caching capability. However, the server only caches for one instance of the report in most cases. It also enables users to access and view reports quickly.

What data type should be used when creating Radio Button Parameter Type in SSRS Report?

View answer

When using the bit-type column to add a query for your report, go to Parameter properties to set the data type to boolean. As it is to show Radio Buttons, Or else, a text box will appear for the parameter value.

How would you generate a Sequence Number for all the Records in SSRS Report?

View answer

Use the row number function to generate a sequence number for all the records in your SSRS report. You can do that by put in a new blank column to your Tablix and then click on the cell to pivoted to expressions and write expressions.

Will you store your query in an SSRS Report or a database server? Explain why?

View answer

The SQL queries should be stored in a stored procedure in a database server. As practices of storing SQL queries in text format are not considered to be good anymore and it should be avoided. By storing queries in SP to a data server SQL would be in an accumulated format while providing all the benefits of using an SP.

What are the command prompt utilities for SSRS? List out some and explain.

View answer

Following is the list of utilities of the command prompt for SSRS.

RSS Utility: Command ‘RS.exe’, is the command-line utility that supports SharePoint and Native development modes. That can perform many scripted operations related to SQL SSRS and also be used while publishing reports on the report server or move another report from the server.

Powershell cmdlets: Powershell is getting popular amongst power users as well as IT pros as their preferred language and CLI. CMdlets are important to interact with CLI, in PowerShell, most of them are likely to be written in 'C' and perform functions that return a .NET object. It supports only SharePoint modes, and it installs SSRS service and proxy servers while providing provisioning and managing of SSRS applications and proxies.

List of the most useful cmdlets in PowerShell:

  • Get-Command
  • Get-Member
  • Get-Help
  • Get-Process

Rsconfig utility: Rsconfig utility is a script host that is used to perform scripted operations such as Publish reports, create items in a report server database, also configuring and managing report server connection with report server database. The command file is ‘rsconfig.exe’, which only supports native developer mode.

RsKeymgmt utility: The command ‘rskeymgmt.exe’, it is an encryption key management tool used in database recovery operations. It supports only the Native development mode. Also used to back up, apply, recreate, and delete symmetric keys using the command line.

Explaining is the Multi-value parameter for SSRS Report?

View answer

The multi-value parameter enables users to enter and pass more than one query for the parameter while creating an SSRS report. In any report parameters that we use to filter out the data and extract useful information that is required for the current scenario. In the multi-value parameter, you can choose to enter either static values or we can get values from the databases.

What is mixed mode database security?

View answer

At the point when you install SQL Server, it's smarter to get a different SQL Server user name and password when signing in to the database server. As windows are not considered to be the most secured database security option. In SSRS you have the alternative to allow SQL Server to integrate with Windows or require your users to keep a different SQL Server user ID and password. Your reports will require their username and password key to run reports from SSRS.

Explain the minimum software requirements for the SSRS framework?

View answer

Here is the list of software required for the SSRS framework :

Operating System - Windows Server 2000, Windows XP, Windows Server 2003.

Processor - Intel Pentium 2.

Hard Disk - A report server, required approximately 50 MB space. For the .NET framework, it needs 100 MB of space. For a Report designer, it would be 30 MB and needs 145 MB for samples.

RAM - Needs a minimum of 256 MB RAM for the report server.

Database- Needs SQL server 2000 and a minimum service pack of 3.

Name the multiple roles offered by the SSRS.

View answer

The multiple roles offered by the SSRS include Browsers, Content Managers, My Reports, Publishers, and Report Builders etc.

Name different parts of RDL file and explain them in brief.

View answer

  • Data – This part contains the dataset that can be used for writing queries and it is always well connected with the data source.
  • Design – Here, you can design the report in the form of tables or matrix etc. You just have to drag and drop the information you wanted to use.
  • Preview – It is used to check the preview of a report after the run command is executed.