Create a report server project
1) In Visual Studio, open the File menu, select New > Project.
in the center column of the New Project dialog box.Create a new report definition file
1. In Solution Explorer, right-click Reports, point to Add, and click New Item. (If the Solution Explorer window is not visible, from the View menu, click Solution Explorer).
2. In the Add New Item dialog box, under Templates, click Report.
3. In Name, type Sales Orders.rdl and then click Add. Report Designer opens and displays the new .rdl file in Design view.
NOTE: Report Designer is a Reporting Services component that runs in Business Intelligence Development Studio. It has two views: Design and Preview. Click each tab to change views.
Set up a connection
1. In the Report Data pane, click New and then click Data Source. If the Report Data pane is not visible,from the View menu, click Report Data.
2. In Name, type AdventureWorks.
3. Make sure Embedded connection is selected.
4. In Type, select Microsoft SQL Server.
5. In Connection string, type the following:
Data source=localhost; initial catalog=AdventureWorks
6. Click OK. A data source called AdventureWorks is added to the Report Data pane.
Define a Transact-SQL query for report data
1. In the Report Data pane, click New, and then click Dataset. The Dataset Properties dialog box opens.
2. In the Name box, type AdventureWorksDataset.
3. Click the Use a dataset embedded in my report radio button. Make sure the name of your data source, AdventureWorks, is in the Data source text box, and that the Query type is Text.
4. Type, or copy and paste, the following Transact-SQL query into the Query box.
SELECT
soh.OrderDate AS [Date],
soh.SalesOrderNumber AS [Order],
pps.Name AS Subcat, pp.Name as Product,
SUM(sd.OrderQty) AS Qty,
SUM(sd.LineTotal) AS LineTotal
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader AS soh
ON sp.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesOrderDetail AS sd
ON sd.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product AS pp
ON sd.ProductID = pp.ProductID
INNER JOIN Production.ProductSubcategory AS pps
ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS ppc
ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, pps.Name, pp.Name,
soh.SalesPersonID
HAVING ppc.Name = 'Clothing'
5. Click the Query Designer button. The query is displayed in the text-based query designer. You can toggle to the graphical query designer by clicking Edit As Text. View the results of the query by clicking the Run (!) button on the query designer toolbar.You see the data from six fields from four different tables in the AdventureWorks database.
6. Click OK to exit the query designer. Click OK to exit the Dataset Properties dialog box. Your AdventureWorksDataset dataset fields appear in the Report Data pane.
Add a Table data region and fields to a report layout
1. In the Toolbox, click Table, and then click on the design surface. Report Designer draws a table data region with three columns in the center of the design surface. The Toolbox may appear as a tab on the left side of the Report Data pane. To open the Toolbox, move the pointer over the Toolbox tab. If the Toolbox is not visible, from the View menu, click Toolbox.
2. In the Report Data pane, expand the AdventureWorksDataset dataset to display the fields.
3. Drag the Date field from the Report Data pane to the first column in the table.
When you drop the field into the first column, two things happen. First, the data cell will display the field name, known as the field expression, in brackets: [Date]. Second, a column header value is automatically added to Header row, just above the field expression. By default, the column is the name of the field. You can select the Header row text and type a new name.
Alternately click move your mouse cursor to the first cell on table, a small symbol for field list appears click on it and select the desired field.
4. Drag the Order field from the Report Data pane to the second column in the table.
5. Drag the Product field from the Report Data pane to the third column in the table.
NOTE: To add a new column in table right click the last column and select insert column -> right
6. Drag the Qty field to the right edge of the third column until you get a vertical cursor and the mouse pointer includes a plus sign [+]. When you release the mouse button, a fourth column is created for [Qty].
7. Add the LineTotal field in the same way, creating a fifth column.
8. The following diagram shows a table data region that has been populated with these fields: Date,
Order, Product, Qty, and Line Total.
