SQL Server Interview Questions on Reporting Services

We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on Reporting Services

Note  I know everyone screaming this is a part of Data mining and warehousing. I echo the same voice 
          with you my readers, but not necessarily. When you want to derive reports on OLTP systems this is the 
          best day to get your work done. Secondly reporting services is used so much heavily in projects now a 
          day that it will be completely unfair to discuss this topic in a short way as a subsection of some chapter.

Question 1.
Can you explain how can we make a simple report in reporting services?
Answer:
We will be using the “AdventureWorks” database for this sample. We would like to derive a report on how much quantity sales were done per product. For this sample, we will have to refer to three tables Salesorderdetails, Salesorderheader, and product table. Below is the SQL which also shows what the relationship between those tables is:-

select production .product.Name as ProductName, count (*) as TotalSales from sales. sales order detail
inner join Sales.Salesorderheader 
on Sales.Salesorderheader.salesorderid=
Sales.Salesorderdetail.Salesorderid 
inner join production. product
on production. product,productid=sales.sales order detail .productid
group by production. product.Name

So we will be using the above SQL and trying to derive the report using reporting services. First, click on the business intelligence studio menu in SQL Server 2005 and say File –> New — > Project. Select the “Report” project wizard. Let’s give this project name “TotalSalesBy Product”. You will be popped with a startup wizard as shown below.

Click next and you will be prompted to input data source details like type of server, connection string, and name of the data source. If you have the connection string just paste it on the text area or else click edit to specify connection string values through GUI.

As we are going to use SQL Server for this sample specify OLEDB provider for SQL Server and click next.

After selecting the provider specify the connection details which will build your connection string. You will need to specify the following details Server Name, Database Name, and security details.

This is the most important step of reporting services, specifying SQL. You remember the top SQL we had specified the same we are pasting it here. If you are not sure about the query you can use the query builder to build your query.

Now it’s time to include the fields in reports. At this moment we have only two fields name of the product and total sales.

Finally, you can preview your report. In the final section, there are three tabs data, layout, and preview. In the data tab, you see your SQL or the data source. In the layout tab, you can design your report most look and feel aspect is done in this section. Finally below is the preview where you can see your results.

Question 2.
How do I specify stored procedures in Reporting Services?
Answer:
There are two steps to specify stored procedures in reports of reporting services:-
Specify it in the query string. For instance, I have a stored procedure “GettotalSalesofproductsbySales” which has “@ProductSold” as the input parameter.

You have to also specify the command type from the data tab.

Question 3.
What is the architecture for “Reporting Services “?
Answer:
“Reporting Services” is not a stand-alone system but rather a group of server sub-system which work together for the creation, management, and deployment of reports across the enterprise.

Report designer

This is an interactive GUI that will help you to design and test your reports.

Reporting Service Database

After the report is designed they are stored in XML format. These formats are in RDL (Report Design Layout) formats. These entire RDL formats are stored in Report Service Database.

Report Server

Report Server is nothing but an ASP.NET application running on IIS Server. Report Server renders and stores these RDL formats.

Report Manager

It’s again an ASP.NET web-based application that can be used by administrators to control security and managing reports. From an administrative perspective who has the authority to create the report, run the report etc…
You can also see the various formats which can be generated XML, HTML, etc using the report server.