Creating a Basic Table Report (SSRS)

This post will help you create a basic table report based on the AdventureWorks2008R2 database using Report Designer. You can also use Report Builder or the Report Wizard to create reports. In this post, you will create a report project, set up connection information, define a query, add a Table data region, group and total some fields, and preview the report.

Create a report server project

  1. Click Start, point to Programs, point to Microsoft SQL Server 2008 R2, and then click Business Intelligence Development Studio.

  2. On the File menu, point to New, and then click Project.

  3. In the Project Types list, click Business Intelligence Projects.

  4. In the Templates list, click Report Server Project.

  5. In Name, type Tutorial.

  6. Click OK to create the project.

    The Tutorial project is displayed in Solution Explorer.

Create a new report definition file

  1. In Solution Explorer, right-click Reports, point to Add, and click New Item.

  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.

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 AdventureWorks2008R2.

  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=AdventureWorks2008R2

    This connection string assumes that Business Intelligence Development Studio, the report server, and the AdventureWorks2008R2 database are all installed on the local computer and that you have permission to log on to the AdventureWorks2008R2 database.

    If you are using SQL Server Express with Advanced Services or a named instance, the connection string must include instance information:

    Data source=localhost\SQLEXPRESS; initial catalog=AdventureWorks2008R2



  6. Click OK. A data source called AdventureWorks2008R2 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.BusinessEntityID = 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. (Optional) 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 AdventureWorks2008R2 database. The query makes use of Transact-SQL functionality such as aliases. For example, the SalesOrderHeader table is called soh.

    Click OK to exit the query designer.



  6. Click OK to exit the Dataset Properties dialog box.

    Your AdventureWorksDataset dataset fields appear in the Report Data pane.


Adding a Table to the Report (Reporting Services)


To 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.

    NoteNote

    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.



  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.



  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.

    The column header is Line Total. Report Designer automatically creates a friendly name for the column by splitting LineTotal into two words.

    The following diagram shows a table data region that has been populated with these fields: Date, Order, Product, Qty, and Line Total.

    Design, Table with header row and detail row


Preview Your Report


Previewing a report enables you to easily view the rendered report without having to first publish it to a report server. You will probably want to preview your report frequently during design time.

To Preview a report



  • Click the Preview tab. Report Designer runs the report and displays it in Preview view.

    The following diagram shows part of the report in Preview view.

    Preview, Detail rows of table with 5 columns

    Notice that the currency (in the Line Total column) has six places after the decimal, and the date has an unnecessary time stamp. You're going to fix that formatting in the next lesson.


Now that you've added a table and some fields to the Sales Orders report, you can format the date and currency fields and the column headers.

Format the Date


The Date field displays date and time information by default. You can format it to display only the date.

Format a date field



  1. Click the Design tab.



  2. Right-click the cell with the [Date] field expression and then click Text BoxProperties.



  3. Click Number, and then in the Category field, select Date.



  4. In the Type box, select January 31, 2000.



  5. Click OK.


Format the Currency


The LineTotal field displays a general number. Format it to display the number as currency.

To format a currency field



  1. Right-click the cell with the [LineTotal] field expression and then click Text BoxProperties.



  2. Click Number, and in the Category field, select Currency.



  3. If your regional setting is English (United States), the defaults should be:



    • Decimal places: 2



    • Negative numbers: ($12345.00)



    • Symbol: $ English (United States)



  4. Select Use 1000 separator (,).

    If the sample text is: $12,345.00, then your settings are correct.



  5. Click OK.


Change Text Style and Column Widths


You can also change the formatting of the header row to differentiate it from the rows of data in the report. Lastly, you will adjust the widths of the columns.

To format header rows and table columns



  1. Click the table so that column and row handles appear above and next to the table.

    Design, Table with header row and detail row

    The gray bars along the top and side of the table are the column and row handles.



  2. Point to the line between column handles so that the cursor changes into a double-headed arrow. Drag the columns to the size you want.



  3. Select the row containing column header labels and from the Format menu, point to Font and then click Bold.



  4. To preview your report, click the Preview tab. It should look something like this:

    Preview of table with bold column headers


Grouping Data in A report

To group data in a report



  1. Click the Design tab.



  2. From the Report Data pane, drag the Date field to the Row Groups pane. Place it above the row called Details.

    Note that the row handle now has a bracket in it, to show a group. The table now also has two Date columns -- one on either side of a vertical dotted line.

    Date field added to Row Groups pane



  3. From the Report Data pane, drag the Order field to the Row Groups pane. Place it below Date and above Details.

    Note that the row handle now has two brackets in it, to show two groups. The table now has two Order columns, too.



  4. Delete the original Date and Order columns to the right of the double line. This removes this individual record values so that only the group value is displayed. Select the column handles for the two columns, right-click and click Delete Columns.

    Select columns to delete

    You can format the column headers and date again.



  5. Switch to the Preview tab to preview the report. It should look similar to the following illustration:

    Table grouped by date and then order


To add totals to a report



  1. Switch to Design view.



  2. Right-click the data region cell that contains the field [LineTotal], and click Add Total.

    This adds a row with a sum of the dollar amount for each order.



  3. Right-click the cell that contains the field [Qty], and click Add Total.

    This adds a sum of the quantity for each order to the totals row.



  4. In the empty cell to the left of Sum[Qty], type the label "Order Total".



  5. You can add a background color to the totals row. Select the two sum cells and the label cell.



  6. On the Format menu, click Background Color and click Light Gray.

    Design view: Basic table with order total


To add a daily total to a report



  1. Right-click the Order cell, point to Add Total, and click After.

    This adds a new row containing sums of the quantity and dollar amount for each day, and the label "Total" in the Order column.



  2. Type the word Daily before the word Total in the same cell, so it reads Daily Total.



  3. Select the Daily Total cell, the two Sum cells and the empty cell between them.



  4. On the Format menu, click Background Color and click Orange.

    Design view: Daily total row in basic table


To add a grand total to a report



  1. Right-click the Date cell, point to Add Total, and click After.

    This adds a new row containing sums of the quantity and dollar amount for the entire report, and the Total label in the Date column.



  2. Type the word Grand before the word Total in the same cell, so it reads Grand Total.



  3. Select the Grand Total cell, the two Sum cells and the empty cells between them.



  4. On the Format menu, click Background Color and click Light Blue.

    Design view: Grand total in basic table



  5. Click Preview.

    The last page should look something like this, although your Order Total, Daily Total, and Grand Total may be on a second page:

    Preview: Basic table with grand total

Posted by
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)