22 October, 2011

I am going to start 1st class on ssrs

My dear friends just follow my instructions.




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,

No comments:

Post a Comment