Exception Handling in SQL Server by TRY…CATCH

Like C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks. In Sql Server, against a Try block, we can have only one CATCH block.

TRY..CATCH Syntax

 BEGIN TRY
--T-SQL statements
--or T-SQL statement blocks
END TRY
BEGIN CATCH
--T-SQL statements
--or T-SQL statement blocks
END CATCH 

Error Functions used within CATCH block

1. ERROR_NUMBER()

   This returns the error number and its value is the same as for @@ERROR function.

2. ERROR_LINE()

   This returns the line number of T-SQL statement that caused an error.

3. ERROR_SEVERITY()

   This returns the severity level of the error.

4. ERROR_STATE()

   This returns the state number of the error.

5. ERROR_PROCEDURE()

   This returns the name of the stored procedure or trigger where the error occurred.

6. ERROR_MESSAGE()

   This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Exception handling example

 BEGIN TRY
DECLARE @num INT, @msg varchar(200)
---- Divide by zero to generate Error
SET @num = 5/0
PRINT 'This will not execute'
END TRY
BEGIN CATCH
PRINT 'Error occured that is'
set @msg=(SELECT ERROR_MESSAGE())
print @msg;
END CATCH
GO 

 BEGIN TRY
DECLARE @num INT
---- Divide by zero to generate Error
SET @num = 5/0
PRINT 'This will not execute'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO 

Note

1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.
2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.
3. Each TRY block is associated with only one CATCH block and vice versa
4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.
5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.
6. XACT_STATE function within the TRY..CATCH block can be used to check whether an open transaction is committed or not. It will return -1 if the transaction is not committed else returns 1.

Difference Between Dictionary And Hashtable In C#

Dictionary and Hashtable in C# are used to hold data as a collection of key-value pair. This blog talks about some differences between a dictionary and a hashtable. 

 

The following code snippet creates a Dictionary in C#. 

1. Dictionary<string, string> EmployeeList = new Dictionary<string, string>();  
2. The following code snippet adds items to the dictionary.  
3. EmployeeList.Add("Mahesh Chand", "Programmer");  
4. EmployeeList.Add("Praveen Kumar", "Project Manager");  
5. EmployeeList.Add("Raj Kumar", "Architect");  
6. EmployeeList.Add("Nipun Tomar", "Asst. Project Manager");  
7. EmployeeList.Add("Dinesh Beniwal", "Manager");  

The following code snippet creates a HashTable in C#. 

1. Hashtable HT = new Hashtable();    
2. HT.Add(1,"s");    
3. HT.Add(3, "n");    
4. HT.Add(4, "j");    
5. HT.Add(2, "a");    
6. HT.Add(5, "u");    

At some point, you will need to make a decision as to which of these two objects to use. Here are some of the key differences between the two.

 

Dictionary

1. Dictionary is generic type Dictionary<TKey,TValue>
2. Dictionary class is a strong type < TKey,TValue > Hence, you must specify the data types for key and value.
3. There is no need of boxing/unboxing.
4. When you try to access non existing key dictionary, it gives runtime error.
5. Dictionary maintains an order of the stored values.
6. There is no need of boxing/unboxing, so it is faster than Hashtable.

Hashtable

1. Hashtable is non-generic type.
2. Hashtable is a weakly typed data structure, so you can add keys and values of any object type.
3. Values need to have boxing/unboxing.
4. When you try to access non existing key Hashtable, it gives null values.
5. Hashtable never maintains an order of the stored values.
6. Hashtable needs boxing/unboxing, so it is slower than Dictionary.

Angular 8 Tutorial - 20 - HTTP and Observables

Angularjs tutorial ( covering angular 7)

How to Publish Report to a Report Server in SSRS

Publish Report

 

            Note:-   Before publishing ensure the following settings

a) solution explorer → project → properties →
Deployment
Overwrite Data sources: False
Target Data source folder: Data Sources
Target Report Folder: MRG_REPORT
Target server URL:HTTP://RAWAN-8080/REPORTS  DWH
b) Configuration manager
Project contexts (Check the project configuration to build or Deploy
project                configuration                     platform                          Build                  Deploy

 
Debug → publishing to test environment
Debug local → Previewing the report in the local
Release → Publishing to production server
c) The user who is publishing the report should belongs to publisher role

Developer responsibilities:

1. Creating & working with standard, complex, Adhoc reports by using report server   project, report builder
2. Parameterization, actions
3. Report manipulations, various data regions, report items
4. Expressions, calculations writing
5. Data sources, datasets, connectivity
6. Linked reports……etc.

DBA (or) Super user (or)Managers Responsibilities:

1. Publishing reports
2. Subscriptions
3. Snapshots, caches
4. Folders, data sources creation
5. Report definition pupation security….etx

 

Complex creation:

1) Complex single report with queried, non queried, cascade multi value, drill through and drill down processes.
2) Create partial dashboard by using cross tab (or) Matrix reports
[various actions implemented]

SSIS in complex implementation (or)creation:

1) SCD manual implementation
i) class rooms
(or)
ii)

 

2) Multi files loading using forced load

3) Multiple worksheets loading through script task
4) Incremental loading
5) Optimized mapping using various performance tuning methods

 

6) Error loading records splitting

 

SSRS - How to Create Dynamic Reports using Parameters

Using Parameters to make Dynamic Reports

Fixed reports that show current data are very useful, but even more useful is the ability to build reports that allow the end user to change parts of the query to get at the information they really want. By adding parameters to a report, you can request information from the user when the report first opens, and then fetch the corresponding data.

As an example, you might specify that the user can type in a year to get data for that period only, or the user could select a category from a drop down list and the report would run using that selection.

Try it

1. In the Report Data panel, right-click on the SalesSummary dataset and choose Dataset Properties.

2. Add a parameterised WHERE clause to the query so that it matches the following query. Do not click OK yet.
SELECT
      MONTH(OrderDate) AS Month,
      SUM(TotalDue) AS Total
FROM
      Sales.SalesOrderHeader
WHERE
      YEAR(OrderDate) = @Year
GROUP BY
      MONTH(OrderDate)
ORDER BY
      Month

3. Click OK. Notice that you can now expand the Parameters folder in the Report Data panel and check that the parameter is there.

4. Preview the report by clicking on the Preview tab, and when the report has loaded, enter 2006 in the Year text box at the top left of the report, then click View Report on the right.

5. Enter 2007 in the Year text box and click View Report again to see that the data has changed.

Be aware that because we have not specified otherwise, this text box will accept anything you type into it, and if it’s not a year, you may get an error.

Parameter lists

To restrict the values that can be entered for a parameter, you can configure values that will be displayed as a drop down list. One option is to supply a fixed list of values that the reader can select from. This might be useful for a parameter that will only ever have a fixed set of options such as a list of the continents of the world, or a ‘male/female’ selection.             

In some cases it would make sense to let the reader select from a dynamic list. This would be useful when selecting a year. Instead of manually adding the latest year to the list on January 1st every year, you could supply a query that will fetch a list of all the years that are present in the data.

In the Try it for this section, you will query the database to get a list of the years. If you’re not familiar with SQL, note that the DISTINCT word is used to only get unique values. If it was left out, you’d get a very long list of values; one for each order in the table!

Try it

1. In the Summary Sales report, right click on the Datasets folder in Report Data and choose Add Dataset…

2. Name the new dataset Years, select the AdventureWorks data source and select Use a dataset embedded in my report. Enter the query text as shown:
SELECT DISTINCT
      YEAR(OrderDate) AS Year
FROM
      Sales.SalesOrderHeader
ORDER BY
      Year

1. Click OK to return to the report.

2. Expand the Parameters folder in the Report Data panel, right-click on the @Year parameter and choose Parameter Properties.

3. In the Report Parameter Properties window, open the Available Values page.

4. Select Get values from a query and choose the Years dataset you just created.

5. Set the both the value field and the label field to be Year.

6. Click OK and Preview the report to see the list of years.

7. Select a year from the list and click the View Report button.

How To Create A Report Dataset Reporting Services - SSRS

Create a report dataset

A dataset is usually specific to a report and will be based on a query to the database. Datasets are added to a report by right-clicking on Datasets in the Report Data panel.

e.g. if the report needs to show a summary of sales broken down by month, there must be a dataset able to supply the month and the total sales value for that month.

The query for this dataset looks at the Sales.SalesOrderHeader table in the AdventureWorks2012 database and gets the month and sum of the sales totals for that month. As we are only interested in grouping by month and not every single date when an order was placed, the MONTH function is used to convert the OrderDate into just the month number.

Note that it is not necessary to specify the database name in the query text, as this was already set by the shared data source.

Try it

1. In the Report Data panel, right click on Datasets and choose Add Dataset…

2. In the Dataset Properties dialog, name your dataset SalesSummary and change the selection from Use a shared dataset to Use a dataset embedded in my report.

3. Select the AdventureWorks data source. Note that this is the Report Data Source, which references the shared data source of the same name provided by the project.

4. Enter the SELECT query as shown:

SELECT
        MONTH(OrderDate) AS Month,
        SUM(TotalDue) AS Total
FROM
        Sales.SalesOrderHeader
GROUP BY
        MONTH(OrderDate)
ORDER BY
        Month

5. Click OK

Note that the dataset SalesSummary is now shown under Datasets in the Report Data panel. If the dataset is not already expanded, click the + button next to it, and you will see the two fields Month and Total.

SSRS Interview Questions

Q1. What is SSRS?

Ans. SSRS - SQL Server Reporting Services is a server-based report generation software system which is introduced by Microsoft.It is a collection of tools and services that helps you to create, deploy, and administer reports.Microsoft SSRS lets you create very rich reports (interactive/tabular/graphical) from several data sources with rich data visualization (maps, sparklines, charts).SSRS exports reports in various formats (Word, Excel, PDF, etc).

Q2. SSRS architecture?

Ans. SSRS architecture is an integrated set of processing components, programmatic interfaces, and tools. It is a multi-tiered included with server, application, and data layers.

Q3. List the tools and components of SSRS architecture.

Ans.

• Report Builder
• Report Designer
• Report Manager
• Report Server
• Report server database
• Data sources

Q4. Explain SSRS features.

Ans. The following are the key features of SSRS: 

• Retrieves data from managed providers such as ODBC and OLE DB connections.
• Users can create reports in various forms such as tabular, matrix,  free form, charts, etc.
• Exports data in various formats such as PDF, HTML, CSV, XML, Excel, and Word.
• Supports web-based features.
• Able to create ad hoc reports using graphics, images, etc., and also store them to a server. 
• It supports SOAP (Simple Object Access Protocol) programming interface and pluggable architecture.
• Displays KPI data using Gauge and Chart controls.

Q5. Define Reporting Life Cycle?

Ans. Reporting Life Cycle involves the following phases:

• Report authoring - It involves the creation of published reports using the Report Definition Language.
• Report management - It involves managing published reports as a part of the web service.
• Report delivery - It involves delivering reports either on the consumer’s demand or based on an event.
• Report security - It involves protecting reports as well as the report resources.

Q6. List the various Reporting Services components.

Ans.

• Report Server — It plays a key role in processing and deploying data.
• Report Server database — It stores the reports definition data.
• Report Designer — It is used to create, configure, and deploy the SSRS reports.
• Report Builder — It is used to create ad hoc reports. 
• Report Manager — It is a web-based application used to view and manage reports using Report server web service.

Q7. Crystal Reports Vs SSRS.

Ans.

Crystal Reports	SSRS
1. Reports exist as files on your network/PC	1. Reports exist on your server and can be accessed on any web browser
2. It requires an additional software purchase	2. It is included with the SQL Server database engine
3. Open Database Connectivity/ActiveX Data Objects data connection is embedded in the report	3. Data connections are separate from the report and can be shared
4. It uses the traditional banded report design	4. It uses the Tablix data region
5. Crystal reports are processed by IIS	5. SSRS has a report server
6. Crystal reports have standards and user-defined field labels	6. SSRS allows the only user-defined field labels

Q8. what is the difference between the Report Manager and Report server?

Ans.

• Report Manager is a web-based tool developed using ASP.NET application to access /view SSRS reports.
• Report Server is a collection of processing engines and extensions and is responsible to authenticate, process data, render, and delivery decisions.

Q9. How to integrate Reporting Services into custom applications?

Ans. There are three options available to integrate Reporting Services into custom applications:

• Report server web service - It is the primary interface to develop Reporting Services. If you want to develop code to manage your report catalog or to render reports to a supported format, the Report Server Web service presents all the essential methods to integrate Reporting Services into your applications.
• Report Viewer controls for Visual Studio - It is used to integrate report view within your applications.

There are two controls:

1. 1. 1. Windows Forms-based applications
      2. WebForms applications.

• URL access - It is another option to integrate report viewing into your applications if the Report Viewer controls is not a choice.

Q10. List the different types of roles available in SSRS.

Ans. 

• Browser Role - User/group who prefers to observe reports, folders, and subscribe to reports, add them to Browser role.
• Content Manager Role - User/group who manages contents in the Report Server, add them to the Content Manager Role. 
• My Reports Role - User/group who want to publish reports, manager folders,  create linked reports, etc., in the user’s My Report folder, add them to My Reports Role.
• Publisher Role - User/group who want to possess publish permissions, add them to the Publisher Role.
• Report Builder Role - Users who often want to view the definition of reports using Report Builder, add those users to Report Builder Role.

Q11. Name few Command Line Utilities in SSRS.

Ans.

Name	Command file	Purpose
RSS utility	rs.exe	used to deploy the reports on the report server database.
Rsconfig utility	rsconfig.exe	used to configure and manage a report server connection to the report server database.
Rskeymgmt Utility	rskeymgmt.exe	It is an encryption key management tool used to back up, recreate, apply, and delete symmetric keys.

Q12. What is RDL?

Ans. 

• Report Definition Language (RDL) is a file extension to an XML file and is used for SQL Server Reporting Services report definition.
• It contains data retrieval and layout information for a report and is composed of XML elements.
• It is an extensible and open schema that supports additional namespaces and custom elements.
• PDF files can be created using Microsoft Visual Studio, third-party tools, or with a text editor.

Q13. Is it possible to edit the .rdl code that associated by a linked report?

Ans. No, it is not possible because the linked report does not have a .rdl code of its own.

Q14. Explain report rendering in SSRS.

Ans. A rendering extension is a component/module of a report server that exports any report data into a device-specific format. SSRS supports seven rendering extensions: HTML, Word, CSV or Text,Excel, XML, PDF, and Image. 

Q15. List the various data sources available in SSRS.

Ans.

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

Q16. List the advantages of using SSRS.

Ans. 

• It is considerably cheaper and faster.
• Easy to deploy
• It helps to generate effective reports in both Oracle or MS SQL Server databases with data residing.
• There is no need for expensive specialist skills to use this reporting tool.
• Supports Drilldown reporting
• It is integrated with Visual Studio .NET to generate the reports in the same environment.
• Once parameters are defined clearly by developers, the UI will be generated automatically.
• Subscription-based reports are automatically sent by mail to the users

SSRS Interview Questions For Experienced

Q1. What are the Export formats of SSRS?

Ans. To export a report from report builder, SSRS supports the following formats:

• XML
• CSV
• PDF
• MHTML
• Excel
• TIFF
• Word

Q2. Explain Report Snapshot.

Ans. A Report Snapshot means an instance of a report that contains layout information and query results which are saved on a report server for future reference.

Q3. What are Data-Driven Subscriptions?

Ans. A data-driven subscription means usage of dynamic subscription data from an external data source that is retrieved at run time. It can also use static text and default values.

Data-driven subscriptions are intended to do the following:

• Distributing a report to a large list of subscribers.
• Filtering the report output that is retrieved at run time using report parameter values.
• Varying delivery options and report output formats for each report delivery.

Q4. When should we apply a Null Data-Driven Subscription?

Ans. Design a data-driven subscription that uses the Null Delivery Provider, specify the Null Delivery Provider as the method of delivery in the subscription, then the report server targets the report server database as the delivery destination and applies a specialized rendering extension called the null rendering extension.

Compared to other delivery extensions, we cannot configure the Null Delivery Provider through a subscription definition as it does not have delivery settings.

Q5. How to fine-tune Reports?

Ans. Follow the below-mentioned ways to tune-up the Reporting Services: 

• Expand the Server or using 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 (no lock), 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.

Q6. How to display PDF as an export option in report Manager?

Ans. 

• We need to edit RsReportServer.config file to limit the exporting extensions only to PDF.
• You can find the file under;
• %Program Files%Microsoft SQL ServerMSRS10.Reporting ServicesReportServer

Q7. Can you create a Data Source for MS Excel files in SSRS?

Ans. Yes, we can create a data source but before doing that, we need to create an ODBC connection on the server to the Excel file before building a data source in Report Manager.

Q8. Explain Logical Page in SSRS?

Ans. Logical Page is the size of one page of the actual report as seen in previewer and calculated at run time based on the number of columns and rows.

Rendering formats:  HTML, Excel

Q9. Mention some Report Server Web Service Endpoints.

Ans. 

• Management Endpoints
• Execution Endpoint
• SharePoint Proxy Endpoints

Q10. What are the new features introduced in SQL Server 2017 reporting services?

Ans.

• Comments on reports - comments are now accessible for reports and also you can include attachments with comments.
• DAX queries in reporting tools - You can create native DAX queries against the SQL Server Analysis Services tabular data models.
• REST API support - SSRS now supports OpenAPI compliant RESTful API.
• Query designer support for DAX now in Report Builder and SQL Server Data Tools - Now you can use the query designer in both Report Builder and SQL Server Data tools to drag and drop the fields you want.

Q11. List the Data Sources Supported by SSRS 2017.

Ans. 

• Microsoft SQL Server
• Microsoft Azure SQL Database
• OLE DB
• Oracle
• Hyperion Essbase
• Microsoft SharePoint List
• Microsoft SQL Server Analysis Services for  DMX, MDX, Microsoft Power Pivot, and tabular models
• Teradata
• ODBC
• XML
• SAP BW

Q12. Does SSRS support different databases other than MS SQL Server?

Ans. Yes, SSRS supports different databases other than MS SQL Server. It is built based on relational or multidimensional data source like OLEDB, ODBC, Oracle, etc.

Q13. What is the main purpose of query Parameter?

Ans. The main purpose of the query parameter is to filter data during data processing. They are defined in the syntax of a data processing extension.

Q14. What is the Matrix?

Ans. The matrix is a data region displays report data in cells that are grouped into columns and rows. The number of Columns and rows in a group are determined by the unique values of each column and row groups.

Q15. Explain SSRS Subreports.

Ans. A Subreport is a reference to another report, allows us to add one or more related reports to the data region.

The following are the benefits of SSRS Subreport:

• Enables us to add different related reports in one Report.
• Allows us to nest or include one report inside the other.
• Allows us to pass parameters from the main report to the SSRS subreport.

Q16. How to add a chart to a report in SSRS?

Ans. 

• Run a new chart wizard to add a chart data region to your report. The wizard offers line, column, pie, area, and bar charts.
• You can drag report dataset fields for both numeric and non-numeric data to the Chart Data pane of the chart, after adding a chart data region to the design surface.
• Click the chart to represent the Chart Data pane with its three areas:

1. 1. Series Groups
   2. Category Groups 
   3. Values

Q17. How to add the custom code in SSRS Report?

Ans. In order to add the custom code in the SSRS report, go to the Reports menu and select the Report Properties submenu and there you will find the options for custom code.

Q18. When filters will be applied in the Cached Report instance?

Ans. When a report is rendered filters are applied.

SSRS Scenario Based Interview Questions

Q1. What is a Data Region?

Ans. A data region is an object in a report that displays repeated rows of summarized information from report datasets.

Report data can be represented as text and numbers in a table/matrix/list,  graphically in a chart or gauge; and against a geographic background in the form of a map.

Q2. If you want to create a report in a chart format. How can you use the Report Wizard to create such a report? 

Ans. You cannot create a Report wizard to create such a report, as the Report Wizard lets you create the only matrix and tabular reports. You must create the chart report directly by using the Report Designer.

Q3. Is this possible to use Datagrid as a data source for my report using SSRS?

Ans. No

Q4. List the various Processing Modes offered by SSRS.

Ans.

• Local Processing Mode -  Processes reports by ReportViewer control in the client application.
• Remote Processing Mode -  Processes reports on a SQL Server Reporting Services report server.

Q5. What are ReportServerTempDB and ReportServer?

Ans. 

• Reporting Services use two SQL Server databases for the storage of data. By default, they are named as ReportServer and ReportServerTempdb.
• ReportServer is the main database, which stores all internal configuration and reports metadata.
• ReportServerTempdb is used to store temporary data, session information, and cached reports.

Q6. What are encryption keys and how to backup them?

Ans. Encryption keys are used for protection of sensitive data and credentials in SSRS. They are used to perform restoration of the report server databases. In SSRS, encryption is supported through a sequence of public, private, and symmetric keys that are used to protect sensitive data.

• To backup encryption Keys in SSRS, use SQL Server Reporting Services Configuration tool to backup symmetric keys.
• Start Reporting Services Configuration Manager and connect to report server instance to configure.
• Select Encryption Keys.
• Select Backup.
• Select a File Location to contain the stored key.
• Enter a password (this will be used to Restore the key if required).

Q7. List the major configuration files for SQL Server Reporting Services.

Ans.

Name	Description
RSReportServer.config	It stores configuration settings for characteristic areas of the Report Server service such as Report Manager/web portal, the Report Server Web service, and background processing.
RSSrvPolicy.config	It stores the code access security policies for the server extensions.
RSMgrPolicy.config	It stores the code access security policies for the web portal.
Web.config for the Report Server Web service	It includes only specific settings that are required for ASP.NET.
Web.config for Report Manager	It includes only specific settings that are required for ASP.NET.
ReportingServicesService.exe.config	It stores configuration settings that specify the logging options and trace levels for the Report Server service.
Registry settings	It stores configuration state and other settings that are required to uninstall Reporting Services.
RSReportDesigner.config	It stores configuration settings for Report Designer.
RSPreviewPolicy.config	It saves the code access security policies for the server extensions used while report preview.

Q8. What is the use of SQL Server Report Builder?

Ans. 

• SQL Server Report Builder is a report authoring tool from Microsoft which allows users to design, manage, and publish reports to SQL Server Reporting Services.
• It is a standalone application and works on both SQL Server and Analysis Services data sources.

Q9. In which SQL Server version report builder was introduced?

Ans. Report builder was introduced in SQL Server 2005. 

Q10. How does Report Builder support Analysis Services cube?

Ans. Report Builder supports relational SQL and Analysis Services data sources in SQL Server. For creating a model to Analysis Services cube, go to the Report Manager or Management Studio, build a data source for your Analysis Services database, and then choose the Generate Model option to build the model.

Q11. How do users use Report Builder with SQL Server data sources?

Ans. 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.

Q12. How do I get Report Builder to generate a parameter that can be set by users viewing the report?

Ans. In the filter dialog box, click the name of the criteria that you would like to prompt the user for when viewing the report. For example, for the criteria Order Year=2000, click Order Year. Select the Prompt option in the drop-down list.

Q13. Is it possible to run SQL Server Reporting Services with SQL Server Express edition, which is a free version of SQL Server?

Ans. Yes, we can SQL Server Express Edition with Advanced Services supports SSRS. 

Q14. What are the limitations of SQL Server Express Edition in SSRS?

Ans. The limitations of SQL Server Express Edition in SSRS are listed below:

• We cannot store the report server database on a remote server (it has to be local only)
• Management Studio cannot be used to administer a report server
• Report Builder is not available
• The remote server database is not available for Report Data Source (Local SQL Server is an only option)
• Cannot be integrated with SharePoint
• Caching, History, and Delivery of Report is not available.
• SQL Server Agent is not available
• Scale-out Report Servers will not be available
• Report Models will not be available
• Only named instances are supported
• Reports can be rendered only in PDF, Image, and Excel 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
• No scheduling is possible
• Cannot implement Role-based security

Q15.  List the tools available in the market as an alternative to SQL Server Reporting Services?

Ans. Non-Open Source:

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

Open Source:

• BIRT
• ClicData
• The ELK Stack
• Helical Insight
• Jedox
• JasperReports Server
• KNIME
• Pentaho
• Microsoft Power BI
• RapidMiner
• ReportServer
• Seal Report
• SpagoBI
• SQL Power Wabit
• Tableau Public
• Zoho Reports
• Windward Studios
• GridGain In-Memory Data Fabric
• Sisense
• Crystal Reports
• KiniMetrix
• Telerik Reporting
• Valentina Reports
• Aspose.Total for Reporting Services
• ART
• ASP.NET Report Maker

Q16. How to deploy the SSRS Report on SQL Server?

Ans. We can deploy the SSRS report on SQL Server in three ways:

• Using Visual Studio - We can directly deploy the SSRS report through solution explorer by giving the report server URL in project properties at Target Server URL. 

1	In Solution Explorer -> right-click on your project -> Properties

• Using Report Server - We can go directly to the report server and deploy the report by browsing the report from the disk location of the server.
• Creating the Utility - SQL server provides the facilities to create a customized utility to deploy the report

Q17. What are the new features of SQL Server 2016?

Ans. 

• New Report Portal
• Paginated Report Enhancements
• Mobile Report Publisher
• Support of SharePoint mode and SharePoint 2016
• Microsoft .NET Framework 4 Support
• Subscription Improvements

Q18. List SSRS 2017 new features.

Ans.

• DAX queries in reporting tools
• REST API support
• Query designer support for DAX now in Report Builder and SQL Server Data Tools

Q19. How to backup SQL Server Reporting Services?

Ans. SQL Server Reporting Services can be backed up in the following ways:

• Backup the Report Server Databases- which can be done by SQL server backup and restore method.
• Backup the Encryption Keys- By using the SSRS Configuration tool we can backup the symmetric keys.
• Backup the Configuration Files- SSRS Configuration is saved in config files, which can be copied as part of the backup. 
• Backup Data Files- Back up the files that you build and manage in Report Designer.

SSRS Advanced Interview Questions

Q1. Which Web Service is used for reporting services?

Ans. Report Server Web Service is used in SSRS. By accessing this web service, we get the full functionality of the report server. It is an XML Web service with a SOAP API.

Q2. What is a cache in SSRS?

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

Q3. Is it possible to create a cache of a report regularly?

Ans. No, you cannot create a cache of a report regularly. We can create a cache of a report based on some requirements, such as having unique credentials which are met with the stored data in the Report Server.

Q4. what are the different types of Reports available in SSRS?

Ans. You can create the following types of reports with SSRS:  

• Cached reports
• Parameterized reports
• Linked reports
• Snapshot reports
• Drill Down reports
• Subreports
• Ad hoc reports
• Clickthrough reports
• Drillthrough reports

Q5.What is Parameterized Report in SSRS?

Ans. 

• A parameterized report performs report or data processing using input values. 
• You can vary the output of a report based on the values that are produced while the report runs using a parameterized report. 
• They are frequently used for linked reports, subreports, and drill through reports to connect and filter reports with related data.

Q6. What is a Linked Report in SSRS?

Ans.

• A Linked Report in SSRS is a report server item which provides access to an original report.  
• Conceptually, it is relevant to a program shortcut that you do to run a program or open a file.
• A Linked Report is actually obtained from an actual report in report manager and also retains the original report definition. It maintains the report layout and data source properties of the original report.
• The linked report is created whenever you want to create the additional versions of the original report and also you want to deploy an existing report with other settings.

Q7. What is Snapshot Report in SSRS?

Ans. 

• A Snapshot Report in SSRS is a pre-executed report that contains the layout information, and the dataset that is used for the report. They are used to store the report data based on schedules and saved to report server. 
• Report Snapshots are rendered in a viewing format when a user or application requests it.
• When you choose for a report snapshot for viewing, the report server collects the saved 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.

The following are the benefits of the Report snapshots:

1. Report history - You can build a history of a report while creating a report snapshot. 
2. Consistency - Report snapshots provide consistent results for many users with identical sets of data.
3. Performance - By scheduling large reports to run during off-peak hours, you can reduce the processing impact on the report server during core business hours.

Q8. What is Cached Report in SSRS?

Ans. 

• A cached report in SSRS is a saved copy of a processed report and displays it when the user opens the report. 
• Cached reports are used to improve the performance of processing requests to the report processor by shortening the time period to recover large reports. 
• Cached Reports have a mandatory expiration period in minutes.

Q9. What are Click through Reports in SSRS?

Ans.

• A clickthrough report represents a table of related data from a report model when you click the interactive data contained within your model-based report. 
• These reports are created by the report server based on the information we produced while creating the report model.

Q10. What are Drilldown Reports in SSRS?

Ans.

The Drilldown reports in SSRS represent allowing the users to show or hide the data by providing plus and minus symbols on a text box to control how much detail data they want to see.

Q11. What are Drillthrough Reports in SSRS?

Ans.

• A Drillthrough report is a standard report that is accessed through a hyperlink on a text box from the original report. They work with the main report and are the target of a drill through action for a report item such as placeholder chart or a text. 
• The data in the drill-through report is retrieved when the user opens by clicking a link in the main report.
• If the data for both the drill-through report and the main report must be retrieved at the same time, consider using a subreport.

Q12. What is Subreport in SSRS?

Ans.

• A subreport is a report that represents another report inside the body of the main report. A subreport is actually a reference to another report.
• The report that the subreport shows are stored on a report server, mostly in the same folder as the main report. You can set up the main report to pass parameters to the subreport.
• The subreport can use different data sources than the main report.

Q13. What is Data Set in a report in SSRS?

Ans. A dataset does not contain the original data. It contains the data that is required to retrieve a particular set of data from a data source.

Datasets are of two types:

• Embedded datasets
• Shared datasets

Q14. Will there be any issues while exporting SSRS reports into Microsoft Excel?

Ans. Exporting SSRS reports to MS Excel is not always perfect. When you export a report to Excel try to re-sort the exported data you will get a merged cell error.

• There are many ways by which merged cell problem occurs when you export reports to Excel:
• If you have anything ( images, controls, etc.) placed out above your matrix/table regions.
• If you merge cells in matrix/table regions.
• If controls from the top of the report do not lineup with controls from your table.

Q15.  What is report subscription in SSRS?

Ans. A report subscription in SSRS is a report snapshot created on some defined scheduled time and delivered to the intended audience by the report server. 

• SSRS enables you to build two types of subscriptions.
• Standard subscriptions
• Data-driven subscription

Q16. Can you use a stored procedure to provide data to an SSRS report?

Ans. Yes, you can use a stored procedure to get data on the SSRS report. However, your stored procedure should use only a single result set.

Q17. How to send an SSRS report from SSIS?

Ans. Trigger an Email of an SSRS Report from an SSIS Package.

At the report subscription, you can mention the report format and the email address of the recipient. After creating a schedule for the SSRS report, a SQL Server Agent Job will be created. From the SSIS package, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.

Q18. You want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server URL?

Ans. You can right-click the project in Solution Explorer and then change the Target-Server URL property.

Q19. Can we deploy SSRS reports on our personal website?

Ans. We can deploy reports only on the reporting services website. The only option for viewing them from other sites is an HTTP link. Some tools like SharePoint provide control to view reports in the context of the other websites, but the report is still deployed and hosted from reporting services.