SQL SERVER REPORTING SERVICES-2008
SSRS Architecture
Reporting Services architecture includes development tools, administration tools, and report viewers. There are a number of ways to get to Reporting Services programmatically, including URL, SOAP and WMI interfaces.
Report Server: - Report Server is the core engine that drives Reporting Services. The URL for Report server is, http://servername/reportserver
Report Manager: - Report Manager is a Web-based administrative interface for Reporting Services. The URL for Report server is, http://servername/reports
Note:- The content of Report Server is same as Report Manager
Report Designer: - Report Designer is a developer tool for building complex reports. Report Designer can create reports of any complexity that Reporting Services supports, but requires you to understand the structure of your data and to be able to navigate the Visual Studio user interface (Business Intelligence Development Studio).
Report Builder:- Report Builder provides a simpler user interface for creating ad hoc reports, directed primarily at business users rather than developers. Report Builder requires a developer or administrator to set up a data model (.smdl) before end users can create reports.
Designing a Report from Report Designer:
Step to Create a new Report:
1. Open Business Intelligence Development Studio (BIDS)
2. Select File à New à Project.
3. Select the Business Intelligence Projects project type.
4. Select the Report Server Project template.
5. Name the new report MorningBatch and pick a convenient location to save it in.
6. Right-click on the Reports node in Solution Explorer (Ctrl+Alt+l) and select Add à New Item.
7. Select the Report template.
8. Name the new report Tabular.rdl and click Add.
9. In the Report Data (Ctrl+Alt+d) window, select New à Data Source.
10. In Data Source Properties Editor,
a. NameàDSrcProductDetails
b. TypeàSelect Microsoft SQL Server (default)
c. Click Edit
d. In Connection Properties editor, Provide,
i. Server Name à localhost or . or servername
ii. Connect to Database à Select AdventureWorks database from dropdownlist
iii. Click Ok
11. Click Ok
12. In the Report Data window, select DSrcProductInformation à Right and select Add Dataset.
13. In Dataset Properties Editor, Provide the following information,
a. Name à dsProducts
b. DataSource àSelect DSrcProductInformation
c. Query Type à Select or Check Text Radio Button to the dataset from SQL Script
d. Query à Click Query Designer to build the query or build the query on SQL Server Management Studio,
e. In Query Designer , Click Edit As Text
f. Click Add Tables to add tables to query list
g. Production.Product, Production.ProductSubcategory and Production.ProductCategory tables
h. Select the following columns or fields from the above mentioned tables,
Production.ProductCategory.ProductCategoryID, Production.ProductCategory.Name AS CategoryName, Production.ProductSubcategory.ProductSubcategoryID,
Production.ProductSubcategory.Name AS SubcategoryName, Production.Product.ProductID, Production.Product.Name, Production.Product.Color,
Production.Product.ReorderPoint, Production.Product.StandardCost, Production.Product.ListPrice, Production.Product.Size, Production.Product.Weight,
Production.Product.Class, Production.Product.Style, Production.Product.SellStartDate
i. Click Ok
j. Finally, the dataset (dsProducts) query is generated as mentioned below,
SELECT Production.ProductCategory.ProductCategoryID, Production.ProductCategory.Name AS CategoryName, Production.ProductSubcategory.ProductSubcategoryID, Production.ProductSubcategory.Name AS SubcategoryName, Production.Product.ProductID, Production.Product.Name, Production.Product.Color, Production.Product.ReorderPoint, Production.Product.StandardCost, Production.Product.ListPrice, Production.Product.Size, Production.Product.Weight, Production.Product.Class, Production.Product.Style, Production.Product.SellStartDate
FROM Production.Product INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID
14. Click Refresh fields and Click Ok. Finally, the datasource and dataset looks like,