SSRS INTERVIEW QUETIONS

SQL Server Reporting Services (SSRS) Interview Questions Part-1

1. You want to include an image in a report. How do you display the Image Properties dialog box?
   When you drag an image item from the Toolbox window to the Report Designer, the Image Properties dialog box automatically opens.
2. You want to configure an amount to display the value in a currency format. Which property do you use? 
   To configure an amount to display a value in a currency format, select the report item, and then set the format property to C or c.
3. What are data regions?Data regions are report items that display repeated rows of summarized information from datasets.
4. You want to generate a report that is formatted as a chart. Can you use the Report Wizard to create such a report?
   No, the Report Wizard lets you create only tabular and matrix reports. you must create the chart report directly by using the Report Designer.
5. 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?
   You can right-click the project in Solution Explorer and then change the Target-Server URL property.
6. Which rendering formats are affected by the PageSize properties?Because only the Adobe PDf file, Word, and Image rendering extensions use physical page breaks, they are the only formats that are affected by the PageSize properties.
7. Can you use a stored procedure to provide data to an SSRS report?Yes, you can use a stored procedure to provide data to an SSRS report by configuring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.
8. You want to use a perspective in an MDX query. How do you select the perspective?     Multidimensional Expressions (MDX) lets you query multidimensional objects, such as cubes, and return multidimensional cellsets that contain the cube's data. This topic and its subtopics provide an overview of MDX queries.  Use the Cube Selector in the MDX Query Designer to select a perspective.
9. Can you use data mining models in SSRS?Yes, you can use the DMX Designer to create data mining queries for SSRS reports. However, do not forget to flatten the result set returned by the DMX query.
10. You want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink?Create a text box item, set the action to Go To URL, and then configure the URL.
11. You want a report to display Sales by Category, SubCategory, and Product. You want users to see only summarized information initially but to be able to display the details as necessary. How would you create the report?Group the Sales information by Category, SubCategory, and Product. Hide the SubCategory group and set the visibility to toggle based on the Category item. Hide the Product category group and set the visibility to toggle based on the SubCategory item.
12. You want to create an Excel interactive report from SSRS. In SSRS, can you create the same interactive experience in Excel that you would have on the Web? No, you cannot create the same experience with SSRS. you can, however, use Excel to create such an experience.
13. What is the main difference between a Matrix report item and a Table report item? The main difference between a Matrix and a Table report item is in the initial template. Actually, both report items are just templates for the Tablix data region.
14. When you do not use report caching, is it better to use parameters to filter information in the query or to use filters in the dataset?From a performance perspective, it is better to use parameters because they let SSRS pull filtered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.
15. How do you configure a running aggregate in SSRS?You can use the RunningValue function to configure a running aggregate.
16. What is the main purpose of a report parameter?
    The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select.
17. What is the main purpose of a query parameter?
    The main purpose of a query parameter is to filter data in the data source.
18. You want your users to select a parameter from a list of values in a list box. How should you configure the parameter?
    You should create a data source that contains the possible values and then bind the data source to the parameter.
19. What is the main benefit of using embedded code in a report?The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.
20. What programming language would you use to create embedded functions in SSRS?
    An SSRS report supports only visual Basic .nET embedded code.

21. How do you reference an embedded function in a report expression?Use the Code prefix and the name of the function to reference an embedded function in a report expression.
22. Which of the following are valid options for deploying a report? (Choose all that apply.)a. With BIDS
    b. With the Computer Management console
    c. With the .nET START command
    d. With SSMS
    e. With Report Manager
    The correct answers are a and e, BIDS and Report Manager.
23. Why should you not overwrite a shared data source in production?You should not overwrite a production-shared data source because the administrator has probably already changed some connection string properties.
24. Can you edit a report that an end user created by using Report Builder in BIDS?
    Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.
25. How can you manage reports from your application if the report server is deployed in SharePoint integrated mode?
    Use the ReportService2006 endpoint of the SSRS Web service if your report server is deployed in SharePoint integrated mode.
26. In which processing mode of a report viewer control can you use the full functionality of your report server?You should use the remote processing mode to use the full functionality of your report server.
27. What types of roles are available in SSRS 2008, and what are their purposes?
    Item-level roles and system-level roles are the two types of roles available in SSRS 2008. An item-level role is a collection of tasks related to operations on an object of the report object hierarchy of SSRS 2008. A system-level role is a collection of tasks related to operations on server objects outside the report object hierarchy of SSRS 2008.
28. Can a user or group belong to more than one item-level or system-level role?
    Yes, in SSRS 2008, a user or group can have more than one association to a system-level or an item-level role.
29. When storing the credentials of a data source in the server, are those credentials safe?Yes, the data source credentials are safe because Reporting Services encrypts them and stores them in the ReportServer SQL Server database.
30. What happens if you do not specify a parameter value in a subscription and the parameter does not have a default value?
    If you do not specify a parameter value in a subscription and the parameter does not have a default value, the execution of the report will fail.
31. You want to create a subscription to a report. However, when you right-click the Subscription subfolder of the report, you notice that the new Subscription option is dimmed. What is wrong? When the new Subscription option is dimmed, the report probably does not use stored credentials for accessing the data. SSRS needs these credentials stored in its own ReportServer database to execute a report on a schedule.
32. What can you do if your query with properties for a data-driven subscription does not provide values for all of the subscription properties?If your query with properties for a data-driven subscription does not provide values for all of the subscription properties, you can use text and default values instead. These values are then used for parameters of all subscriptions you get from the query.
33. What mechanisms do you identify to reduce the overhead of Reporting Services data sources?
    Snapshots and cached reports can help reduce the processing pressure on data sources and improve report response time.
34. Can you always create a cache of a report? No, you can create a cache of a report only when certain requirements, such as having credentials stored in the Report Server, are met.
35. Can you edit the .rdl code associated with a linked report?No, because a linked report has no .rdl code of its own. It refers to the .rdl code of the base report.
36. Which of the tools and utilities described in this lesson can change or create the virtual directories for the report server and Report Manager after installation? Only Reporting Services Configuration Manager can enable and name the virtual directories for the report server and Report Manager.
37. What is the file name extension for an encryption key backup?
    Encryption key backups have an .snk file name extension.
38. What are the three SSRS command-line utilities and their primary functions?RSConfig.exe is used to define the connection properties from the SSRS instance to the Report Server database; RSKeyMgmt.exe performs encryption key operations and scale-out deployment setup; RS.exe runs Report Server Script files that can perform report deployment and management operations.
39. If you want to disable one of the rendering options in the Save As drop-down list when viewing a report through Report Manager, where do you do that? The visibility property for any of the rendering devices can be changed by modifying the RSReportServer.config file and locating the <ExtensionName="RenderDevice"/>  tag for the specific device.

----------------------------------------------------------------------------------------------------------------------------------

This article describes some common important interview questions and answers in SSRS. Hope it will help you to build successful carrier.

What is SQL Server Reporting Services or SSRS?
SQL Server Reporting Services or SSRS is a server based report generation software system. It is developed by Microsoft Corporation. It is administered via a web interface. It is used to generate and deliver interactive and printed reports. We can use it instead of Crystal Reports and other reporting tools. The entire report and data source definition is stored as a simple XML file. The reporting engine uses this file to render reports.

What is the History of SSRS
Microsoft released SSRS in 2004 for SQL Server 2000. The second version was released in November 2005 for SQL Server 2005. The latest version was released in April 2010 for SQL Server 2008 R2.

What are the Reporting Service Components in SSRS?

• Report Designer -A place where the report is designed or created.
• Report Server -Provides services for implementation and delivery of reports.
• Report Manager -A Web-based administration tool to manage the Report Server.

What are the core components of SSRS?
SSRS includes the following core components:

• A complete set of tools that can be used to create, view and manage report.
• A Report Server component that hosts and processes reports in a variety of formats like HTML, PDF, TIFF, Excel, CSV, and more.
• An API that allows developers to integrate in custom applications or to create custom tools to build or manage reports.

What is Data Set in report?
Data set is a set of data which we want to show in report. Data source is the source of data from where we are getting this data (database server name, database name, connection string).

Is SSRS support other database except MS SQL Server?
Yes. SSRS can be building based on relational or multidimensional data source like Oracle, OLEDB. ODBC etc

What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.

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

What are the Types of SSRS?
The types of SSRS are given below:

• Parameterized reports
• Linked reports
• Snapshot reports
• Cached reports
• Ad hoc reports
• Clickthrough reports
• Drilldown reports
• Drillthrough reports
• Subreports

What are Advantages of SSRS or why we should use SSRS?
The SQL Server Reporting Services or SSRS has some Advantages. Such:

• It is faster and cheaper.
• Efficient reporting access to information residing in both Oracle and MS SQL Server databases.
• No need for expensive specialist skills.
• The default report designer is integrated with Visual Studio .NET so that we can create application and its reports in the same environment.
• The security is managed in a role-based manner and can be applied to folders as well as reports
• Once parameters are defined, the UI for these parameters is automatically generated.
• Subscription based reports are automatically sent by mail to the users.

What are the Export Options of SSRS?
SSRS allow many ways of rendering the reports:

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

Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server?
Yes we can. SQL Server Express Edition with Advanced Services support Reporting Services. These is the free version.

What are the limitations in Reporting Services on SQL Server Express Edition?Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition. But it has the following limitations:

• Management Studio cannot be used to administer report server
• Report Models will not be available
• Report Builder is not available
• Caching, History and Delivery of Report is not available.
• SQL Server agent is not available
• No scheduling is possible.
• Remote server database is not available for Report Data Source (Local SQL Server is a only option,).
• We cannot store the report server database on a remote server (it has to be local only).
• Reports can be rendered only in Excel, PDF, Image formats only
• Reporting Services will not be able to use more than 1 GB of RAM
• No Subscriptions (Standard and Data Driven) can be made
• Can not be integrated with Share Point
• Can not implement Role based security.
• Only named instances is supported
• Scale-out Report Servers will not be available

What are the tools available in market as an Alternative to SQL Server Reporting Services?
Non-Open Source:

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

Open Source:

• Jasper Reports
• JFreeReport
• BIRT (Business Intelligence Reporting Tools)
• OpenReport
• DataVision
• Pentaho

What is reporting lifecycle?
Generally a Reporting Services has three mainly three phases:
Development of Reports (Developer) – at first reports need to be developed and it is done the developers.
Management of Reports (DBA) – When the Report is being developed DBA ensure the following things:

• Security – Only authorized user should access the report.
• Execution – How the report will be executed to optimize data sources performance.
• Scheduling of Reports – so that report are executed on scheduled timings.

Report Delivery (DBA + Developer) – When the report is being developed and executed the report is transferred to the business users. They use it and if any modification is required the report is go back to the development stage.

How to deploy the Report?We can deploy SSRS report in three ways.

1. Using Visual Studio -In visual studio we can directly deploy the report through Solution explorer by providing the report server URL in project properties at Target Server URL. As our choice this will deploy entire project or single report as.
2. Using Report Server – We can directly go to the report server and deploy the report by browsing the report from the disk location of server.
3. Creating the Utility -SQL server provides the facilities to Create a customize utility to deploy the report.

What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It is built in with the report server and can be customized.

What is the web service used for reporting services? What is Reporting Services Web service
The web service used in reporting service or SSRS is Reporting Services Web service. With this Web service SSRS provides a single entry point to the full functionality of the report serve. These Web service uses SOAP (Simple Object Access Protocol) over HTTP and acts as a communications interface between client programs and the report server. The Web service and its methods represent the functionality of the report server and allow us to create custom tools for any part of the report life cycle (from management to execution).

What are the New Features of SQL Server 2008 R2 Reporting Services?
In SQL Server 2008 R2 we get a lot of new features. Like:

• New Report Types – Table, Matrix, List, Chart, and Sub report
• Some New Tools is added to report designer Toolbox
• Report Data Panel – built in page numbers
• Report Builder 3.0

What are the new features are introduced in SQL Server 2012 reporting services?
SQL Server 2012 has introduced a lot of new features like

• Power View – interactive data exploration
• SharePoint integration
• Introduction to Data Alerts
• SQL Server Data tool
• New rendering extensions (supports MS Office 2010)
• Project Crescent is being introduced

What is Sub report?
Sub Reports is on kind of child report which opens in main report when main report loads. We can pass parameter to sub report.

Can sub report data source be different from that of the parent report?
YES.

What is report rendering?
To Exporting a report data with different type of file format is knows as Report rending. SSRS supports multiple rendering extensions like Word, Excel, CSV, PDF, HTML etc.

Can I disable or restrict SQL Server Reporting Services Export Formats (Rendering Formats)?
YES.
We can control this using report services server configuration file which is available at “C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\Report Server\ rsreportserver.config“.

What is the RDL file?
RDL stands for Report Definition Language. When we save a report then than the file is saved as ReportName.rdl. It is a XML file. This RDL file is used for deploying report to report server.

----------------------------------------------------------------------------------------------------------------------------------

What is SQL Server Reporting Services?

Reporting Services helps business to Transform raw data (table data) into graphical like chart, histogram etc. to add meaning to raw data.

For example, the table below having a data but the moment that data was presented in chart, that added more business meaning to data.

SQL Server Reporting Services can query and display data from any of the multiple databases and represent in a way which is more understandable to users.

What are the tools available in market as an Alternative to SQL Server Reporting Services / (Business Intelligence tools) ?

Market is full of business intelligence tools here are few listed of them,

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

Open Source solutions

· Jasper Reports

· JFreeReport

· BIRT (Business Intelligence Reporting Tools)

· OpenReport

· DataVision

· Pentaho

What is reporting lifecycle ?

Reporting Services has three mainly three phases

1. Development of Reports (Developer) – First of all a report needs to be design which is primarily done by report developer

2. Management of Reports (DBA) – Once the Report is being developed, DBA need to ensure

1. Security – Only authorized user should access the report

2. Execution – How the report will be executed to optimize data sources performance

3. Scheduling of reports – so that report are executed on scheduled timings

3. Report Delivery (DBA+Developer) – Once the report is being developed and executed now the report should be reached to final recipients (business users) who are going to understand / analyze report data. if any changes, we again go back to development stage.

What is Reporting Services Architecture ?

Reporting Services is a .NET framework-based platform that includes a comprehensive tools that we can use to integrate reporting solution into any centrally-managed environment. CLICK HERE(http://goo.gl/LwBRj) for more details

What can we do with SQL Server Reporting Services ?

Reporting Services can help you in building and deploying fully interactive scorecards, dashboards, and enterprise reports.

SQL Server Reporting Services Feature

· Design ad-hoc dashboards and reports quickly

· Data is interactive and available everywhere even on your IPAD / IPHONE.

· This is bundle product with SQL Server database services, so there is additional cost for this service.

· .Net integrated for rapid development for report even in few clicks by using wizards.

By using Reporting Services, we can build up a solution which is kind of Google maps, where we can list our all business locations.

Check out video post to learn, “How to Integrate Map with SQL Server Reporting Services”

What are the New Features of SQL Server 2008 R2 Reporting Services ?

Lot of new features were added in SQL Server 2008 R2, like

· New Report Types – Table, Matrix, List, Chart, Sub report

· New Tools added to report designer Toolbox

· Report Data Panel -  built in page numbers

· Report Builder 3.0

For details for new feature of SQL Server 2008 R2, please check my previous post, “SQL Server Reporting Services R2 – New Features”

What are the new features are introduced in SQL Server 2012 reporting services?

SQL Server 2012 has introduced a couple of new features like

· Power View - interactive data exploration

· SQL Server 2012 is fully integrated with SharePoint

· Introduction to Data Alerts, data alerts are a data-driven alerting solution that informs you about changes in report data that are of interest to you, and at a relevant time

· SQL Server Data tool

· new rendering extensions supports MS Office 2010

· Project Crescent is being introduced

DBA (Production Support) Prospective

What are the tasks which are mainly performed by a production DBA on a SQL Server Reporting Services ?

Operational support DBA are mainly responsible for

· Install and Configure reporting services

· Backing up Reporting Services (including databases, config files and encryption keys)

· Deploying new reports

· create and manage shared data sources

· Optimizing reports – Enable caching of reports

· Authorize users for reports

· Create and deploy security roles.

· Check Reporting Services Error Logs for errors

· Maintain response time of reporting services

Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server ?

Yes, SQL Server Express Edition with Advanced Services support Reporting Services. we can use SQL Server Express Edition for deploying reporting Services.

What are the limitations in Reporting Services on SQL Server Express Edition ?

Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition but following restrictions will be applicable to this edition.

· SQL Server Management Studio Express cannot be used to administer report server

· Report Models will not be available

· Other features like Caching, History and Delivery of Report is not available.

· Report Builder is not available

· Local SQL Server is a only option, which can be used as Report Data Source.

· We cannot store the report server database on a remote server, it has to be local only.

· Reports can be rendered only in Excel, PDF, Image formats only

· No SQL Server agent is available, so no scheduling is possible.

· Reporting Services will not be able to use more than 1 GB of RAM

· No Subscriptions (Standard and Data Driven) can be made

· Can not be integrated with Share Point

· Only named instances is supported

· Scale-out Report Servers will not be available

· Can not implement Role based security.

How to Install SQL Server Express with Reporting Services ?

For Step by Step instructions, Please check my previous post, “How to Install SQL Server Express with Reporting Services”

What are the parameters which should be considered for Reporting Services Disk storage requirement ?

There are various factors to be considers as a primary I would consider

1. Number of reports to be hosted server

2. Report size and frequency of reports

3. Number of snapshots to be saved

Can I configure SQL Server Reporting Services on a Windows cluster for High availability ?

Please check my previous post, “Installing & Configuring SQL Server Reporting Services on Cluster Environment for High Availability” to understand how reporting services works on cluster.

What is ReportServer and ReportServerTempDB ?

Reporting Services uses two SQL Server databases for storage by default, the databases are namedReportServer andReportServerTempdb.

ReportServer is a main database, which store all internal configuration and report meta data where asReportServerTempdb is used to store temporary data, session information, and cached reports.

How to backup SQL Server Reporting Services ?

There are mainly three things, which should be backed up as part of reporting services backup

1. Report Server Databases, which can be backed up by SQL server backup and restore method. Check my previous Video post, “HOW to BACKUP SQL”

2. SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.

3. Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.

What is encryption key ?

Encryption keys are used by the report server so that items such as connection strings are maintained securely. these keys are required in case you want to perform restoration of report server databases.

How to backup encryption key ?

Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.

Can we install a 32-bit version of SQL Server Reporting Services on a computer that is running a 64-bit version of Windows?

YES, we can install SQL Server 32 bit on Windows 64 bit version. SQL Server Reporting services are part of SQL Server.

Where the SQL Server Reporting Configuration is saved, is that in registry ?

NO, all SQL Server configuration is saved in configuration files (.xml files)

What are the key configuration files for SQL Server Reporting Services ?

Mostly all Configuration files located at Install Directory\Microsoft SQL Server
\<SQL Server Instance>\Reporting Services\ReportServer and  ReportManager

· RSReportServer.config stores configuration settings for feature areas of the Report Server service: ReportManager, the Report Server Web service, and background processing.

· RSSrvPolicy.config stores code access security policies for the server extensions.

· RSMgrPolicy.config stores code access security policies for Report Manager

· ReportingServicesService.exe.config stores configuration settings that specify the trace levels and logging options for the Report Server service.

Other Files

· RSReportDesigner.config contains settings for Report Designer and this file is located in the..\Program Files\Visual Studio 9.0\Common7\IDE\PrivateAssemblies

· RSPreviewPolicy.config stores server extensions used during report preview and this file is located in ..\Program Files \Microsoft SQL Server\100\Tools \ReportDesigner

What are the mostly used PERFMON Counters for monitoring SQL Server Reporting Services?

This is bit tricky and everyone has his own answer, but I personally choose the following at least

· ReportServer: Service Performance Object \ Active Connections

· ReportServer: Service Performance Object \ Report Executed per second

· ReportServer: Service Performance Object \ Total Cache Hits

· ReportServer: Service Performance Object \ Total Requests

· Web Service \ Deliveries per second

· Web Service \ Total processing failures

What all should be backed as part of reporting Services ?

1. Report server databases (ReportServer and ReporterverTempdb)

2. Encryption keys and

3. Configuration Files (xml files)

1. RSReportServer.config,

2. RSSrvPolicy.config,

3. RSMgrPolicy.config,

4. ReportingServicesService.exe.config,

5. RSReportDesigner.config and

6. RSPreviewPolicy.config

How to backup up Encryption Keys?

Use SQL Server Reporting Services Configuration tool to backup symmetric keys

Questions for Developer as well as Developers (depends on profile)

What are the tools which are being offered by Microsoft SQL Server for Developers?

· Report Designer, used to develop reports in Business Intelligence Development Studio (BIDS) and then publish them to a Report Server

· Model Designer, used create a report model that Report Builder users uses to help them build on-demand reports.

· Report Builder – used to build reports without having to understand the underlying data source structures in the database

Note – These are built in tools with Microsoft SQL Server, there is no additional cost for using these tools.

What are the tools which are being offered by Microsoft SQL Server for DBA’s?

· Report Manager – a web-based report access and management tool to browse existing reports, upload new reports, and other properties like execution properties, security, and subscriptions.

· Reporting Services Configuration –used to configure a report server like report server databases, SMTP server and services properties.

· SQL Server Management Studio (SSMS) -  an alternative to Report Manager, which is primarily being used by DBA’s for managing reports.

Note – These are built in tools with Microsoft SQL Server, there is no additional cost for using these tools.

How to deploy reports ?

Reports can be deployed by using BIDS (Business Intelligence development Studio ) or by Report Manager which is web based application. Please see previous post, “How to deploy report to SQL Server Reporting Services”

Can I deploy a report at multiple servers at one go ?

NO, native SQL Server Reporting services doesn’t supports this functionality but we can deploy reports directly to multiple servers at one go but this functionality is not built in, we need to buy some third party tools.

What is Report Builder ?

Report builder is used to create small reports and it a define interface. You can’t change the report interface in report builder it pre designed. You can just drag columns in the report.

Report builder creates reports on database objects available with report model project. Please check my previous Video post, ”How to Install Report Builder 3.0”

What is report rendering ?

Exporting a report data with design o different type of file types is knows as Report rending. SQL Server Reporting Services supports multiple rendering extensions like Word, Excel, CSV, PDF, HTML etc.

Can I disable | restrict SQL Server Reporting Services Export Formats (Rendering Formats) ?

YES, we can restrict Reporting services export formats.

SQL Server Reporting Services (SSRS) supports export to different formats like XML, CSV, HTML, PDF, etc., when you view the report.  This export option is available at report viewer Export drop-down list.

We can control this via report services server configuration file which is available at “C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\Report Server\ rsreportserver.config“.

For More details, please check my previous post “How to Disable/restrict SSRS Export format ?”

How to migrate SQL Server 2008 Reporting Services to another computer?

If you are trying to migrate only SQL Server reporting services to new server, then we have an option to install reporting services on new server and while configuring report server, we can point report server services to old report database database.

Or incase, you are trying to migrate report server databases and reporting service together, the install reporting services on new instance and restore configuration files  and restore report server databases and reconfigure the report server services.

How to check how frequent report is being called ?

Check Report history either from SSMS or Report manager. In report manager, go to report and select history tab. check here for more details

What is Report Caching ?

Report caching is a performance enhancing technique in Reporting Services that saves copies of reports for faster viewing. Cached instances have an expiration time set to force a refresh of the data set used
for the report.

In case you have filters in your report, when filters will be applied in Cached Report instance ?

Filters are applied when a report is rendered, Filters will not create a new cached instance on the Report Server.

What is report snapshot ?

Snapshot means a instance of a report for future reference, that means a copy of report (data is freezed) will be saved on a report server for future reference.

What is Data Driven Subscription ?

Subscriptions are used to deliver rendered reports to business users at specified schedule. For example, a sales report should go to sales manager daily at 7:00 AM for last day report. This is kind of pushing reports on a schedule. By creating subscription, we can send reports to users in e-mail or Deliver to a file share
or can put report in Microsoft Office SharePoint server

A data-driven subscription can deliver a report in many rendered formats to many destinations. For example, USER1 wants report in XML format and USER 2 wants in a pdf format and user 3 wants in word but that too on a shared drive.

To meet these objective we can use Data-driven subscriptions as data driven  subscription require a database table to store all these report values so that these values can be used during processing.

How to upgrade report from SQL Server 2005 to SQL Server 2008?

SQL Server Setup is used to upgrade SQL Server Reporting Services. Run the SQL Server 2008 setup on existing SQL Server 2005 and opt for upgrade.

Developer (Report Designer) Prospective

Any Idea, how to to build / design Report using Visual Studio?

Building Reports using Visual Studio is quite easy, Please check my previous Video POST, “How to create a report using Visual Studio 2008?” which describes

1. how to build a report using Visual Studio 2008

2. deploy report to SQL Server Reporting Services

3. How to create an shared data sources

4. How to design a report

5. How to add interactive sorting Change

6. Run the report in SSRS

What is Parameterized Reports in SSRS ?

You often need more advanced reporting such as drop down lists and the ability to use parameters when working in Sql Server Reporting Services. Please check VIDEO Tutorial, “How to make Parameterized Reports in SSRS”  which describes,

· How to use variables that have not been declared in SSRS vs TSQL

· How to create report with auto-Parameterization

· How to View Report Parameters

· How to create a drop-down list

What are different types of reports are available ?

There is as such not any type of of reports, but we can categories them as

Tabular Report – where we represent data in rows and columns format. (Now this is non as Tablix which is mixture of table and a matrix)

CHART REPORTS – where we represent the data in graphical format mainly in charts?

further more these reports can fetch data either from relational database (TSQL REPORTS category) or from CUBES (MDX Reports)

What sort of query we can write in SSRS, is that just SQL / TSQL or is there anything else too?

· MDX – for OLAP / CUBE data sources

· DMX – for data mining

Can you edit a report that an end user created by using Report Builder in BIDS?

Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.

What is Sub report ?

SUB report is very helpful when the detail of a report’s data region is so complex that a separate report is a better option then using other tools.

Can sub report data source be different from that of the parent report ?

YES, sub report data source can be different from that of the parent report.

What are the various ways to deploy reports ?

Reports can be deployed to report server by using any of the following method

· BIDS

· Computer Management console

· .nET START command

· SSMS

· Report Manager

but normally BIDS  and Report managers are mostly used.

Can we use Analysis Services Database as a data source with Model Designer ?

NO, Model Designer doesn’t access data from Analysis Services cubes.

What is report subscription?

Subscriptions are standing requests to deliver report data to requested recipients. Once the report is being subscribed and subscriber will get updates from report server on scheduled interval.

What is the RDL file?

RDL stands for Report Definition Language, when we design a report using any tool like BIDS and when we save the report, it’s saved as .rdl file, RDL file is an kind of xml code, which stores the design of the report.

This RDL file is being used  for deploying report to report server.

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

Yes, you can use a stored procedure. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.

How to deploy Reports on SQL Server Reporting Services ?

Please check my previous VIDEO post to learn, Step by Step Learning, How to deploy report to SQL Server Reporting Services 2008

How can I build my first report which is ready to deploy in SQL Server Reporting Services ?

Please check my previous blog post, Introduction to SQL Server Reporting Services, for a brief video tutorial for SQL Server Report designer.

Solution Architect prospective

IT Manager prospective

Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server ?

Yes, SQL Server 2005 Express Edition with Advanced Services support Reporting Services. we can use SQL Server Express Edition for deploying reporting Services. For more details to know SQL Server Express Limitations, please check my previous post, “What are the limitations in Reporting Services on SQL Server Express Edition”

Is there any free tools to build reports ?

YES, there are log of open source code solution are available, which can used to design reports, here are few listed.

· Jasper Reports

· JFreeReport

· BIRT (Business Intelligence Reporting Tools)

· OpenReport

· DataVision

· Pentaho

What are the product alternatives to SQL Server Reporting Services ?

Market of full of tools and here are few of them

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

How many instances of Reporting Services can be managed by a single DBA ?

This is pretty tricky questions, and there is not any single formula to decide this. This all depends on what sort of monitoring tools you have and what sort of automation you have achieved.

But as per my understanding and calculations a single 3-7 year experience DBA without any third party tool can easily deploy 25-35 report in a day, which is one time activity for most of the organizations and DBA can easily monitor / provide production support to 25-40 instances of SQL Server, which included daily

· Monitor Event Viewers

· Check SQL Server Errors

· Check Reporting Services Logs

· Monitor Perfmon counters

· Backup all databases

· check schedule sql jobs etc.

but I believe, without any tool (just using manual / Script) we can assume 1 DBA for every 25 servers is ideal number to focus more on preventive actions rather then corrective actions.

Is my data is safe, What all we are backing up for SQL Server Reporting Services ?

There are mainly three things, which should be backed up as part of reporting services backup

1. Report Server Databases, which can be backed up by SQL server backup and restore method. Check my previous Video post, “HOW to BACKUP SQL”

2. SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.

3. Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.

What is the licensing cost for SQL Server Report Server?

SQL Server Reporting Services is bundled with SQL Server. So in case you have already purchased SQL Server Standard edition for your business then there is no additional cost for using this SQL Server Reporting Services.

Do I need to buy separate licenses for users who view reports ?

NO, there is no separate licenses for report delivery or viewing report on demand, i’’s all bundelled in SQL Server with once license cost.

Is there any free edition where I can use SQL Server Reporting Services ?

Yes, SQL Server 2005 Express Edition with Advanced Services support Reporting Services but there are several limitation, check my post,”What are the limitations in Reporting Services on SQL Server Express Edition“ for these limitations.