Friday, November 19, 2010

SQL Server Reporting Services(SSRS) Interview Questions

1. Explain the flow from a report request to report delivery.
2. Where does connection string of a report exists? Can we read it?
3. Where does reporting services stands as a BI tool compared to BO, Cognos, Crystal reports, Hyperion etc?
4. What is Subreport control used for?
5. What is the difference between Matrix control & Table control?
6. What are the different aggregate functions available in SSRS?
7. How to change the format of the currency field?
8. What are report parameters?
9. What are the different types of parameters used in SSRS, explain each one of them.
10. How to add a cascading parameter to a report?
11. How to create a drilldown report? Explain the steps.
12. How to add page breaks in a report?
13. What is data driven subscription? How to implement this?
14. What services in windows need to run for SSRS to run properly?
15. What is the report history in SSRS manager means? How to enable history?
16. Explain the security architecture of SSRS
17. Mention some of the in built security roles in SSRS
18. Why do we use Reporting services configuration manager tool? What are the tasks we achieve in this?

Q.What are the drawbacks of SSRS?
Ans-For many years Microsoft had no direct solution for reporting with the SQL server except their dependency on Crystal Reports. With SQL Server 2005 onwards they have come up with SSRS – SQL Server Reporting Services, but as such this is no better than Crystal Reports although it comes bundled with the Server. The components of this are:
1. Report Builder
2. Report Designer
3. Report Server
4. Report Manager
5. Datasource
6. Report Server Database

The drawbacks are (that you can reply in your interview):
1. It is complex as of now to understand the complete functionality and structure of this new component
2. Users are still depending on Crystal reports as they are more confident as used to compared to SSRS
3. Report Designer is for developers for standard reports and report builder is for end users for designing their simple formatted reports.
4. Complete understanding and exposure to both is important to utilize both functions fully and extensively
5. For end users using report builder, data models or report models are used
6. Report model generation rules building is a complex process
7. Report exporting to excel beyond a particular size results in loss of data

Q.What can SQL Server Reporting Services do?

With Reporting Services,

* You can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.
* You can publish reports, schedule report processing, or access reports on-demand.
* You can create ad hoc reports based on predefined models and interactively explore data within the model.
* You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports.
* You can view the reports created over a Web-based connection or as part of Windows application.
SQL Server - how to send a SSRS report from SSIS? - Feb 27, 2010 at 11:50 AM by Shuchi Gauri
How to send a SSRS report from SSIS?

Steps to send a SSRS report from SSIS:

* Create a subscription to the report from Report Manager.
* At subscription level, mention the report format and email address of the recipient user. When a schedule is created for SSRS report, an SQL Server agent job is created.
* Execute the SSRS report subscription using sp_start_job with the appropriate report name.

SQL Server - how to send a SSRS report from SSIS? - May 05, 2009 at 22:00 PM by Rajmeet Ghai
How to send a SSRS report from SSIS?

SSIS has the ability to send SSRS report in different formats like Excel, PDF etc. This is achieved by creating a report subscription using the Report manager. In the report subscription, the format of the SSRS report can be mentioned along with the email address of the recipient. sp_start_job is used to execute the report subscription.

SQL Server - how to send a SSRS report from SSIS? - June 21, 2009 at 09:00 PM by Amit Satpute

After the SSIS package has finished loading the data, the SSRS report can be sent to different users in .xls, .pdf, etc formats.

The steps one needs to follow to do this are:

* Creation of an SSRS report subscription from Report Manager.
* Entering the report format and the email address of the recipient in the subscription.
* Creation of a schedule for the SSRS report so that the SQL Server Agent Job gets created.
* Execution of SSRS report subscription from the SSIS by using sp_start_job and providing a relevant job name.


  1. Thank you.Well it was nice post and very helpful information on SSRS Online Training.

  2. The information you provided in this article is very useful. As a newbie, i learned a lot about Documentum from your blog. The information is worth and very useful for the beginners.Thank you very much. A beginner can refer to this online resource Documentum Learner as which I found as a helpful guide.