Sunday, October 31, 2010

SSRS Interview Questions

1.How to configure SSRS default report directory
2.Explain Drill down and drill threw report
3.Use of Switch and IIF functions 

Q.What can SQL Server Reporting Services do?
A-With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.............
Read answer

Q-Explain the architecture of reporting services.?
A-Reporting Services runs as a middle-tier server as part of the existing server architecture.
SQL Server 2000 should be installed for the database server, and Internet Information Services 6.0 as a Web server............

Describe Reporting Life-cycle.
The phases of the Reporting Life Cycle involve:

Report authoring
This stage involves creation of reports that are published using the Report Definition language. RDL is an XML based industry standard for defining reports..............

Q-What are the ways to tune Reporting Services?
Following are a few ways you can tune up Reporting Services:
You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance............

Q.Can you explain Report definition language (RDL) file in reporting services?
Ans - RDL is an XML description of a report in reporting services. So basically the RDL file has the XML grammar which describes a reporting services report.

Q.How can we consume reports in ASP.NET?
Ans -  There are three famous ways of calling reporting services report in ASP.NET:-
. Using URL way to access reports.
. Using reporting web service to pro-grammatically access reports.
. Using report viewer control.

Q. What is Expressions ?
Ans- An expression is a short statement, written in code, which sets the value of a property. An expression can set the value of a property based on just about any data available in the report: parameters, report global values, fields in a dataset, and built-in functions.
let's say we want it to highlight all the customers from a particular State. We could use the expression:
=iif(Fields!State.Value = "CA", "Bold", "Normal")
Q.What is  Common functions ?
 Ans- The last option is Common Functions. Expand the Common Functions node then you find different function like Text,Datetime,math,Aggregate etc,

Q.what is Global variables ?
Ans- In expression editor click on Globals in the first column of the expression editor.
In the second column, you will see the list of the global variables that are provided to the report.
we are going to use the global variables to create a page-numbering scheme for our report
add a footer to the FirstReportMan.rdl report. You enable this by going to the Layout tab and selecting the Report Footer option from the Report main menu. Now that you have a footer, drag four text boxes into the footer section.
Some other globals worth noting are:
  • ExecutionTime – this tells you how long it took to run the report
  • ReportServerUrl – this allows you to verify that you are getting your report from the correct source
  • UserID - this shows under which User ID the report was run 
Q. What is Custom functions?
Ans - There are about 100 common functions in SSRS 2005
 nd they can handle most of what you will need to do. Occasionally, however, you will still need to set an expression with more control than the common functions can manage. So, to make SSRS even more flexible, you can write custom VB.NET or C# functions and use them in expressions.
Now we are going to add a custom function to the FirstReportMan.rdl report.
Open the report and navigate to the Layout tab. From the Report menu select Report Properties and then jump to the Code tab. We are going to write a custom function that returns a different color, depending on the value that is passed to it. We will then use that function to set the background color for the status field in the report detail table.
Copy the following code into the code window:
Public Function GetColor(ByVal status as String) as String
IF status = "100" Then
Return "White"
End IF
IF status = "101" Then
Return "Yellow"
End IF
IF status = "102" Then
Return "Tomato"
End IF
End Function
Click OK and close the window.
 Now that we have a function that returns color names we need to wire up that function to an expression. Click on the Customer Status cell and open the Properties window. Find the Background Color property and choose Expression from the dropdown. Add the following line of code to create an expression:
When the report runs and this expression is resolved, SSRS will call your GetColor function and pass the value of the customer status for that instance of the row to the function.
Q. What is Sub report ?
Interview Questions
 What is the difference between drill down and drill through?
Drill down means navigating the summary level to detail level in the same report. 
where as drill through is connected to different reports.
Drill DOWN means a way to filter the results to a more 
detailed level from the Parent Level.Drill UP on the other hand means to go up to a Higher (Parent) Level from a Lower (Child) level.
There is a another term for this : SLICING & DICING (Multi Dimesion Analysis)
Drill Through on the other hand means to drill into (get into ) another report for getting more details. The values 
from the Parent(Main) report are passed to the Child (Detailed Report) for filter conditions. 
Drill down means to navigate one level or summary level to next detailed level.And also the next detailed level is 
shown inthe same report. But drill through navigate from one report to another report. Here we can use some 
parameters and other things are needed. Drill down is applicable for only dims data. 
Actually I found 4 different types of drilling mode.

Drill Down  - When you move from summary level to detail 
level in the same report and in the same hierarchy. 

Drill Up - When you move back from detail level to summary 
level in the same report and in the same hierarchy.

Drill Across - When you need to move from one hierarchy to 

Drill Through  - When you need to get additional detail 
data from the Database or from any other report.