SSRS Interview Questions

The SQL queries should be saved in a database server's stored procedure. Because storing SQL queries in text format is no longer regarded as good practice, it should be avoided. SQL would be in an accumulating format if queries were stored in Store Procedures to a data server, while still giving all of the benefits of using a Stored Procedure.

Following are some of the different servers with which we can use SQL Server Reporting Services (SSRS):-

• Oracle
• ODBC (Open Database Connectivity) and OLEDB (Object Linking and Embedding Database)
• Hyperion
• Teradata
• Flat XML files

Following are some examples of open-source software which are an alternative to SQL Server Reporting Services:-

• Jasper Reports
• JFree Report
• BIRT (Business Intelligence Reporting Tool)
• OpenReport
• DataVision

To reduce the overhead of Reporting Services Sources, cached reports and snapshot reports can be employed.

When building an SSRS report, users can utilise the multi-value parameter to enter and pass several queries for the parameter. Any report parameters that we employ to filter out data and get meaningful information for the current scenario. You have the option of entering static values or obtaining values from databases in the multi-value parameter.

The virtual access to the data that we have is known as data visualisation. A well-designed data might take the form of a graph, charts, or a report, and visual representation of data is the simplest and most powerful way to describe data.

Every day, we generate approximately 2.5 quintillion bytes of data, which is a massive amount of data. We need to be able to see what this data implies, so it must be converted into a visual format. Data visualisation is preferable over reading since our eyes perceive information better. The basic goal of data visualisation is to help people comprehend data better than they can by reading it. Humans are better at interpreting data or information using charts and graphs than they are at analysing data using spreadsheets. Data visualisation is a simple and rapid technique to explain common notions. The following are some of the reasons why data visualisation is beneficial:

• It assists you in identifying areas that require additional attention or improvement.
• It also aids in the identification of the factors that affect customer behaviour.
• It can also be used to estimate sales volume.

To obtain a sequence number for all the records in your SSRS report, use the row number function. You may achieve this by adding a new blank column to your Tablix and then pivoting to expressions and writing expressions in the cell.

When adding a query to your report using the bit-type column, go to parameter properties and change the data type to boolean. Otherwise, a text box will appear for the parameter value.

The number of pages in a paginated report and how the report components are organised on these pages are referred to as pagination. The renderer extension you choose to view and send the report affects the pagination in Reporting Services. When you run a report on the report server, the HTML renderer is used. Pagination in HTML is governed by a set of principles. When you export the same report to PDF, for example, the PDF renderer is utilised and a different set of rules is used; as a result, the pagination of the report varies. Pagination rules are important to create an easy-to-read report for your users that is optimised for the renderer you plan to use to deliver your report.

Following are some of the rendering extensions available in SQL Server Reporting Services (SSRS) :-

• Excel
• XML
• Word
• PDF
• CSV
• HTML

Following are the different types of reports in SQL Server Reporting Services (SSRS):-

• Parameterised Reports: A parameterized report completes report or data processing by using input values. You can change the output of a report using a parameterized report by changing the values that are set when the report runs. Drillthrough reports, linked reports, and subreports are all common uses for parameterized reports, which connect and filter reports with related data.
• Linked Reports: A linked report is an item on the report server that provides a link to an existing report. It's akin to a programme shortcut that you use to start a programme or open a file in terms of concept. A linked report is created from an existing report and preserves the report definition from the original. The report style and data source attributes of a linked report are always inherited from the original report. All additional attributes and options, including security, parameters, location, subscriptions, and scheduling, can differ from those in the original report. 
  When you need to make extra copies of an existing report, you can use the report server to build a linked report. For example, you might build region-specific reports for all of your sales regions using a single regional sales report.
  Although parameterized reports are commonly used to create connected reports, they are not essential. When you want to deploy an existing report with different settings, you can construct linked reports.
• Snapshot Reports: A report snapshot is a report that provides layout information and query results from a single point in time. Report snapshots are processed on a schedule and then saved to a report server, unlike on-demand reports, which obtain up-to-date query results as you select the report. The report server gets the stored report from the report server database and displays the data and layout that were current for the report at the time the snapshot was created when you select a report snapshot for viewing.
  Snapshots of reports are not saved in a specific rendering format. Instead, when a user or an application asks for it, report snapshots are rendered in a final viewing format (such as HTML). A snapshot is portable thanks to deferred rendering. The report can be displayed in the appropriate format for the requesting device or browser.
• Cached Reports: A cached report is a copy of a processed report that has been saved. Cached reports increase speed by lowering the number of processing requests sent to the report processor and the time it takes to obtain large reports. They must expire after a certain amount of time, which is commonly measured in minutes.
• Clickthrough Reports: When you click the interactive data contained inside your model-based report, a clickthrough report displays relevant data from a report model. The report server generates these reports using the information given in the report model. When a clickthrough report is opened, the person who designed the model selects which fields are interactive and which fields are returned. The report creation tools do not allow you to update certain field settings.
  Clickthrough reports are generated automatically. You can, however, construct a customised report for interactive data items that is displayed instead of the model. The custom report is a Reporting Services standard report.
• Drilldown Reports: Drilldown reports hide complexity at first, allowing the user to select how much detailed data they want to see by toggling conditionally hidden report components. Drilldown reports are required to retrieve all data that can be displayed in the report. Consider drillthrough reports for reports with a lot of data.
• Drillthrough Reports: Drillthrough reports are regular reports that can be viewed by clicking on a hyperlink in the original report's text box. Drillthrough reports are used in conjunction with main reports and are the target of a drillthrough action for a report item like placeholder text or a chart. The main report provides summary data in a matrix or graphic, for example. Drillthrough links to reports based on the aggregate in the main report are provided by actions defined in the matrix or chart. Drillthrough reports can but do not have to, be filtered by parameters. Drillthrough reports are distinct from subreports in that they do not appear within the original report, but instead open as a new window.
  They differ from clickthrough reports in that they are custom reports that are saved on the report server rather than being generated automatically from the data source. They differ from drilldown reports in that they only retrieve report data for the parameters or dataset query supplied.
• Subreports: A subreport is a report that presents a different report within the main report's body. A subreport is akin to a frame in a Web page in terms of concept. It's used to put a report inside another report. A subreport can be created from any report. Different data sources can be used in the subreport than in the main report. The report that the subreport displays is normally kept in the same folder as the parent report on a report server. The parent report can be configured to transmit parameters to the subreport. Although a subreport can be repeated inside data regions by using a parameter to filter data in each instance, subreports are usually used in conjunction with the main report as a briefing book or as a container for a series of related reports.
  Consider using drillthrough reports instead of subreports for reports with a lot of them.

Following are the benefits of paginated reports in SQL Server Reporting Services (SSRS):-

• Reports provide a unified perspective of data to aid in business decision-making. For this, we should use a single set of data sources to get a single set of facts.
• We can view reports for our own use or share reports with our team or organisation by publishing them to a report server or SharePoint site.
• Once we have defined a report, we can use it in a variety of ways. We can export the report to a variety of file formats, or send it to subscribers as an e-mail attachment or a shared file. Multiple linked reports can be created with different parameter settings applied to the same report definition.
• Using data regions, we may visualise our data in a variety of interconnected ways. We can display data in tables, matrices, expand/collapse groups, charts, gauges, indicators or KPIs (Key Performance Index), and maps, and nest charts in tables.
• We can manage report data sources separately from the report definition. We can, for example, go from a test to a production data source without affecting the report.
• To create reusable data visualisations, we can employ report sections, shared data sources, shared queries, and subreports.
• We can define expressions that allow us to tailor the way report data is filtered, aggregated, and sorted.
• We can create a free-form layout for our reports. The arrangement of a report is not limited to data. We can arrange the facts on the page so that it fosters comprehension, insight as well as action.
• To allow report readers to engage with the report, we can enable drillthrough actions, expand/collapse toggles, sort buttons, Tooltips, and report parameters. We use report parameters in conjunction with custom expressions to provide report readers control over how data is filtered, aggregated, and sorted.

Paginated reports in SQL Server Reporting Services are XML-based report definitions that comprise report data and layout elements. Report definitions have the .rdl file extension on a client file system. A paginated report can be referred to as a report item on the report server or SharePoint site after it is published. Paginated reports are a feature of Reporting Services' server-based reporting technology. SQL Server Mobile Report Publisher can also be used to create mobile reports.

We specify a report definition file (.rdl) in XML format when we generate a report. This file contains all of the information required by the report processor to merge report data and layout. When we view a report, it goes through the stages listed below:

• Compile: Evaluate report definition expressions and store the produced intermediate format on the report server.
• Process: Execute dataset queries and mix intermediate format with data and layout to create a finished product.
• Render: Send the paged report to a renderer extension to assess how much information fits on each page.
• Export (optional): We can save the report in various different formats such as a doc file or a pdf.

 A reporting lifecycle is a set of procedures that every organization uses to create a report. There are three stages to the reporting lifecycle. They are as follows:

• Authoring: The report's layout and data source are determined by the authors. The report definition language is the syntax used to define the report. Report builder, for example, is one of the tools used for authoring.
• Management: The second phase of the reporting lifecycle is management. It entails handling publicly available reports, which are currently available through web services. You must establish role-based security models for the reports that are currently available through the web interface. It's used to determine who has access to the report and who can view it. Controlling the execution, i.e. when the report should have received the data, is a crucial duty in the management phase. In addition, the entire report should be updated in real-time. Any data source that uses data must be updated on a regular basis. Working with static data is simple. When working with dynamic data, it's important to specify when the updates will take place. It could be a day, an hour, or a week.
• Delivery: We determine when the reports will be given to the client in the Delivery phase. It could be on an as-needed basis or according to a set timetable. You can also include an automatic subscription option that will generate and send reports to the client on a regular basis.

Following are the different types of data regions in SQL Server Reporting Services (SSRS) :-

• Table: A table is a data region that displays data in a row-by-row format. The number of columns in a table is fixed; you decide how many columns you want when you build your report. Table rows are dynamic, meaning they expand downwards to fit the data. Tables can be divided into groups, which organise data based on selected fields or expressions.
• Matrix: A crosstab is another name for a matrix. In a matrix data region, both dynamic rows and columns can be present. Dynamic and static columns and rows are both possible in a matrix. Columns and rows can be used to group data and can contain other columns and rows.
• List: A list is a type of data region that displays data in a freeform format. You can use text boxes, photos, and other data areas to create a form by arranging report items in a list.
• Chart: A chart is a visual representation of data. Bar, pie, and line charts are examples of charts, although there are much more available.
• Gauge: A gauge shows data in the form of a range with an indicator pointing to a specific value within that range. Key performance indicators (KPIs) and other metrics are displayed using gauges. Linear and circular gauges are examples of gauges.
• Map: You can use a map to present data against a geographic backdrop. Spatial data from a SQL Server query, or Microsoft Bing map tiles can all be used as map data. Sets of coordinates define polygons that represent shapes or areas, lines that represent routes or journeys, and points that are represented by markers in spatial data. You can correlate aggregate data with map items to change their colour and size automatically. For instance, you can change the marker type for a store depending on the volume of sales or the colour for a road depending on the speed limit.

In a report, a data region is an object that displays data from a report dataset. In a table, matrix, or list, report data can be displayed as numbers and text; visually in a chart or gauge; or against a geographic backdrop in a map. The tablix data region, which expands as needed to display all of the data from the dataset, is the basis for all tables, matrices, and lists. Multiple rows and column groupings, as well as static and dynamic rows and columns, are supported by a tablix data region. In a number of chart forms, a chart presents numerous series and category groups. For a dataset, a gauge shows a single value or an aggregated value. A map shows spatial data as map elements that can look different depending on the aggregated data from a dataset.

The architecture of SQL Server Reporting Services (SSRS) consists of the following components:-

• Report Builder: It's a client-side ad-hoc report publishing tool that runs on the user's computer. It has an easy-to-use drag-and-drop interface.
• Report Designer: The Report Designer tool aids in the creation of a variety of reports. It's a publishing tool for Visual Studio or Business Intelligence Development Studio (BIDS).
• Report Manager: Report Manager is a web-based tool that comes with Reporting Services and is responsible for all areas of report management.
• Report Server: It is a server that stores metadata information using the SQL Server database engine.
• Report Server Database: It keeps track of metadata, report definitions, resources, security settings, and delivery information, among other things.
• Data Sources: Data is retrieved from data sources such as relational and multidimensional data sources via reporting services.

Working of SQL Server Reporting Services: Any data source, such as Oracle, MySQL, SQL Server, and so on, can be used. Any of these data sources can be linked to Microsoft SQL Server. The data retrieval and rendering processes begin after the data sources are connected to the SQL Server. The process of filtering and evaluating data in order to meet the requirements is known as rendering. Processing of reports: Rendering is followed by report processing. The process of publishing, filtering, and amending existing reports is known as report processing. Web service interfaces in XML: When you publish a report, it is automatically published to the website via the XML web service interface. SQL Server includes security, scheduling, and delivery.

Following are the advantages of SQL Server Reporting Services (SSRS):-

• It is both quick and less expensive.
• It provides access to information stored in both MS SQL Server and Oracle databases for efficient reporting.
• There is no need for high-priced specialised training to use SQL Server Reporting Services.
• Visual Studio.NET is integrated with the default report designer in SSRS. This allows us to create an application as well as reports in the same place.
• Security is controlled by a role-based approach that may be applied to folders and reports.
• Users receive subscription-based reports automatically.
• Reports on relational and cube data can be produced more quickly.
• It provides real-time data pertaining to the business, allowing for better decision-making.

Following are the disadvantages of SQL Server Reporting Services (SSRS):-

• There is no option to print. If you wish to print something, you must first export it to PDF, Excel, Word, or another format.
• If the report has parameters, users must need to pass the values for parameters even if they do not wish the data in the report to be filtered.
• Making changes to custom code and debug expressions is tough.
• It is not possible to include a page number or the total number of pages in the report body.
• There is no way to send values from sub-reports to the main report.
• Every new page has an extra space created by the page header.

 Following are the features of SQL Server Reporting Services (SSRS):-

• It provides a pluggable architecture and a Simple Object Access Protocol (SOAP) application.
• Data can be retrieved from OLE (Object Linking and Embedding Database, ODBC (Open Database Connecting) , and database connections.
• It allows us to produce and save ad hoc reports to the server.
• Data can be shown in a variety of ways, including tabular, free-form, and chart styles.
• Using the report-processing extension, we can create custom controls.
• We can incorporate graphics and photos into the reporting. SharePoint can also be used to integrate with external material.
• Custom reports can be saved and managed.
• We can use the chart and gauge control tool to display KPI (Key Performance Index) data.

Power Bi: Microsoft's Power BI is a data analysis and reporting tool. It's a cutting-edge corporate analytics tool with a plethora of intelligent and user-friendly features. Data connectivity, data translation, data preparation, data modelling, reporting, and dashboarding are just a few of the capabilities that Power BI offers. In addition, additional features such as DAX formulae, bespoke graphics, and so on are available in Power BI.

Following are the differences between SQL Server Reporting Services and Power Bi:-

• SSRS requires a SQL Server licence, whereas Power BI is free. Power Bi Report Server (an on-premises report server that includes a web portal for viewing and managing reports and Key Performance Indexes), on the other hand, necessitates the purchase of a Power BI Premium licence.
• SSRS is a server-based database, while Power BI is cloud-based.
• Power BI is more graphical and modern. SSRS is a traditional reporting and analysis system that requires manual effort and time.
• In Power BI, you may use data from the cloud. Only on-premise data can be used by SSRS.
• The successor to SSRS, Power BI Report Server (PBIRS), contains additional functionality than SSRS. For one thing, unlike SSRS, it can use and display both interactive PBIX and analytical XLSX reports.
• Cortana has been integrated with Power BI for AI-based natural language, Q&A about your data, and reports. This feature is not available in SSRS.
• To create reports, Power BI includes a graphical interface with drag-and-drop functionality. Working with unstructured data, modern rendering, publishing, integrating, and collaborating across platforms is simple.

References and Resources:

SQL Server Reporting Services (SSRS) Documentation

SSRS Download

SSIS Interview

Microsoft Interview

Reports with many parameters are known as cascaded parameterized reports.

Some parameters, on the other hand, will have values or value sets that are dependent on other parameters. The order of the parameters can affect the report's optimization. For example, if we have a million results for a customer parameter, we may filter it by adding a gender parameter first, which will cut the customer parameter results in half. We have a subcategory parameter, for example, but its values aren't filled in until the category parameter is selected.

After an SSIS package has completed a data load, it is frequently necessary to be able to transmit an SSRS report in Excel, PDF, or another format to different users. To begin, you must first create a subscription to the report. From the Report Manager, you can create an SSRS report subscription. You can specify the report format and the recipient's email address when subscribing to a report. A SQL Server Agent Job is established when you set a schedule for an SSRS report. You may run the SSRS report subscription from SSIS by using sp_start_job and giving the required job name.

RDL stands for Report Definition Language. It is an XML representation of a SQL Server Reporting Services report definition. The data retrieval and layout information for a report is contained in a report definition. RDL is made up of XML elements that follow a Reporting Services-specific XML language. By accessing code assemblies within report definition files, you can add your own custom functions for manipulating report item values, styles, and formatting.

RDL encourages commercial reporting product interoperability by creating a common schema that allows report definitions to be exchanged. RDL can be used with any XML-based protocol or programmatic interface. In other words, RDL is :

• An XML schema for report definitions.
• A business-to-business and third-party interchange format.
• An extensible and open architecture that provides support to additional namespaces and custom elements.

Following are the various components of an RDL file:-

• Data: This section holds the dataset that may be used to write queries and is always linked to the data source.
• Design: You can create a report in the shape of tables, matrices, or other formats. All you have to do now is drag and drop the data you wish to use.
• Preview: After the run command has been executed, it is used to examine the report preview.

Following are the use cases of Snapshot reports in SQL Server Reporting Services (SSRS):-

• History of the report: You may create a reported history that displays how data changes over time by taking a series of report snapshots.
• Consistency: When you want to give consistent results for multiple users who must work with the same data sets, use report snapshots. An on-demand report can offer different results from one minute to the next when dealing with variable data. A report snapshot, on the other hand, allows you to compare data from the same point in time to data from other reports or analytical tools.
• Performance: You can lessen the processing effect on the report server during core business hours by scheduling large reports to execute during off-peak hours.

Following are the advantages of using SQL Server Authentication:-

• It enables SQL Server to work with older applications and third-party apps that require SQL Server authentication.
• It enables SQL Server to work in mixed-OS situations where all users are not authenticated through a Windows domain.
• It enables users to connect from domains that are unknown or untrusted. For example, an application that allows established clients to check the progress of their orders using assigned SQL Server logins.
• It enables SQL Server to support user-created identities in Web-based applications.
• It enables software developers to deploy their programs utilising a complicated permission hierarchy based on SQL Server logins that are known and pre-configured.

Following are the disadvantages of using SQL Server Authentication:-

• Even a Windows domain user with a Windows login and password must provide another user id and password (SQL Server) to connect. Many people find it challenging to keep track of several user ids and passwords. It can be inconvenient to have to supply SQL Server credentials each time you connect to the database.
• The Kerberos security protocol is not supported by SQL Server authentication.
• Additional password policies are provided for Windows logins that are not accessible for SQL Server logins.
• At the moment of the connection, the encrypted SQL Server Authentication login id and password must be sent over the network. The password is stored on the client by some applications which connect automatically. These are attack spots that are susceptible to attackers.

You must choose an authentication mode for the Database Engine during setup. Windows Authentication mode and mixed-mode are the two options. In Windows Authentication mode, Windows Authentication is enabled while SQL Server Authentication is disabled. Both Windows and SQL Server authentication is supported in mixed mode. Windows Authentication is anytime enabled and can't be turned off.

• You must set up a strong password for the built-in SQL Server system administrator account if you choose Mixed Mode Authentication during setup. SQL Server Authentication is used by the system administrator account to connect.
• The setup establishes the system administrator account for SQL Server Authentication if you choose Windows Authentication while setting up, but it is disabled. You must enable the system administrator account if you later switch to Mixed Mode Authentication and want to utilise it. As a system administrator, any Windows or SQL Server account can be set up. Since the system administrator account is well-known and frequently targeted by unscrupulous individuals, you should only enable it if it is required by your application.

When a report is divided into columns, such as columns in a newspaper, they are referred to as logical pages rendered on the same physical page. They are ordered from left to right, top to bottom, and each column is separated by white space. Each physical page is divided vertically into columns, each of which is regarded as a logical page if the report is divided into more than one column.

Take, for example, a physical page with two columns. The first column and then the second column is filled with the content of your report. If the report does not fit entirely within the first two columns, the first column is filled first, followed by the second column on the following page. From left to right, top to bottom, columns are filled until all report items are rendered. The column spacing defaults to zero if you give column sizes that cause the horizontal or vertical widths to equal zero.

The paper size is the physical page size. The report's rendering is determined by the paper size that you pick. Reports that are formatted with a hard page break When printing or viewing a hard page break file format, insert page breaks horizontally and vertically based on the physical page size to provide an ideal reading experience. Soft page break formats are used in reports. Insert horizontal page breaks based on the physical size to provide a better reading experience in a Web browser.

The page size is set to 8.5 x 11 inches by default, but you can modify it in the Report Parameters window, Page Setup dialogue box, or the Properties pane by setting the PageHeight and PageWidth properties. The report body's contents do not cause the page size to increase or decrease. If you want the report to be printed on one page, all of the text in the report body must fit on that page. If it doesn't fit and you choose the hard page break format, you'll need to add more pages to the report. A page break is inserted horizontally if the report body extends beyond the right margin of the physical page. If the report body extends beyond the physical page's bottom edge, a vertical page break is inserted.

Following things should be kept in mind while creating paginated reports:-

• Determine the report's purpose for the intended audience. A well-designed report offers readers information that leads to understanding and action. The report parameters, report layout design, and report viewing experience are all influenced by the design decisions made during this step.
• Select the query type. Choose between a generalised, shared dataset query and a dataset query tailored to your individual set of reports. A common dataset with a generalised query is simple to maintain for multiple reports, but each report creator must filter the data for their own set of reports.
• Make plans for associated data views. Consider how your report's viewers will interact with it. A helpful technique to dealing with big amounts of data is to use summary reports with the flexibility to drill down into detailed data.
• Permissions must be configured. Prepare a strategy for granting the appropriate level of access. Creating a folder structure on the report server and granting access to reports and related things based on roles and folder security is a popular method.
• Select which data from each data source to use. Define report datasets for each data source. Each dataset has a query that specifies which data should be used. Define a dataset to populate the list of the available values for each parameter if you have report parameters.
• Customize the data as well as the layout. Create a report layout. The report body, data sources, datasets, data regions, text boxes, lines, and images are all included in the report definition. Rectangles are utilised as both layout and graphic element containers. Write expressions to control filtering, grouping, sorting, formatting, and displaying data in each data section. Add report titles, locations, and other identifying information to make managing dozens or hundreds of reports easier. To organise the layout elements on the website, use visual elements and containers.
• Think about which parts can be reused. Check to see whether any of the data sources or dataset queries can be reused. Create shared data sources and shared datasets on the report server or SharePoint site if this is the case. Check to see if the data areas can be reused as report sections.

Following are the command prompt utilities available in SQL Server Reporting Services (SSRS):-

RS.exe utility: The rs utility is a script host that allows you to run scripted commands. It has its source code in the rs.exe file. Run Microsoft Visual Basic scripts to copy data across report server databases, produce reports, create objects in a report server database, and more with this tool. It supports both Native and SharePoint deployment modes.

Powershell cmdlets: Powershell cmdlets are becoming increasingly popular among power users and IT professionals as a preferred language and command-line interface. CMdlets are used to interface with the CLI; in PowerShell, the majority of them are written in C and perform functions that return a.NET object. Only SharePoint modes are supported, and it installs SSRS service and proxy servers as well as deploying and managing SSRS apps and proxies.

The following is a list of the most helpful PowerShell cmdlets:

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

Rsconfig utility: Rsconfig utility is a script host that is used to perform scripted tasks such as publishing reports, creating items in a report server database, and setting and managing report server database connections. 'rsconfig.exe' is the command file, and it only supports native developer mode.

RsKeymgmt utility: The 'rskeymgmt.exe' command is an encryption key management tool that is used in database recovery procedures. Only the Native development mode is supported. Using the command line, you may also back up, apply, rebuild, and delete symmetric keys.