Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Report Builder Report Body



By:

Overview

The Report Body is the section of a report that is between the Report Header and the Report Footer.  It contains the report content.  This topic will walk through the steps for creating the report body.  We'll add a Matrix data region to the report and populate it with the data from the shared dataset that we created in the Getting Started section.

Explanation

We will continue with the report that we began in this section.  Our report has the header section and the footer section completed and looks like this:

To begin click Matrix on the Insert tab of the Report Builder ribbon and click Insert Matrix on the popup menu:

Click on the report designer surface with the left mouse button and drag the mouse to create the rectangular area for the matrix.  You will be prompted to select the dataset to populate the matrix; fill in the dialog as shown:

The default value or name will be something like Dataset1; it's a good idea to provide a meaningful name.  I'm using the shared dataset that we created in the Getting Started section.  It's definitely a best practice to create shared datasets that provide the data that you need for your reports.  This allows you to simply select the one you need rather than having to specify a data source, a query to retrieve the data you need, and so on.

Click OK to complete the Dataset Properties and the report now looks like this:

Expand the Datasets node in the Report Data area to show the dataset and its fields:

Drag fields from the CategorySales dataset and drop them onto the matrix placeholders as follows:

  • ProductCategoryName -> Rows
  • CalendarYear -> Columns
  • SalesAmount -> Data

The report design surface now looks like this:

There are a couple of things to note at this point:

  • The matrix and each of its cells all have a number of properties that you can set; as you click in a cell the Properties window will refresh to show the properties for the item you have selected and allow you to edit them
  • You can right click in a cell to display a popup menu with a bunch of choices; e.g. insert column, insert row, and so on

Click Run on the Home tab to preview the report:

I want to make a couple of improvements to what we have:

  • Add category totals
  • Add yearly totals
  • Format the amounts as currency without decimal places
  • Get rid of the blank space between the report header and the matrix

Right click in the [CalendarYear] cell then select Add Total, After from the popup menu to add the category totals:

Right click in the [Sum(SalesAmount)] cell and select Add Total, Row from the popup menu to add the yearly totals.

To fix the number formats, click in the cell, go to the Properties window, and set the Format property to C0 (it's in the Number group):

To get rid of the blank space at the top of the report, right click on the "Click to add title" TextBox and delete it; click the report header area and drag it up to remove the unused space.

Click Run on the Home tab to preview the report:

At this point we have achieved our objective of building a report from scratch.  You can go back and customize the various properties to spice it up.

Additional Information


Last Update: 6/23/2011




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools