Microsoft Technologies: SQL Server Reporting services Interview Questions and Answers

Q1. WHAT is SQL Server Reporting Services (SSRS)?
SQL Server Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection

Q2. Architecture of SSRS:

Q6. Which programming language can be used to code embedded functions in SSRS?
Visual Basic .NET Code.

Q7. Important terms used in the reporting services?

1. Report definition: The blueprint for a report before the report is processed or rendered. A report definition contains information about the query and layout for the report.

2. Report snapshot: A report that contains data captured at a specific point in time. A report snapshot is actually a report definition that contains a dataset instead of query instructions.

3. Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).

4. Parameterized report: A published report that accepts input values through parameters.

5. Shared data source: A predefined, standalone item that contains data source connection information.

6. Shared schedule: A predefined, standalone item that contains schedule information.

7. Report-specific data source: Data source information that is defined within a report definition.

8. Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.

9. Linked report: A report that derives its definition through a link to another report.

10. Report server administrator: This term is used in the documentation to describe a user with elevated privileges who can access all settings and content of a report server. If you are using the default roles, a report server administrator is typically a user who is assigned to both the Content Manager role and the System Administrator role. Local administrators can have elevated permission even if role assignments are not defined for them.

11. Folder hierarchy: A bounded namespace that uniquely identifies all reports, folders, report models, shared data source items, and resources that are stored in and managed by a report server.

12. Report Server: Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions.

13. Report Manager: Describes the Web application tool used to access and manage the contents of a report server database.

14. Report Builder: Report authoring tool used to create ad hoc reports.

15. Report Designer: Report creation tool included with Reporting Services.

16. Model Designer: Report model creation tool used to build models for ad hoc reporting.

17. Report Server Command Prompt Utilities: Command line utilities that you can use to administer a report server.
a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe

Q8. What are the Command Line Utilities available In Reporting Services?
· Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. Encrypted values include report server database connection information and account values used for unattended report processing
· RsKeymgmt Utility: Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access
· RS Utility: this utility is mainly used to automate report server deployment and administration tasks. Processes script you provide in an input file.

Q. How to know Report Execution History?
ExecutionLog table in ReportServer database store all the logs from last two months.
SELECT * FROM ReportServer.dbo.ExecutionLog

-Development
Q. What is difference between Tabular and Matrix report?
OR What are the different styles of reports?

Tabular report: A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.

Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:
    a. one group of data is displayed across the page.
    b. One group of data is displayed down the page.
    c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
    d. One group of data is displayed as the "filler" of the cells.
Matrix reports can be considered more of a Pivot table.

Q. How to create Drill-through reports?
Using Navigation property of a cell and setting child report and its parameters in it.

Q. How to create Drill-Down reports?
To cut the story short:
- By grouping data on required fields

-Then toggle visibility based on the grouped filed

Q. How to select ALL from a parameter list?
http://sqlserversolutions.blogspot.com/2011/03/select-all-in-parameter-of-ssrs-report.html

http://20fingers2brains.blogspot.com/2013/12/ssrs-2012-interview-questions-and.html

1. What are data regions?
Data regions are report items that display repeated rows of summarized information from datasets.

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

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

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

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

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

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

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

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 / analyse report data. If any changes, we again go back to development stage.

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

1. Power View - interactive data exploration

2. SQL Server 2012 is fully integrated with SharePoint

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

4. SQL Server Data tool

5. new rendering extensions supports MS Office 2010

6. Project Crescent is being introduced

9. What is ReportServer and ReportServerTempDB?

1. Reporting Services uses two SQL Server databases for storage by default, the databases are named ReportServer and ReportServerTempdb.

2. ReportServer is a main database, which store all internal configuration and report Meta data whereas ReportServerTempdb is used to store temporary data, session information, and cached reports.

10. What is encryption key?

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

================================================================

http://www.msbiguide.com/2012/12/ssrs-interview-questions-and-answers/

1. What is Query parameter in SSRS?
Query parameters is mentioned in the query of the data sources that are to be included into the SQL script’s WHERE clause of the SQL that can accept parameters. Query parameters begin with the symbol @.The name should not contain spaces and cannot begin with numeral. For clarity, we use only letters.

2. What are the Reporting Service Components in SSRS?
Report Designer: A place where we can create report. Report Server: Provides services for implementation and delivery of reports. Report Manager: A Web-based administration tool for managing the Report Server.

3. What is a matrix in SSRS?
A matrix is a data region linked to a report set. Matrix allows us to create crosstab reports with the report variables displaying on rows and columns. It allows us to drag and drop fields into it.

4. What are sub reports and how to create them?
A sub report is like any other reports which can be called in main report and can be generate through main report. Parameters can be passed from main report to sub report and basis of that report can be generated.

5. What is the report model project?
Report model project is for creating Adhoc reporting. You can create the adhoc reports through report builder. Report model project can be created on bids or report server. This model can have simple view. And using

6. What is report server project?
Report Server Project contains the RDL file and it need to be deployed on report server to view the report files to application and user. It a solution where we design our reports. You can add it by going into BIDS clicking on new item and then selecting reports server project. Once the solution is created you can start
creating reports.

7. What is the 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.

8. In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model project on report server you can get error or it might not get created. For this you need to check whether the service pack 22 is installed or not.

9. How to deploy the Report?
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly deploy the report through
solution explorer by providing the report server URL in project properties at Target
Server URL. This will deploy entire project or single report as per you selection.
2. Using report server: Can directly go to the report server and deploy the report by
browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility using that which can be used to
create a customize utility for your report deployment in bulk.

10. What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report
server and can be customize accordingly.

11. What is the name of reporting services config file and what’s it’s used for?
Reporting service config file is used for report configuration details. It contains the report
format and also the report import types. Report service config reside at ISS.

12. What are the three different part of RDL file explain them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag
columns values from source.
3. Preview: to check the preview after the report run.

13. Which language rdl files made of?
RDL files are written in XML.

14. What is the chart in report?
Chart reports are for graphical representation. You can get pie charts columns charts and various other options. 3d charts are also available in reporting services.

15. What is Data Set in report?
Data set are the set of data which we want to show in report. Data creates on data source.
Data source is the source of data from where we are getting this data i.e. database server and database name connection string.

16. What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML

17. What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all report server component and also get the report deployed on report server.

18. How to add the custom code in Report?
To add the custom codes in report go to report tab on top then properties and there you will find the options for custom code.

19. What is a cache in SSRS?
Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is known as cache and the process is called caching.

20. What is report snapshot in SSRS?
Report snapshot is a report which contains layout information and a dataset that is extracted at a particular point of time. When the new snapshot is created the previous report snapshot will be overwritten.

21. What is bookmark link in SSRS?
Bookmark is a link which a person clicks to shift to a different area or page in a report. We can insert bookmarks links only to textboxes and images.

22. What is Command parameter in SSRS?
A Command parameter is used to forward protocol to the Report Server regarding the item that has been retrieved. Command parameter is also used for rearranging a user’s session information.

23. What is Format parameter in SSRS?
Format parameter is used to control report output. Every delivering format on Report Server has to pass through this parameter.

24. What is Snapshot parameter in SSRS?
When a report is saved in a snapshot history, it is allocated a time or date to uniquely identify that report. Snapshot parameter is used to get back these historical reports by passing this time or date to get proper report.

25. What are the rendering extensions of SSRS?
Rendering extensions manage the category of document produced when a report is processed. Rendering Extensions are: HTML, MHTML, EXCEL, CSV, IMAGE, PDF, and XML.

26. What is a ReportItem in SSRS?
A ReportItem is one of the subsequent types of items: Rectangle, Textbox, Image, Subreport, Line, CustomReportItem, List, Table, Matrix or Chart.

27. What is Datasets in SSRS?
Dataset elements have information about the sets of data recovered as a part of the report.

28. What are the main components of reporting services?
The main components of Reporting Services are

1. Report designer

2. Report server

3. Report manager

4. Report user.

29. What is Report Designer?
Report Designer is a collection of graphical query and design tools that are hosted within the Microsoft Visual Studio environment.

Report Designer provides a Report Data pane to organize data used in your report, and tabbed views for Design and Preview so that you can design a report interactively.
Report Designer also provides query designers to help specify data to retrieve from data sources and the Expression dialog to specify report data to use in the report layout.
When the report is complete, you can use Report Designer to preview the report and publish it directly to the report server.

30. What is Report manager?
Report Manager is a Web-based report access and management tool that you use to administer a single report server instance from a remote location over an HTTP connection. You can also use Report Manager for its report viewer and navigation features.
You can use Report Manager to browse the report server folders or search for specific reports. You can view a report, its general properties, and past copies of the report that are captured in report history.

31. What is Report Builder?
Web-based tool to create reports
Come free with SQL Server 2005
Launch Report Builder 1.0 to create ad hoc reports that you can save and run on the report server.
Users build desired reports themselves; can export to Word, Excel, PDF, and more

32. What is Report Server?
The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations.

General SSRS Questions

Question: How do you normally create reports (wizard/manually)?
Comment: If wizard in most cases then you got rather inexperienced person, if manually than it is usually good answer. The best answer is using a template. Generally developers create reports from scratch which is not bad but it is not very efficient either.

Question: What languages have you used to query data for SSRS Reports?
Comment: Most answers will probably be SQL (TSQL). But T-SQL is not the only query language. If someone build reports based on cubes before than they will say MDX. You can also query data from other sources (not recommended) or use data mining expressions (DMX = advanced).

Question: Gives examples where you used parameters?
Comment: Typically you use parameters to filter data in datasets (or data on reports) but you can also use them to restrict values like the well-known make->model->year example. You can also have hidden and internal parameters which get very handy for more advanced stuff.

Question: What types of graphs do you normally use and what effects do you apply to them?
Comment: Good graph are bar, line, scatter, bullet graphs. Bad graphs are pie charts, area graphs, gauges (apart from bullet graph which classified as gauge in SSRS). Effects should be limited to minimum. Developers should avoid 3D effects, "glass" effect, shadows etc.

SSRS Advanced questions

Question: Have you used custom assemblies in SSRS? If yes give an example
Comment: This allows to re-use code in reports and it is not very common. Re-usability is good but building dependencies is not so good… so one small mistake might break all reports using it; so it should be used with care.

Question: Can you update report data using SSRS?
Comment: This is not often used (and probably shouldn't be used in most cases) but it is possible.

Question: What is the formatting code (format property) for a number with 2 decimal places?
Comment: N2. Attention to details and good memory is always welcome.

SSRS interview "narrowed" questions

In this section I will give you fairly long list of short and narrowed questions:

Question: What does rdl stand for?

Answer: Report Definition Language

Question: How to deploy an SSRS Report?

Answer: Configure project properties (for multiple environments) and deploy from bids, upload manually or use rs.exe for command line deployment.

Question: What is Report Manager?

Answer: Web based tool that allows to access and run reports.

Question: What is Report Builder?

Answer: Report Builder is a self-service tool for end users.

Question: What permission do you need to give to users to enable them to use Report Builder?

Answer: "Report Builder" role and "system user". Report builder should also be enable in report server properties.

Question: What do you need to restore report server database on another machine?

Answer: SSRS Encryption key

Question: Can you create subscription using windows authentication?

Answer: No.

Question: What caching options do you have for reports on report server?

Answer: Do no cache, expiry cache after x minute, on schedule.

Question: How to find slow running reports?

Answer: Check ReportExecution table

Question: How can you make calendar available in report parameter?

Answer: Set parameter data type to date.

Question: How to pass multi-valued parameter to stored procedure in dataset?

Answer: Join function in SSRS and split function in T-SQL

Question: Which functions are used to pass parameter to MDX query?

Answer: StrToMember and StrToSet

Question: How to create "dependant" parameter "Make, Model, Year"

Answer: They need to be in correct order, and previous parameter should filter next parameter dataset. For instance Model dataset should be filtered using Make parameter

Question: How to create "alternate row colour"?

Answer: use RowNumber and Mod function OR visit our tutorial.

Question: How to create percentile function?

Answer: Custom code is required. Visit our tutorial for more details.

Question: How to create median function?

Answer: Custom code is required. Visit our tutorial for more details.

Question: How to make conditional sum in SSRS?

Answer: IIF condition true then SUM else nothing. Visit our tutorial for more details

Question: How to find a value in another dataset based on current dataset field (SSRS 2008 R2)?

Answer: Use lookup function.

Question: How to change parameter value inside the report?

Answer: Set action. "Jump to itself" and pass different value for the parameter.

Question: How to identify current user in SSRS Report?

Answer: User!UserID

SSRS INTERVIEW QUESTIONS

These are SSRS ( SQL Server Reporting Services) Interview Questions so far, Will keep adding new questions. Good luck with your interview as SQL Server Report Developer :)

General

1. What is SSRS ?
2. When did first version of SSRS (SQL Server Reporting Services) was introduced in market ?

3. What are the different versions of SSRS (SQL Server Reporting Services) you have worked with?

4. What is Shared Data Source in SSRS (SQL Server Reporting Services) ?

5. What is Shared Dataset in SSRS (SQL Server Reporting Services) ?

6. What is difference between Shared Data Source and Shared Data Set?

7. What is Embedded data source and how is it different from Shared Data Source?

8. What type of reports the Report Wizard creates?

9. Can you create parameter reports by using Report Wizard?

10. What is report server and What Report server modes available?

11. RDL reports can run on Reporting Services report servers in native mode or in SharePoint mode, Is this true statement?

12. What are the stages of Report Processing?

13. What are different options available to View Report?

14. What are the different ways to sort your report data? What is interactive sort?

15. Can we display data from multiple datasets in one report?

16. What is look up in SSRS Report?

17. What is Cache shared Dataset? how can we cache that? what are advantages/disadvantages of Cache Shared Dataset?

18. Can you schedule shared Datasource refresh? if yes what condition can cause Cache to get expired?

19. What is Subscription in SSRS?

20. What are types of Subscription available in SSRS?

21. What is difference between Standard and Data Driven Subscription?

22. What are the prerequisites to create a Subscription?

23. What does Delivery Extension mean when you are going to create subscription?

24. What delivery extensions are available in Subscription? and when administrator provide Null delivery extension? what does Null delivery mean?

25. What are the important parts of Subscription?

26. What are Data Alerts in SSRS? and how does it work?

27. Can you create Data Alerts on reports which are created with Power View?

28. What are the steps involved to create data alert? and in which database data alert meta data is saved? What is role of SQL Server Agent in Data alert?

29. What is Logging in SQL Server Reporting Services?

30. What is difference between Reporting Services Execution and Trace Logging?

31. Where is the execution log stored?

32. Where can you find Report Server Service Trace Log? and what type of information does it provide?

33. What is Power View? can we use Power view feature in Native mode?

34. What are two data models that can be used by Power view to create reports?

35. Explain difference between Report Designer, Report Builder and Power View

36. If you have created a report with Report builder, can you open that with Power View?

37. If you have created a report with Power View, Can you modify by using Report Builder?

38. What is extension of report that Report Builder/Report Designer creates?

39. What is extension of report that you create with Power View?

40. What are seven rendering extensions available in SSRS?

41. What is Reporting Services Configuration Manager?

42. What is Report Manager? Explain the function of Report Manager.

43. What is running total? and what function will you use to include running total in new column in SSRS?

44. If you want to display only 10 rows per page in SSRS, how will you achieve that?

45. What is page break in SSRS Report?

46. If you want to display header on each of new page, how would you do that?

47. If you want to display data for each group on new page, how would you do that?

48. You have report that has multiple pages with different group data, you want to render to excel but each group data should go to separate sheet, how would you accomplish that?

49. We can insert image in report , what image sources are available for us to use?

50. What security roles are available in SSRS?

51. What is Report Parts in SSRS?

52. What is List report Item?

53. What is Indicator Report Item?

54. What is Sparkline and Data bar item in SSRS?

55. In which version of SSRS Maps were introduced? SSRS uses Google Maps, is this correct statement?

56. Do you need to have Internet Information Services(IIS) installed on server in order for SSRS 2008 to work properly?

57. To hide the Parameter in SSRS, which property do you have to use?

58. What types of images can you embed in reports?

59. What are the components of SSRS?

60. Which tool is available in SSRS to help create business users their own reports?

61. Which delivery options are allowed in SSRS?
62. How to display all the records on one page in SSRS?
63. Which Business Intelligence Tools (BI Tools) have you worked with?

Report Types

1.       What are different types of SSRS reports available?
2.       What is different between Matrix and Tablix Report?
3.       What is difference between drill down and drill through report?
4.       What is Sub report?
5.       What is parameterized report?
6.       What is Linked Report?
7.       What is snapshot report?
8.       What is cached report?
9.       What is Ad hoc report?
10.   What is clickthrough report?
11. What is Dashboard report and how would you create it?
12. What is Scorecard Report in SSRS?

Parameters

1. What type of parameters available in SSRS?

2. What is Single Value parameter?

3. What is Multi value parameter?

4. If you have to create single value parameter report , how would you write your T SQL Query?

5. If you have to create multi value report , how would you write your T SQL Query?

6. If you need to display a calendar as one of your parameter , how would you do that?

7. Is there a way to provide default value to your parameter?

8. You have created a report, the report has month name as parameter, What is best way to provide values for month name parameter?

9. What is Cascading parameter in SSRS?

10. What are range parameters?

11. Can you show Radio buttons as your parameter in SSRS, if yes which data type these parameters work for?

12. What data types are available for Parameters in SSRS?

13. How do you set parameter on a Drill through report?

14. How do you set parameter on a Sub report?

Conditional Formatting, functions

1. What is difference between IIF and Switch Function?

2. If you need to color a cell which depends on value such as red for negative and green for positive, how would you do that?

3. How to remove Null values in Cell with some default value?

4. How would you hide empty row in SSRS?

5. Name few of the functions you have used in expressions (String and date or math functions etc.)

6. Which function would you use to add a new column that will generate sequence number for your all of your records?

7. Let’s say we are in a situation where we need to write a special function that is not available in SSRS, how would you accomplish that and which language would you use to use it in your SSRS report?

Scheduling

1. What is shared schedule in SSRS?

2. why would you use SSRS scheduler?

3. Which roles have permission to create, modify or delete shared schedules?

4. What is the schedule information saved ( Report Server Service, Report Server interface, Share Point site Or Report Server Database)?

5. If SQL Server Agent is disabled on SQL Server , would your reports run on defined schedule?
6. How would you generate SSRS report with DateTime in a shared folder on schedule ? (Answer)

Deployment

1. What is SSRS deployment?

2. How would you deploy your report using BIDS/SSDT?

3. What is RS Utility and why do we use it?

4. What are the steps to deploy your report using Report Server?

5. what are the steps to deploy the reports on sharepoint site?

What is SQL Server Reporting Services?

SSRS is reporting tool which comes with Microsoft SQL Server. It’s a part of BI suite which SQL Server incorporates with it.

SSRS is free with Microsoft SQL Server and used to create reports for client purpose. With reporting service reports can be created in different format such as tabular, matrix.

What are the different types of possible reports?

Report can be of three types in terms of format

1. Tabular Report: In this format report will come in tabular format. Fields for which data required are in columns and data comes in the form of row. It’s a static report no change in the format is possible at run time (i.e. No of columns will remain same)

2. Matrix Report: Matrix reports are the dynamic reports and columns can be changed at run time according to the data.

3. Chart Report: In chart report chart can be used in report for graphical representation of the data. You can create dashboard using various charts and gauge.

What is the extension for report file?

Report file saves with the extension of .rdl and same file exist at report server.

What are the shared data sources in SSRS?

Shared data source is the source which can be shared across the different reports. Then need of created a separate data source each time can be removed. Reports firing on same databases can used the same data source.

What are the different types of report?

Using BIDS reports can be created in two ways. I.e. two ways to retrieve the data from SQL Server data source.

1. TSQL Reports: TSQL Reports are made up of plain SQL Query. Data source in this case is SQL server database engine.

2. MDX Reports: MDX reports are created through cubes. For this data source is analysis services cubes.

What is the report rendering?

Report rendering is to call the report from server to application. Report rendering can be possible through different ways.

What are the different types of report rendering methods?

There two methods through which we can render the report.

1. URL Method

2. SOAP Method

What is the report server?

Report server is the server where we deploy the report. I.e. it’s a holding place for reports. Applications access report server to view the report.

What are the different folders on report server?

There are two folders which exist on report server.

1. Report Server: Report server contains the xml code of rdl.

2. Report : Report folder stores the actual RDL file and which can be run by clicking on it on server

What is URL Methods of rendering?

URL Method is report rendering method in which you provide the report details through URL.

URL method contains the report server details, parameter details and login details in the url itself to view the report in report viewer control.

Which language rdl files made of?

RDL files are written in XML.

What is the chart in report?

Chart reports are for graphical representation. You can get pie charts columns harts and various other options.

3d charts are also available in reporting services.

What is Data Set in report?

Data set are the set of data which we want to show in report. Data creates on data source. Data source is the source of data from where we are getting this data i.e. database server and database name connection string.

What are the different types of data sources in SSRS?

SSRS use different data source. Some of them are listed below.

1. Microsoft SQL Server

2. OLEDB

3. Oracle

4. ODBC

5. SQL Server Analysis Service

6. Report Server Model

7. SAP Net weaver BI

8. Hyperion

9. Teradata

10. XML

What is the web service used for reporting services?

Reporting Service Web Service used in SSRS. By accessing this web service you can access all report server component and also get the report deployed on report server.

In which SQL Server version report builder introduced?

Report builder introduced in SQL Server 2005. While creating or deploying report model project on report server you can get error or it might not get created. For this you need to check whether the service pack 2 is installed or not.

How to deploy the Report?

Report can be deployed in three ways.

1. Using visual studio: In visual studio you can directly deploy the report through solution explorer by providing the report server URL in project properties at Target Server URL. This will deploy entire project or single report as per you selection.

2. Using report server: Can directly go to the report server and deploy the report by browsing the report from the disk location on server.

3. Creating the utility: SQL server provides the utility using that which can be used to create a customize utility for your report deployment in bulk.

What is RS.exe utility?

Rs.exe utility is used for deploying the report on report server. It comes with the report server and can be customize accordingly.

What is the name of reporting services config file and what’s it’s used for?

Reporting service config file is used for report configuration details. It contains the report format and also the report import types. Report service config reside at ISS.

What are the three different part of RDL file explain them?

In visual studio RDL files has three parts.

1. Data: It contains the dataset on which we write the query. Data set is connected with data source.

2. Design: In design you can design report. Can create tables and matrix reports. Drag columns values from source.

3. Preview: to check the preview after the report run.

What are the drilldown reports? Give one example.

Drill down reports are the reports which can collapse or expand as per requirement i.e. information can be drilled down in to minor level information if required.

For example when you group by the data at particular group. At the group level you can get the sum or any other information which you want to see. When you expand it you will get the row level data.

For drill down there will be + sign on the row through which you can drill.

What are sub reports and how to create them?

Sub reports are the reports which can be called in main report and can be generate through main report.

Parameters can be passed from main report to sub report and basis of that report can be generated.

What is the report model project?

Report model project is for creating Adhoc reporting. You can create the adhoc reports through report builder.

Report model project can be created on bids or report server. This model can have simple view. And using

What is report server project?

Report Server Project contains the RDL file and it need to be deployed on report server to view the report files to application and user.

It a solution where we design our reports. You can add it by going into BIDS clicking on new item and then selecting reports server project. Once the solution is created you can start creating reports.

What is the 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.

hat is report subscription?

Report subscription is to schedule the resource on particular time and to send a mailer to particular users.

You can find this functionality on report server for each report.

What is the RDL file?

RDL File stands for report definition language. It contains the report definition which located on report server.

It contains the report layout and data source and datasets.

What are the Matrix reports and what are their uses?

Matrix reports are the reports which used to generate data dynamically i.e. table structure is not static and it can be changed at run time.

How to pass cascaded parameters in report?

Cascaded parameters are the parameters which are depended on other parameters. These parameters can be created in parameter section in upper window while creating the report.

How to implement group by in report?

Group by can be implemented by adding a group on row in table data part. Adding group will two group rows for this row on above and below of this particular row.

Below row can be used for subtotal of this group which is specified in above row. By repeating the same operation on group row can created a group on group.

What is the report rendering?

Report rendering is to call the report from server to application. Report rendering can be possible through different ways.

What are the different types of report rendering methods?

There two methods through which we can render the report.

1. URL Method

2. SOAP Method

What is the report server?

Report server is the server where we deploy the report. I.e. it’s a holding place for reports. Applications access report server to view the report.

What are the different folders on report server?

There are two folders which exist on report server.

1. Report Server: Report server contains the xml code of rdl.

2. Report : Report folder stores the actual RDL file and which can be run by clicking on it on server

What is URL Methods of rendering?

URL Method is report rendering method in which you provide the report details through URL.

URL method contains the report server details, parameter details and login details in the url itself to view the report in report viewer control.

SSRS Interview Questions 1

SQL Server Reporting Services (SSRS) Interview Questions 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?
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.

SSRS Interview Questions

Q1. WHAT is SQL Server Reporting Services(SSRS)?
SQL Server Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection

Q2. Architecture of SSRS:

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIjGrMFWJcI_7u3ZGou6iMzWDs1DgqfeEB-kUxDeEiZGnMbYy0_-gQdofxjntDYSfxL_QzAAMTaFxpUHV_lIoliQ1yqnJl8g9jotRgontyEDsnMoZQ4CYW1_J5P3ht7k87gVNQeb_Ef3Q/s320/ssrsa.PNG

-Admin

Q3. What are the three stages of Enterprise Reporting Life Cycle ?
a. Authoring
b. Management
c. Access and Delivery

Q4. What are the components included in SSRS?
1. A Complete set of Tools that can be used to create, manage and view reports
2. A Report Server component that hosts and processes reports in a variety of formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
3.An API that allows developers to integrate or extend data and report processing in custom applications, or create custom tools to build and manage reports.

Q5. What is the benefit of using embedded code in a report?
1. Reuseability of Code: function created in embedded code to perform a logic can be then used in multiple expressions
2. Centralized code: helps in better manageability of code.

Q6. Which programming language can be used to code embedded functions in SSRS?
Visual Basic .NET Code.

Q7. Important terms used in the reporting services?

1. Report definition: The blueprint for a report before the report is processed or rendered. A report definition contains information about the query and layout for the report.

2. Report snapshot: A report that contains data captured at a specific point in time. A report snapshot is actually a report definition that contains a dataset instead of query instructions.

3. Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).

4. Parameterized report: A published report that accepts input values through parameters.

5. Shared data source: A predefined, standalone item that contains data source connection information.

6. Shared schedule: A predefined, standalone item that contains schedule information.

7. Report-specific data source: Data source information that is defined within a report definition.

8. Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.

9. Linked report: A report that derives its definition through a link to another report.

10. Report server administrator: This term is used in the documentation to describe a user with elevated privileges who can access all settings and content of a report server. If you are using the default roles, a report server administrator is typically a user who is assigned to both the Content Manager role and the System Administrator role. Local administrators can have elevated permission even if role assignments are not defined for them.

11. Folder hierarchy: A bounded namespace that uniquely identifies all reports, folders, report models, shared data source items, and resources that are stored in and managed by a report server.

12. Report Server: Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions.

13. Report Manager: Describes the Web application tool used to access and manage the contents of a report server database.

14. Report Builder: Report authoring tool used to create ad hoc reports.

15. Report Designer: Report creation tool included with Reporting Services.

16. Model Designer: Report model creation tool used to build models for ad hoc reporting.

17. Report Server Command Prompt Utilities: Command line utilities that you can use to administer a report server.
a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe

Q8. what are the Command Line Utilities available In Reporting Services?
· Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. Encrypted values include report server database connection information and account values used for unattended report processing
· RsKeymgmt Utility: Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access
· RS Utility: this utility is mainly used to automate report server deployment and administration tasks.Processes script you provide in an input file.

Q. How to know Report Execution History?
ExecutionLog table in ReportServer database store all the logs from last two months.
SELECT * FROM ReportServer.dbo.ExecutionLog

-Development
Q. What is difference between Tablular and Matrix report?
OR What are the different styles of reports?

Tablular report: A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.

Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:
    a. One group of data is displayed across the page.
    b. One group of data is displayed down the page.
    c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
    d. One group of data is displayed as the "filler" of the cells.
Martix reports can be considered more of a Pivot table.

Q. How to create Drill-through reports?
Using Navigation property of a cell and setting child report and its parameters in it.

Q. How to create Drill-Down reports?
To cut the story short:
- By grouping data on required fields

-Then toggle visibility based on the grouped filed

Q. How to select ALL from a parameter list?
http://sqlserversolutions.blogspot.com/2011/03/select-all-in-parameter-of-ssrs-report.html

Home >SQL Server >Interview questions and answers

SSRS Interview questions and answers

SQL Server Reporting Services skills are required in more and more IT Developer Jobs so if you need to interview someone with SSRS skills and you do not know much about the tool itself than it is worth knowing what are good SSRS Interview questions and answers. Currently I just prepared not very structured questions but later on I will try to structure them so you can ask basic junior questions for junior positions and senior questions for senior positions. These are Developer related question and are not so much applicable for DBA roles and they are not necessarily commonly asked questions.

Who is it for?:

· People doing SSRS Interviews (face to face)

· Recruiters trying to check the candidate's proficienty with SSRS

· Candidates who can prepare better for the interview (You won't get explicit answers here)

How to use it?
I suggest to take the questions applicable to the job, include the ones you already have; give each question importance factor (1 to 3) and score the candidate answers between 1 to 10 (and multiply by question importance factor). Choose the best candidate… not always the highest score.

The questions and answers below are for SQL Server Reporting Services 2008 R2 but I will also explain briefly differences for 2008 and 2005 (if needed). They are usually open questions and allow the candidate to talk about their experience with SSRS but I also give comments how to interpret the answers (best practise). Remember if you know exactly what you need (or you know how you work) make sure you include this kind of questions and make them very clear to the candidate so they have a chance to answer them (without guessing).

Below are categorized SSRS interview questions and answers:

General SSRS Questions

Question: Which versions of SSRS have you used?
Comment: Differences between 2005 and 2008 are quite big so if someone hasn't used 2008 or 2008 R2 before (or vice versa) than make sure you know this will require a few days to get up to speed with the new/previous version. If the candidate used several versions ask to describe the differences between them.

Question: Have you got samples of your work?
Comment: If SSRS is main skills for the role than it is worth asking for samples (before interview) they will often tell you a lot about the candidate quality of work.

Question: Do you have Data Visualization skills? (optional)
Comment: If you need dashboards then it is worth asking this question (we will ask more specific questions later). This is also related to previous question. When you review samples ask the candidate or yourself two questions: What is the purpose? And is it useful? Someone who does not know anything about data visualization will usually use pie charts, gauges (exception is bullet chart which is classified as gauge in SSRS), 3D effects, make colourful designs (without any colour meaning). Some candidates may mention Stephen Few or Edward Tufte and this normally is a big plus.

Question: How have you learnt SSRS (on the job, articles, books, conferences)
Comment: The thing is that most people who read good books have usually an advantage over those who hasn't because they know what they know and they know what they don't know (but they know it exists and is available)…. Blog/Articles vary in quality so best practise articles is a big plus+, conferences can be also a plus.

Question: Do you have certifications
Comment: This is rather disappointing point for me. Qualifications generally are welcome but unfortunately many people simply cheat. Companies run courses and then give questions and answers, or people find them on the internet. I've met people who had certification but knew very little, I've met people very experienced and knowledgeable without certification and people who have done certification for their self-satisfaction and are experienced and knowledgeable.
Important: (My opinion only) Microsoft BI certifications test tool proficiency…. They do not have much to do with best practise which is very disappointing as this is usually what makes a difference between poor quality solution and high quality solution….. Fortunately 2008 preparation books/exam seem to be going in the right direction.
In other words be careful with certifications…. It is easy to get misleading impression if you don't assess the candidate's actual knowledge.

SSRS Development Questions

Question: How do you normally create reports (wizard/manually)?
Comment: If wizard in most cases then you got rather inexperienced person, if manually than it is usually good answer. The best answer is using a template. Generally developers create reports from scratch which is not bad but it is not very efficient either.

Question: What languages have you used to query data for SSRS Reports?
Comment: Most answers will probably be SQL (TSQL). But T-SQL is not the only query language. If someone build reports based on cubes before than they will say MDX. You can also query data from other sources (not recommended) or use data mining expressions (DMX = advanced).

Question: Gives examples where you used parameters?
Comment: Typically you use parameters to filter data in datasets (or data on reports) but you can also use them to restrict values like the well-known make->model->year example. You can also have hidden and internal parameters which get very handy for more advanced stuff.

Question: What types of graphs do you normally use and what effects do you apply to them?
Comment: Good graph are bar, line, scatter, bullet graphs. Bad graphs are pie charts, area graphs, gauges (apart from bullet graph which classified as gauge in SSRS). Effects should be limited to minimum. Developers should avoid 3D effects, "glass" effect, shadows etc

SSRS Advanced Questions

Question: Have you used custom assemblies in SSRS? If Yes give an example
Comment: This allows to re-use code in reports and it is not very common. Re-usability is good but building dependancies is not so good… so one small mistake might break all reports using it; so it should be used with care.

Question: Can you update report data using SSRS?
Comment: This is not often used (and probably shouldn't be used in most cases) but it is possible.

Question: What is the formatting code (format property) for a number with 2 decimal places?
Comment: N2. Attention to details and good memory is always welcome.

Mandatory question (task)

The last question (task) is one of the most important ones. Ask the candidate to do the job using the software! It doesn't have to be anything special. You can ask them just to create a simple report, graph, chart or use one of the questions and ask the person to explain it using SSRS. It is usually very easy to spot how familiar is the person with the software and options/properties.

SSRS Books I read and I can recommend

I have read several books related to SSRS and I can recommend those two:

For beginners:

Microsoft SQL Server 2008 Reporting Services Step by Step

For those who want to learn more:

Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports (Wrox Programmer to Programmer)

PDF Download - ebook

I will make this page avialable in PDF format for download (ebook) soon.

I will try to find time to add more questions soon. Feel free to suggest new questions (add comments).

Hope that helps!

1. What are all different types of reports that you have created?

Tabular reports
Matrix reports
Sub reports
Charts
Drill down reports
Go to reports

How to create variables in SSRS

It is not required to declare variables in SSRS. When we execute the query which are having parameters, parameters will be created automatically in ssrs.

3. What is the difference between table and matrix report items/data regions

By using table we can create row grouping and by using matrix we can create row grouping and column grouping as well. But in 2008, with Tablix property, we can do row and column groupings either with table or matrix.

What are the new features in 2008

Tablix
Coumn grouping with list
Gauge charts
MS word rendering option
Enhancement to excel cell merging issue

What is a tablix

Combination of table+matrix+list, to perform column grouping and row grouping

What are cascading parameters or parameter dependency

Based on first parameter selection second parameter values will be displayed.

How to restrict the 10 rows per page

By adding Ceiling(Rownumber(nothing)/10) in group by clause

How do you implement drill down functionality

Lets assume we have a text box and table report item values and we want to display table report item values when we click on + symbol on text box. Then in the table properties, we have toggle item to be selected option under visibility tab. below the toggle item drop down box select the text box name and select hide property.

What is a rectangle report item?

Rectangle report item is just a container. We can keep different report items inside a list to keep all the items under one group.

What is a subreport?

Subreport displays the embed report out put in the main report itself. We can pass parameters from main report to sub report if required.

What are different rendering options available in ssrs

Excel
PDF
WS Word
Web archive
XML

What is jump to report?

Jump to report is a hyper link to another report. If we click on main report, control will be moved to second (another) report. If required we can send parameters across the reports.

What are different types of roles in ssrs

Browser: for testing team, just to execute the reports
Content manager: Admin role
My Reports
Linked reports
Report Builder

Types of caching reports

Cached reports (Data is saved in the report server database)
Snap shot reports (report layout will be saved in the report manager)

types of delivery

File system – Report output will be saved in the system folder)
Email

i. Static: Email address will be static

ii. Data driven: Email address will be accessed from table ( it will work on only in Enterprise editions)

Difference between report manager and report server?

We can upload reports, implement security and improve the report execution performance in report manager.
Report server can be used to integrate the report manager in any third party application.

Types of reports uploading?

Open report manager and click on Upload file
In the report designer, right click on properties and give the report server URL.

Types of data sources?

Embedded data source
Shared data source

What is report builder?

We can create ad hoc reports with report builder tool. We will create data model and send upload to report manager. End users will access these data model to create their ad hoc reports.

3. What are data regions?
Data regions are report items that display repeated rows of summarized information from datasets.

8. You want to use a perspective in an MDX query. How do you select the perspective?
Use the Cube Selector in the MDX Query Designer to select a perspective.

15. How do you configure a running aggregate in SSRS?
You can use the RunningValue function to configure a running aggregate.

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.

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.

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.

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.

37. What is the file name extension for an encryption key backup?
Encryption key backups have an .snk file name extension.

Building Reports for the AdventureWorks Intranet

Case scenario

You have just successfully installed SSRS 2008. You have also created two shared data sources: one configured to retrieve data from the AdventureWorks relational database and the other to retrieve information from a sales and marketing data mart stored in an OLAP database. The data mart is populated once a week. The schemas of the relational and OLAP databases are the same as the sample databases provided by SQL Server 2008. You are the main developer of a set of reports that will be used in the AdventureWorks portal, and you need to handle the following requirements:

1. Your end users want a report that gives them near real-time information about sales by Product Category, Product Subcategory, and Product model. The report should show only the past week's sales and should have only three columns: Name, Quantity, and Amount. Users also want the ability to drill through the report from summary information to greater detail. You do not want to use the Report Wizard. Given these requirements, what is the best way to create the report?

2. Your end users want a pivot table report that has Categories, Subcategories, and Models as columns and Year, Quarter, and Month as rows. The cell data should be filled with sales amount information. The information does not need to be real time. Given these requirements, what is the best way to create the report?

Answers

1. You can add a new report to the SSRS solution to satisfy this user requirement. Create a dataset that uses the AdventureWorks relational database. In the dataset's query, filter the information to retrieve only the last weekâ€™s sales by product category, subcategory, and model. Use a Table data region and create two additional groupings, one by category and another by subcategory. Set the initial visibility status of the Subcategory and Detail rows to hidden, the toggle property of the Subcategory grouping to change based on Category, and the toggle property of the Detail grouping to change based on Subcategory.

3. For this requirement, you can use the Report Wizard to create the report. On the Data Source page, select the multidimensional database, and then use the MDX Query Builder to create the MDX query. In the Data pane, drag the Product Model Categories hierarchy, the Date.Calendar hierarchy, and the SalesAmount measure onto the Results pane. Remove the Calendar Semester and Calendar data levels. Select a matrix report, and then assign the date-related information to columns and the product category information to the rows. Last, assign the amount as detail information.

Creating a Reporting Services Infrastructure

Case scenario

You just successfully installed SSRS 2008. You also created two shared data sources: one configured to retrieve data from the AdventureWorks relational database and the other configured to retrieve information from a marketing/sales data mart stored in an online analytical processing (OLAP) database. The data mart is populated once a week. The schemas of the relational and OLAP databases are the same as the sample databases provided by SQL Server 2008. You will be the main developer of a set of reports that will be used in the Adventure-Works portal, and you need to address the following requirements:

1. End users want the ability to create their own reports. The users are knowledge workers who have Excel expertise but no database experience. What is the best way to create the reports, without giving end users direct access to the database? How will you build the infrastructure?

2. In the previous version of the reports, users had a set of reports that were identical to each other except that each report grouped information at different levels of the organization. Users still want the flexibility to look at the information grouped in different ways, but you want to build a single report rather than multiple reports. Given these requirements, what is the best way to create the new report?

Answers

1. You can configure report models that will let users create their own reports. You need to create two separate models, one for the relational engine and another for the SSAS database. The relational database model should be created and configured in BIDS, and the OLAP data source should be created from SSMS or Report Manager.

2. You could create a parameterized report that prompts the user for the desired level of aggregation and then dynamically creates the group by statement. Alternatively, you could use the same query for all the aggregation levels, use a table or matrix report item, and hide the grouping level based on the user selection. The main advantage of the first approach is that it pulls only the required data when the report is executed. The main advantage of the second option is that it allows the reuse of the cache if the report uses report caching.

Managing the Report Environment for Adventure Works

Case scenario

Reporting Services has been deployed in Adventure Works, and multiple departments are requesting access to the new server. However, some employees need to access reports from departments other than their own. At the same time, some users from the call center require the flexibility to create reports based on their customers. Adventure Works uses a mixed database environment, with most applications running on SQL Server 2008 but some running on Oracle.

1. What is the general security infrastructure required to support the reporting needs of the Adventure Works departments and employees?

2. From a data source security standpoint, how can you satisfy the requirements to access both Oracle and SQL Server from Reporting Services?

Answers

1. In general, because item-level roles are cumulative, for each departmentâ€™s folder, you can assign the Browser role to the Active Directory group that represents the employees of each division. Additionally, you can add the Browser role to each person who needs access to more than one departmentâ€™s folder.

2. To allow access to the Oracle databases, you will need to define a user with low-level privileges and store the credentials for that user in Report Server. For the SQL Server databases, given the fact that Adventure Works uses a Windows domain, you can enable Kerberos and use the Integrated Windows Authentication mechanism

CHAPTER 13 Configuring and Administering the SSRS Server

chapter summary

A Reporting Services installation includes the report server catalog, which containsall the metadata necessary to run reporting services, including the encrypted reportdefinitions, encrypted data sources, and site and report security settings.
The Reporting Services Configuration Manager tool helps in the setup of an SSRS instance after installation, including setup of virtual directories, service accounts, Report Server database access, and encryption keys.
The RSConfig.exe command-line utility provides SSRS connection administration to the Report Server databases, ReportServer and ReportServerTempDB.
The RSKeyMgmt.exe command-line utility helps manage encryption keys for backup, restore, scale-out instance management, and deletion of encrypted content.
The RS.exe command-line utility provides a way to run SSRS script files at a command line.
The SSRS encryption keys need to be backed up to an .snk file and stored securely to allow for a restore at a later point in the case of a sever failure or a reconfiguration of hardware.
SSRS can be implemented in a scale-out architecture by sharing the report server catalog between multiple instances of the report server. The Reporting Services Configuration Manager tool can synchronize the encryption keys between the servers in order to access the shared catalog, or you can use the RSKeyMgmt.exe and RSConfig.exe command-line utilities to set up the scale-out environment.
Reporting Services uses several configuration files for advanced settings and management. The main file is the RSReportServer.config file, which contains service settings, Report Manager settings, rendering device properties, delivery method settings, and so on.

Lesson 1: Installing and Confi guring Initial Instances of SSRS

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

2. What is the fi le name extension for an encryption key backup?

Encryption key backups have an .snk fi le name extension.

Lesson 2: Configuring Advanced SSRS Settings and Scale-Out Deployment

1. What are the three SSRS command-line utilities and their primary functions?

RSConfig.exe is used to defi ne 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.

2. 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.confi g fi le and locating the <Extension Name="RenderDevice"/> tag for the specifi c device.

Scaling out your SSRS Servers

Case scenario

As you begin the process of setting up a SQL Server 2008 BI environment, you are planning out your hardware architecture. Your solution will consist of an SSRS installation that will access online analytical processing (OLAP) data in SSAS built from a SQL Server data mart. Your manager has approved two blade servers for SSRS so that the service can be configured for scale-out deploymentâ€”that will help to balance the workload and will provide some load balancing. Additionally, your system administrator has set up Network Load Balancing (NLB) to distribute the report requests. How will you handle the following two setup requirements?

1. Your SSRS implementation requires that you set up a scale-out deployment of SSRS on the two allocated servers. You have already installed the first SSRS instance on one of the servers that points to a Report Server database on your database server. You are now ready to install an instance of SSRS on the second computer. How do you use command-line utilities to install and configure this instance to be part of the SSRS scale-out deployment?

2. To prevent a single point of failure for SSRS, you also need to handle failures at the report server catalog database level. In the case of a server hardware failure, you do not want the SSRS implementation to also fail. Which is the best high-availability technology to use for this situation and why?

Answers

1. Because the first SSRS server has been installed and configured, your next task is to install an unconfigured instance of SSRS, without having the installation use the default configuration, on the second server. You will then need to configure the virtual directories through Reporting Services Configuration Manager and set the service accounts. Before joining the new instance to the scale-out deployment, connect the SSRS instance to the same Report Server database by using RSConfig.exe with the /c parameter. At this point, you should run the RSKeyMgmt.exe tool with the /j command parameter to join an existing SSRS. You need to run this command statement on the server that is already configured and then reference the new instance that will join the existing scale-out deployment.

2. High availability at the database level can be handled by clustering, database mirroring, replication, or log shipping. However, the best choice is to use clustering. Clustering will prevent a server hardware failure from affecting the SSRS implementation, as the Database Engine will fail over to another available node in the cluster. In addition, this will be seamless to Reporting Services, as the database connection uses the virtual instance name of the database server, which also moves to the other server. With the other technologies, the SSRS implementation would need the catalog database connection to be repointed to the backup database from the mirroring, log shipping, or replication.

Moving the Report Server Databases to Another Computer

You can move the report server databases that are used in a current installation to a SQL Server Database Engine instance that is on a different computer. Both the reportserver and reportservertempdb databases must be moved or copied together. A Reporting Services installation requires both databases; the reportservertempdb database must be related by name to the primary reportserver database you are moving.

Moving a database does not effect scheduled operations that are currently defined for report server items.

Schedules will be recreated the first time that you restart the Report Server service.

SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.

Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.

Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

SQL Server provides several approaches for moving databases, including backup and restore, attach and detach, and copy. Not all approaches are appropriate for relocating an existing database to a new server instance. The approach that you should use to move the report server database will vary depending on your system availability requirements. The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

Important
The steps provided in this topic are recommended when relocating the report server database is the only change you are making to the existing installation. Migrating an entire Reporting Services installation (that is, moving the database and changing the identity of the Report Server Windows service that uses the database) requires connection reconfiguration and an encryption key reset. For more information about migrating a Reporting Services installation, see Migration (Reporting Services).

Detaching and Attaching the Report Server Databases
--------------------------------------------------------------------------------

If you can take the report server offline, you can detach the databases to move them to the SQL Server instance you want to use. This approach preserves permissions in the databases. If you are using a SQL Server 2008 database, you must move it to another SQL Server 2008 instance. After you move the databases, you must reconfigure the report server connection to the report server database. If you are running a scale-out deployment, you must reconfigure the report server database connection for each report server in the deployment.

Use the following steps to move the databases:

1.Backup the encryption keys for the report server database you want to move. You can use the Reporting Services Configuration tool backup the keys.

2.Stop the Report Server service. You can use the Reporting Services Configuration tool to stop the service.

3.Start SQL Server Management Studio and open a connection to the SQL Server instance that hosts the report server databases.

4.Right-click the report server database, point to Tasks, and click Detach. Repeat this step for the report server temporary database.

5.Copy or move the .mdf and .ldf files to the Data folder of the SQL Server instance you want to use. Because you are moving two databases, make sure that you move or copy all four files.

6.In Management Studio, open a connection to the new SQL Server instance that will host the report server databases.

7.Right-click the Databases node, and then click Attach.

8.Click Add to select the report server database .mdf and .ldf files that you want to attach. Repeat this step for the report server temporary database.

9.After the databases are attached, verify that the RSExecRole is a database role in the report server database and temporary database. RSExecRole must have select, insert, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. For more information, see How to: Create the RSExecRole.

10.Start the Reporting Services Configuration tool and open a connection to the report server.

11.On the Database page, select the new SQL Server instance, and then click Connect.

12.Select the report server database that you just moved, and then click Apply.

13.On the Encryption Keys page, click Restore. Specify the file that contains the backup copy of the keys and the password to unlock the file.

14.Restart the Report Server service.

Backing Up and Restoring the Report Server Databases
--------------------------------------------------------------------------------

If you cannot take the report server offline, you can use backup and restore to relocate the report server databases. You should use the ‘Copy_Only’ option. Beginning in SQL Server 2008, SQL Server Management Studio supports copy-only backups. After you restore the databases, you must configure the report server to use the database on the new server instance. For more information, see the instructions at the end of this topic.

Using BACKUP and COPY_Only to Backup the Report Server Databases
When backing up the databases, set the COPY_OnlyL option and backup type to Full.

Note
Be sure to back up both the ReportServer and ReportServerTempDB databases as well as their associated log files.

For more information on using SQL Server Management Studio to backup a database, see How to: Back Up a Database (SQL Server Management Studio)

Using RESTORE and MOVE to Relocate the Report Server Databases
When restoring the databases, you can Use the RESTORE WITH NORECOVERY argument to perform the initial restore; this keeps the database in a restoring state, giving you time to review log backups to determine which one to restore. You would then repeat the RESTORE operation but use the RESTORE WITH RECOVERY argument.

Note
Be sure to restore both the ReportServer and ReportServerTempDB databases as well as their associated log files.

For more information on using SQL Server Management Studio to restore a database, see How to: Restore a Database Backup (SQL Server Management Studio)

Microsoft Technologies

Pages

Friday, August 21, 2015

SQL Server Reporting services Interview Questions and Answers - Part3

About Me