Saturday, July 12, 2014

SSRS - SQL Server Reporting Services Interview Questions / FAQs

What is a dataset and what are the different types of datasets?
A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and shared datasets can be shared across reports.
What are parameterized reports? What are cascading parameters in SSRS reports?
Reports that accept parameters from users to fetch and report data conditionally, are known as parameterized reports. When you have multiple parameters in a report and values of different parameters are dependent and populated dynamically based on the value of parent parameters, it's known as a cascading parameter.
What is Tablix?
A Tablix can be seen as a control with combined capabilities of a table and a matrix, which facilitates asymmetric and flexible, row and column level, static and dynamic groupings

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

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. 

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

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. 

Advance SSRS Interview Questions / FAQs

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
Architecture of SSRS:
Q What are the three stages of Enterprise Reporting Life Cycle ?
a. Authoring
b. Management
c. Access and Delivery
Q 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.
Q 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.
Q Which programming language can be used to code embedded functions in SSRS?
Visual Basic .NET Code.
Q 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

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. What is drill-down and drill-through in SSRS?
Drill-down is a mechanism of decomposing summarized information to a detailed level. Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report

No comments: