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:

 -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  

(Work in progress, more to follow)

Might want to read SSIS Interview quesitons

Check out Whats happening on the Report Server

Sometime it may happen that Report server may work slow and you  might want to know whats really happening in the server and which reports are in use to get a lead.


1. To know which reports are running on you system is to check ExecutionLog table in ReportServer database of your server.

SELECT * FROM ReportServer.dbo.ExecutionLog

This table contains the history of which report is executed at which time, taking how much time and by whom. This can lead to the report which is taking most time.

But the draw back of this table is that it does not get information about subscription reports. And when I look into any Report Server first thing I look for is Reports Subscriptions. Those are little hidden ones and take work in background with letting anyone who that how much resource and time they are consuming.

2. Next method of knowing whats happening in our Report Server is through Manage Jobs page on ReportServers SiteSettings

This displays and lets user to cancel any In-Progress Report or Subscription. Only limition is that it will display only in-progress reports/subscription which are in-progress for atleast 60 seconds.

Select ALL in parameter of SSRS report

Select ALL as parameter value is one of the most common functionality which most of the reports have and there are number of ways to implement it.

I will explain below the way I do it and I find easiest. The driver of the solution is CASE option under WHERE clause of SELECT DataSet Query.

        SELECT * FROM TableName
         WHERE
         (
               CASE
                      WHEN  @RepParam <> 'ALL' AND ColName= @RepParam THEN 1
                      WHEN  @RepParam= 'ALL'  THEN 1
               END
         ) = 1 ;

Lets see it through an simple Student Table example where we will Select student either on the basis of the Grade they are in or select all of them.

1. Records in Student table are

2. Create a simple report with DataSet Student as SELECT * FROM Student;

3. To add the Grade parameter and option for Select ALL

3.a. Create a DataSet for available Grades for Report parameter

3.b. Configure a Report parameter "Grade" as

3.c. Modify query for Student Dataset  to allow filtering on Grades as

4.a. Run the Report for ALL grades

4.b. Run the Report for grade - X

Cheers!!

Drop-Down/ComboBox Sorting in SSRS

For Dynamic Sorting SSRS has Interactive Sorting  which enables a sorting button on the Tablix Column and user can sort on desired Column but many time user wants to have Drop-Down or Combo Box listing columns and wish to chose the sorting column from there.. Something like

Okay , this is not built-in feature of SSRS but we can certainly work around and provide that feature... I will take a simple example to demonstrate that: I will create a Simple Student table and provide drop down sorting on the columns in the table.

So Lets get started 

1. Create a Student table

2. Create a SSRS project and create a Shared DataSource linking our Database

3. Create a simple report having columns from Student table

4. Report would look like.

5a. Now lets start our work and create a parameter SortBy

5b. Specify Available values as Column name from the Student table and assign values to them

6. Go to Tablix Properties> Sorting

7.Add a sorting Option and in Column tab give expression as

=IIf(Parameters!SortBy.Value=1,Fields!StudentID.Value
,IIf(Parameters!SortBy.Value=2,Fields!Name.Value
,Fields!Marks.Value))

8. Run the report and chose the SortBy column from Drop-Down

a. By Name

b.By Marks

Cheers-Have fun!!

Rahul Sherawat