SSRS Interview Questions and Answers
What are some of the most common interview questions and answers centering on SQL Server Reporting Services (aka SSRS)?
Interview questions and practice tests are one of the quickest ways to evaluate one's knowledge on a subject area. In this tip, we have a list of SSRS Developer level interview questions that you can go through to refresh, evaluate and deepen your knowledge of SSRS development and take it to the next level. As improvements are being made to SSRS reporting tools, it is important to keep up with these changes to be sure you have the latest tools in your toolbox to develop the best business intelligence solutions possible.
SSRS Interview Questions
Following are some general and detailed questions about SSRS development to see if general working knowledge of the SSRS reporting lifecycle is known.
1) What are the different kinds of SSRS Reports?
Graphical reports can be categorized into operational and analytical reports. The distinction is based on the source of data and level of analysis facilitated by any particular report.
Operational reports are based on OLTP sources and are static reports and Analytical reports are based on OLAP sources (SQL Server Analysis Services) and generally facilitate drill-down and drill-through for analysis. Technically, SSRS reports can be categorized into parameterized reports, linked reports, snapshot reports, cached reports, etc. Furthermore, reports can be developed for regular SSRS, or they can be developed for and added to Power BI. Finally, reports can be distinguished as main reports and sub-reports. Sub reports pertain to reports that can be easily embedded within another report. Reports also can be for mobile viewing vs online viewing for increased interactivity by the users.
2) What are parameterized reports? What are cascading parameters in SSRS reports? Do issues exist when multi-select / multi-value parameters are allowed and used.
Reports that accept parameters from users to fetch and report data conditionally, are known as parameterized reports. When you have multiple parameters in a report and values of different parameters are dependent and populated dynamically based on the value of parent parameters, it's known as a cascading parameter. A tangent to cascading parameters are multi-value parameters which is a situation that allows multiple values to be selected (or all values) within a parameter selector.
- Create Dynamic SSRS Reports Using a Query as an Input Parameter
- Cascaded Parameters in SQL Server Reporting Services
- SQL Server Reporting Services Using Multi-value Parameters
- SQL Server Reporting Services SSRS Parameters
- Allow NULL value in Multi Value Report Parameter in SQL Server Reporting Services
3) How would you go about developing a SSRS report?
General development methodology for a SSRS report is to start by creating a data source. Based on the data source, the report designer will create one or multiple datasets as needed for parameters and for the body of the report. Next the report designer will add required controls from the toolbox which would act as a container for the fields in the dataset. Subsequently, formatting of controls must take place. Next the designer must verify and validate the report and finally deploy the report. It is often a good idea to follow specific best practices, so your report tells a story (and does so well) and performs optimally.
- SQL Server Reporting Services Best Practices for Performance and Maintenance
- SQL Server Reporting Services Development Tips
- SQL Server Reporting Services General Best Practices
- SQL Server Reporting Services Programming Language - Expressions Tips and Tricks
4) What is a dataset and what are the different types of datasets? How do these relate to a data source?
A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and can only be used by that report, and shared datasets can be shared across reports. If a dataset is shared, it must be published to the Reporting Service Server in order to be used across different reports and proper folder permissions need to be set in order to access shared datasets.
Whereas a dataset is query definition of the data that is sought, a data source is the "pipe" that is used to connect SSRS to the root location of the data, whether it be SQL Server, Teradata or a whole sundry of other sources. The data source also generally includes the credentials used to connect to the source.
- SQL Server Reporting Services SSRS Data Sets
- SQL Server Reporting Services SSRS Data Sources
- Implement Dynamic Data Sources in SQL Server Reporting Services
- SQL Server Reporting Services Report with Teradata Data Sources
- SQL Server Reporting Services XML File Data Source and Data Set
- Creating an SQL Server Reporting Services Matrix Sparkline Report with Data from Oracle
- Join Multiple SharePoint Lists to build SQL Server Reporting Services Report
5) Would you store your query in a SSRS report or a Database server? State the reasons why or why not.
The answer to this question has changed over the years as SSRS has matured. Previously storing SQL queries directly in text format in the dataset was generally suggested against. However, with the advent of shared datasets (see above), a single data set can be stored in SSRS and shared by many reports. Still, the ideal situation is to use a stored procedure on the database server. The benefit is that the SQL would be in a compiled format in a stored procedure and brings all the benefits of using an SP compared to using an ad-hoc query from the report. One caveat is if you are using multi-select parameters. Using a query embedded in a report or a shared dataset, allows the report designer to take advantage of the multi-value parameter being passed appropriately to a query whereas a passing a multi-value parameter to a stored procedures requires additional parsing of the passed value array within the stored procedure.
- SQL Server Reporting Services Using Multi-value Parameters
- Set Select All as Default for Multi-Value Report Parameters in SQL Server Reporting Services
- SQL Server Reporting Services Multi-Value Parameter - Wildcard Usage
- SQL Server Reporting Services Reports With Optional Query Parameters
6) Beside a chart or map visual, what are the 3 types of objects used to display data on a report and what differentiates each object. What is a tablix?
SSRS allows 3 main types of objects for displaying data:
- List - The list object displays data in a column format
- Table - The table object is similar to a list, yet it can be a multi-column
- Matrix- The matrix object acts similar to a pivot table with rows and columns groups
AA 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 in a data region. Ironically, you will notice if you add any of the 3 above objects, list, table, or matrix, that object becomes a tablix when placed on the design grid.
- Alternate Row Background Color in SQL Server Reporting Services Tablix and Matrix
- Dataset and tablix filtering in SQL Server Reporting Services
- SQL Server Reporting Services Matrix within a Matrix
7) How would you access SSRS reports deployed on report server? Can that interface be changed?
Reports Manager is the most straight-forward way to access SSRS reports deployed on report server. Apart from executing the reports, it is the main administration console for SSRS server to manage reports, permissions, administrative settings, and folder structure. The Report Manager portal can be customized with a set color scheme along with custom logos by adjusting the report server configuration. This situation allows the SSRS admin to brand the SSRS website with an organization’s specific branding.
8) Have you used the Report Viewer control / web part? What are the limitations?
The report viewer control / web part is the basic control to integrate SSRS reports with external applications including windows applications and webpages. 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.
- Integrate Reporting Services Using the Report Viewer Controls - Get Started
- Running a SSRS Report from a Windows Form Video
- SQL Server Reporting Services ReportViewer Control for Windows Applications
9) Which is the latest version of SSRS and what are the new enhancements?
The latest version of SSRS as of this writing is SSRS 2019. This version includes expanded support for publishing SSRS reports on Power BI, expanded support for Azure Managed Instances. Additionally, the SSRS install process has been separated from the main SQL Server install and is a separate download and installation.
- SSRS Install, Setup and Configuration
- Upgrade and Migrate SSRS Report Server and Retain Server Name
- Install SSRS ReportServer Databases on Azure SQL Managed Instance
- SSRS vs Power BI
10) What is Report Builder? What are the most recent enhancements to Report Builder?
Report Builder is a light weight, ad-hoc report authoring tool primarily targeted at business analysts to facilitate self-service report authoring. What is neat about Report Builder is that it has matured with SSRS and with the latest versions supporting the development of paginated reports which can be published to Power BI.
- Power BI Report Builder
- SQL Server Reporting Services Report Builder Tips
- SQL Server Reporting Services Report Builder with DAX Query Support
- SQL Server Reporting Services Report Builder Manual Data Sources
11) How would you deploy SSRS Reports using out-of-box functionality and how can you automate SSRS report deployment?
Visual Studio is generally used to deploy SSRS reports. From Visual Studio, a report (and datasets and data sources) can be developed and then published directly to specific folders on a report server. Additionally, the RS.EXE command line tool can be used to deploy/publish reports; this tool can also export reports to a file, configure system properties and adjust security on your report server. Finally, a group of PowerShell commands is available that function similar to the RS.EXE utility and actually provide more functionality and an easier learning curve.
- SQL Server Reporting Services RS.EXE Utility
- SSRS deploying reports using RS.EXE command line video
- PowerShell Commands for SQL Server Reporting Services
12) What are drilldown reports and drill-through in SSRS?
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. SSRS has a rich drill down functionality that allows the use of +/- buttons to move up and down a group hierarchy that is set up in a matrix report. Furthermore, drill through functionality can be attained through show / hide actions on a report or through link actions that allow another report to be called from a source report which also allows the passing of parameters.
- SQL Server Reporting Services Drilldown Features
- SQL Server Reporting Services Drill Through Tips
- Advanced KPI Configurations in SQL Server Reporting Services
- Cross Tab Report in SSRS
13) What is next for SSRS?
Only Microsoft knows the future of the SSRS product, but I would suspect that your will see a continued merging of Power BI and SSRS; there is a natural migration to push these two products and their related development resources into a single powerful product that serves data, engineering, and analysis under a single umbrella.
Interview questions can get more scenario based once you cover the general questions related to the product The best was to get acquainted with the SSRS is to install it on your local machine (along with SQL Server Developer Edition) and begin to try it out. Also joining blog sites like MSSQLTips allows you to keep up with changes that come with new releases and also you can see the questions that come up related to those items.
- SSRS Administration - SQL Server Reporting Services Administration Tips
- Best Practices - SQL Server Reporting Services Best Practices Tips
- Overview - SQL Server Reporting Services Overview Tips
About the author
View all my tips
Article Last Updated: 2021-12-21