Microsoft SQL Server 2008 Business Intelligence Development and Maintenance: Developing SSRS Reports

Lesson 1: Creating SSRS Projects and Reports in BIDS

Estimated lesson time: 40 minutes

SSRS supports several ways of creating reports. You can design reports by using the Report Designer, Report Builder, or third-party tools. You can also import reports from Microsoft Office Access 2002 or later. BIDS includes the Report Wizard as well as the Report Designer, which is the primary developer tool for creating reports.

Report Builder in SSRS 2008 is a completely new tool compared to Report Builder in SSRS 2005. Report Builder 1.0 in SSRS 2005 is a limited tool, available from Report Manager, which is used mainly by business users to create reports based on previously created report models. Report Builder 2.0 in SSRS 2008 is an entirely new, stand-alone authoring tool similar to Microsoft Office–applications that can take advantage of all of the SSRS features. Report Builder 2.0 is not included with SQL Server 2008 release-to-manufacturing (RTM), but you can download it from the Microsoft Download Center (see References). Note, however, that Report Builder 1.0 is still part of the product. In this lesson, you will learn how to use the Report Wizard and the Report Designer in BIDS to create reports.

Understanding the SSRS Report Templates

BIDS is the preferred tool for developers to create SSRS reports. BIDS extends Microsoft Visual Studio 2008 with project types that include three specific templates for SSRS.

• Report Server Project The first SSRS template is the Report Server Project, a Visual Studio template that contains SSRS items, including data sources and reports. The Report Server Project template also contains deployment information. Creating a Report Server project produces a blank project with two folders in the BIDS Solution Explorer window: Shared Data Sources and Reports.

  You can add a new report by right-clicking the Reports folder and then selecting Add New Report to start the Report Wizard. You can also add a report by right-clicking the Reports folder, selecting Add, selecting New Item, and then selecting either the Report Wizard template or the Report template. The Report Wizard template, of course, starts the Report Wizard. And the Report template opens the Report Designer, a collection of BIDS design surfaces and graphical tools that let you develop reports that use the full capabilities of SSRS.

  Each report has a data source, a database that the data for the report comes from. You can create a report-specific data source or a shared data source. A shared data source can be shared among all reports in a project. You can add a new shared data source by right-clicking Shared Data Source and selecting Add New Data Source.

• Report Server Project Wizard The second SSRS template is the Report Server Project Wizard, which is very similar to the Report Server Project template except that it automatically starts the Report Wizard when you create a project based on this template. This is actually the same wizard mentioned in the previous bullet. The Report Wizard is an automated tool that facilitates the creation of tabular and matrix reports. Tabular reports are reports that have a predefined number of columns. Matrix reports use data as the source to define the columns that the report will contain. SSRS 2008 provides both Table and Matrix data regions as well as a List data region, but all three regions are just templates for the powerful new Tablix data region. You will learn more about different data regions later in this chapter. You can also manually launch the Report Wizard by adding a report to the project. After you complete the creation of a report with the Report Wizard, you can enhance your report in the Report Designer.

• Report Model Project The third SSRS template in BIDS is the Report Model Project template. A report model is a metadata description, from a business point of view, of a data source and its relationships. Chapter 11, “Extending and Deploying SSRS Reports,” gives you an overview of the Report Model Project template and report creation.

Using the Report Wizard

In this section, you learn how to use the Report Wizard to create a report. Remember, you can use this wizard by creating a new project using either the Report Server Project template or the Report Server Project Wizard. The Report Wizard walks you through the following steps, which are illustrated in Figure 10-1.

1. On the Welcome page, you can review the basics of the Report Wizard and the steps it will follow.

2. On the Select The Data Source page, you select a previously defined data source or create a new data source. The data source defines the connection type, connection string, and security configuration that SSRS will use to pull data from the database or data provider.

3. On the Design The Query page, you enter a SQL statement to query the database, or you can click Query Builder to design the query that will generate the data for your report.

   BEST PRACTICES: Optimize Your Query

   To avoid any unnecessary burden on the database, make sure that you query only the data your report needs. Avoid the use of the SELECT * statement, and specify only the columns you require.

4. On the Choose The Report Type page, you select the report type that you want the wizard to generate. The Report Wizard supports tabular or matrix reports; however, both table and matrix regions are just templates for the new Tablix data region. You can enhance your Tablix later in the Report Designer. The wizard does not support chart or free-form reports, so if you want a chart or a free-form report, you must use the Report Designer to create the report interface.

5. On the Design The Table/Matrix page, based on the report type you select, the wizard lets you configure the table or matrix component of the report. In either scenario, you configure the general layout of the report. If you are configuring a table report, the wizard lets you configure Page, Group, and Details elements. If you are designing a matrix, the Report Wizard lets you configure Page, Columns, Rows, and Details elements.

6. Report styles define general formatting configurations such as fonts, colors, and borders. On the Choose A Report Style page, you can use one of the predefined templates. Note that you can extend BIDS with your customized templates.

7. If you launch the Report Wizard by creating a project with the Report Server Project Wizard template, on the Choose Deployment Location page, you have the opportunity to configure the SSRS deployment server and deployment folder. This configuration is stored at the project level rather than in the report. If you add a report to an existing project, this step is skipped.

8. On the Completing The Wizard page, the final step of the configuration is to review the defined report configuration and name the report.

Modifying Project Properties

After the report project is created, you might want to change some of the project properties, such as saving the deployment configuration to another folder. To change the properties of the project, right-click the project in Solution Explorer, and then select Properties. Table 10-1 lists the properties shown in the ProjectName Property Pages dialog box.

Table 10-1 Properties in the ProjectName Property Pages Dialog Box

Category	Property	Description
Debug	StartItem	Lets you configure the report that will be automatically displayed when running the project for debugging purposes.
Deployment	OverwriteDataSources	When set to Yes, this property will replace the shared data sources in the deployment server.
Deployment	TargetDataSourceFolder	Folder in which shared data source will be deployed.
Deployment	TargetReportFolder	Folder in which reports will be deployed.
Deployment	TargetServerURL	URL of the deployment server.

Note that by default in BIDS, the TargetServerURL points to http://localhost/ReportServer; however, you should also note that SSRS 2008 in native mode uses port 80 by default except when installed on a computer running the 32-bit Windows XP SP2 operating system, where the default port is 8080. This means that you should take special care to modify this property accordingly. You can find the correct link on the Report Services Configuration Manager Web Service URL page or by right-clicking your target SSRS instance in SSMS Object Explorer, selecting Properties, going to the General page, and looking at the URL box.

Modifying Report-Level Properties

You might also want to change some report properties after the report is created. Often you will want to change the report layout and set width and height properties that are different from the default report margins. Before changing the report, it is important to understand two fundamental concepts of report layout:

• Page layout is configured at the report level.

• Different rendering extensions are paginated differently. Rendering is the SSRS process of generating an image or document from data. You can select rendering in many ways: in Report Manager; in an ASP.NET application for a default report site in native mode, when viewing the report in default HTML format; in an application that uses the MicrosoftReportViewer control; and when you define a subscription to the report. Each document type has a rendering extension.

Table 10-2 summarizes the ways that different extensions are paginated.

Table 10-2 Rendering Extension Pagination

Rendering Extension	Page Type
CSV	None
Excel	Logical page
HTML	Logical page
Image	Physical page
PDF	Physical page
Word	Physical page
XML	None

Physical pages let you precisely control the way the pages will break in the report. Logical pages are calculated at run time based on the number of columns and rows, and you do not have precise control over how the pages break.

The physical page size is the paper size. The paper size that you specify for the report controls how the report is rendered. By default, the page size is 8.5 by 11 inches. Two properties, PageHeight and PageWidth, let you configure the physical page size. Other reports are not affected by these settings. There are several ways to configure these properties. You can select Report in the Properties window, you can select Report Properties from the Report menu, or you can right-click in a blank area of the report in the Report Designer, select Report Properties, and then set the values accordingly.

You can also specify report page size by setting up margins with the TopMargin, BottomMargin, LeftMargin, and RightMargin properties. But if a report item extends into the margin area, it is clipped so that the overlapping area is not displayed. If this happens, you can change the width of the report body, which contains data regions and other report items, by selecting Body in the Properties window. The size of headers and footers also affects pagination.

Just remember that you could create a report to be one page wide, for example, but when you use different rendering formats, it is displayed across multiple pages. To troubleshoot, check that the width of the report body, including margins, is not larger than the physical page size width. You can also enlarge or reduce the container size to prevent empty pages from being added to your report.

Developing Report Objects with the Report Designer

After you have created a report through the Report Wizard, you will often need to add elements or change elements generated by the wizard. You use the Report Designer to modify report item properties and enhance the user report navigation experience.

The most common items that you need to change after the Report Wizard generates a report are fonts, colors, and date and number formatting.

Modifying Fonts

Changing the font of a report item is as simple as selecting the report item and using the Properties window to change the font. Table 10-3 lists the font properties.

Table 10-3 Font Properties

Property	Description
FontFamily	The fonts installed on your computer. If the font you need does not appear in the Properties window, type in the name of the font. If the font does not exist on the machine that is rendering the report, the font automatically defaults to Arial.
FontSize	The size of the font.
FontStyle	Either Normal or Italic.
FontWeight	Configures the boldness of the font, including Normal, Thin, Extra-Light, Light, Medium, Semi-Bold, Bold, Extra Bold, and Heavy.
TextDecoration	Specifies special text formatting, including Underline, Overline, and LineThrough.

Changing Colors

SSRS uses three color properties to format reports: Background Color, Border Color, and Color. These colors use either HTML names or a hexadecimal HTML color string. The standard format for HTML color strings is #RRGGBB—which represents color by the combination of Red, Green, and Blue—with hexadecimal numbers. For example, the color white is #ffffff and black is #000000.

The color properties that you can configure in SSRS are Background Color, Border Color, and Color (which refers to Foreground Color). Fortunately, you do not have to write hexadecimal strings. When you select any of the color properties, you can choose from the Standard Colors Palette, which shows at the bottom the most recently used colors.

If you need special colors, you can write expressions directly by clicking the Expression link in the dropdown list for a color property, or you can click the More Colors link to access the Select Color dialog box. There are three advanced color options: Palette—Standard Colors, which lets you select a color by its name, and Palette—Color Circle and Palette—Color Square, which let you choose a custom color by pointing to and clicking a color in the circle or the square, respectively. You can also adjust brightness. After you select a color, you can check the expression generated to make sure it is the correct color. Not all color options are available for all report items. Table 10-4 summarizes SSRS’s color support for different report items.

Table 10-4 Color Support for Different Report Items

Report Item	Background Color	Border Color	Color
Body	Yes	Yes	No
Chart	Yes	Yes	No
Chart area	Yes	Yes	No
Chart legend	Yes	Yes	Yes
Image	No	Yes	No
Line	No	Yes	No
List (that is, Tablix)	Yes	Yes	Yes
Matrix (that is, Tablix)	Yes	Yes	Yes
Rectangle	Yes	Yes	No
Subreport	No	Yes	No
Subtotal	Yes	Yes	Yes
Table (that is, Tablix)	Yes	Yes	Yes
Text box	Yes	Yes	Yes
Title	Yes	Yes	Yes

To change the color of any report item, select the item in the Report Designer, and then use the Properties window to change the color.

Changing Date and Number Formatting

You will also often need to modify a report’s date and number formatting. SSRS uses either the Microsoft .NET Framework formatting strings or customized formatting strings. Table 10-5 shows the .NET Framework standard formatting strings.

Table 10-5 .NET Framework Formatting Strings

Numbers		Dates
Format String	Name	Format String	Name
C or c	Currency	d	Short date
D or d	Decimal	D	Long date
E or e	Scientific	t	Short time
F or f	Fixed-point	T	Long time
G or g	General	f	Full date/time (short time)
N or n	Number	F	Full date/time (long time)
P or p	Percentage	g	General date/time (short time)
R or r	Round-trip	G	General date/time (long time)
X or x	Hexadecimal	M or m	Month day
		O or o	Round-trip
		R or r	RFC1123 pattern
		S	Sortable date/time
		U	Universal sortable date/time
		U	Universal full date/time
		Y or y	Year month

You can also extend the standard .NET format strings to use your own customized versions of the strings. For example, you can use the string #,###.0 to specify a number or yyyymmdd to configure a date format.

Adding Report Objects to a Report

In addition to changing the properties of objects, sometimes you will want to add other objects to the report, such as the following:

• A header or footer

• A text box

• An image

• A chart

• A gauge

• A Tablix

• Other graphical items

All of these report items except headers and footers are included in the Toolbox window. The Tablix report item, for example, is available through Table, Matrix, or List elements, which you can configure by displaying the Toolbox window and dragging the component onto the design surface. Because headers and footers are not in the Toolbox window, you must configure them at the report level.

Adding a Page Header or Footer

Reports can contain page headers, which are information included at the top of each page, as well as page footers, which are information included at the bottom of each page. Headers and footers are frequently used to display general report identification information such as title, page numbers, date and time of the report execution, a company logo, and user name. You cannot add data-bound fields or images directly to the page header or footer, but you can write expressions that reference data-bound fields or images.

To add a page header or footer, follow these steps:

1. In the Report Designer, click on the design surface away from the report body.

2. From the main menu, select Report, Add Page Header (or Add Page Footer).

After you add the page header or footer, drag the report items you want to use onto the header or footer area.

A header or footer for a complete report is not the same as a page header or footer. Report headers and footers appear only once, as the first and last content in the report, and there is no special area for report headers and footers. You can create a report header by placing report items at the top of the report body, and you can build a report footer by placing items at the bottom of the report body.

Adding a Text Box

A text box is a report item that displays a text string. The string displayed by a text box can be static text, an expression, or a field from a dataset. You use the Value property to configure the displayed string. Table 10-6 shows some examples of string values you might use in a text box.

Table 10-6 Text Box String Value Examples

Description	Value
Expression	=“Page:” & Globals!PageNumber & “of” & Globals!TotalPages
Field	=First(Fields!Name.Value)
Static text (such as a column heading)	Sales Amounts in US$

Other properties let you change the layout of the text box. The CanGrow and CanShrink properties let SSRS expand or contract the height of a text box based on its contents.

You can also use the Action property to configure the equivalent of a hyperlink that will let a user jump to another part of the report, browse a different report, or navigate to a standard URL. You will learn more about the Action property in the section “Defining Report Actions” in Lesson 3, “Working with Advanced Report Object Properties,” later in this chapter.

Adding an Image

SSRS supports four image formats: JPEG, GIF, PNG, and BMP. Images can be embedded in the report, stored in the report server, stored in the database, or stored on the Web and referenced in the report. You can set up image properties when you drag an image report item from the Toolbox window to the Report Designer.

The Image Properties window includes the following groups of properties:

• General In this group, you define the name, tooltip, source (embedded, external, or database), and path for the image. If the source is embedded, you have to import the image from a file. If the source is a database, you can select any of the fields in your report datasets. If the source is external, you can use a URL to access the image, and the URL can also point to your report server.

• Size In this group, you define how to display the image and padding options. You can specify whether to retain the image size and resize the container for the image, to resize the image to fit inside its container, to fit the image proportionally by resizing it while maintaining the aspect ratio, or to clip the image to fit inside its container.

• Visibility In this group, you define whether the image is visible initially and the expression to toggle visibility.

• Action In this group, you define an action on the image. You will learn about actions later in this chapter.

• Border In this group, you define borders for the image.

Adding a Chart

Another type of report item that you can add to a report is a Chart report item. Charts belong to a type of report item called data regions. Table, Matrix, and List report items are also data regions (and as was noted earlier, all three are simply templates for the Tablix report item). Data regions take rows from datasets and display the information to the user. Table, Matrix, and List report items display the information in a repeated rows format, and the Chart report item summarizes the information in a graphical interface.

To add a chart to a report, follow these steps:

1. In the Report Designer, click the Design tab.

2. In the Toolbox window, double-click the chart item or drag a chart item onto the report area.

3. Select the chart type. (You can change the chart type later by right-clicking the Chart report item and then selecting the chart type you want the report to display.)

4. From the Report Data window, drag the column you want to use as the category onto the Drop Category Fields Here section of the chart. Categories are groups of data that will be displayed together. By default, categories are displayed on the x-axis of a line or column chart.

5. From the Report Data window, drag the column you want to use as the series onto the Drop Series Fields Here section of the chart. Series are optional groups of data that will be displayed together. By default, series are displayed with different colors in line or column charts.

6. From the Report Data window, drag the column you want to use as the values onto the Drop Data Fields Here section of the chart. Values define the size of chart elements (lines, columns, and areas).

To understand the relationship between chart axes and data to a matrix, consider a chart as a matrix, in which you can treat chart categories as matrix columns, a chart series as matrix rows, and chart data as matrix data.

Adding a Gauge

In the Gauge data region, you can display a single value in your dataset. Gauges are useful for displaying key performance indicators (KPIs). You can also use a gauge inside a table or matrix to illustrate values inside each cell. A gauge is always positioned inside a gauge panel. You can add child or adjacent gauges in the gauge panel. You might want to use multiple gauges in a single gauge panel to compare data between fields.

You can use two types of gauges: radial and linear. Both types have pointers. A radial gauge has a pivot point around which one or more pointers rotate. A linear gauge displays the values as a portion of the scale of the gauge in a horizontal or vertical orientation. You select the gauge type when you add a gauge to your report by dragging the Gauge report item from the Toolbox window. However, you cannot change the gauge type in the same way you change a chart type—to change the gauge type, you must remove the gauge and then add a new gauge.

You can change the other gauge properties by right-clicking the gauge and then clicking Gauge or Gauge Properties. From the Gauge option, which is actually a submenu, you can define scale, range, pointer, and gauge panel properties. From the Gauge Properties option, you can change general properties, such as name and size, frame and fill options, and actions. When you right-click a gauge, you can also add a scale, a pointer, a range, a gauge label, and an adjacent gauge.

Adding a Tablix

The Tablix data region is probably the mostly used data region. You can use a Tablix to display fields from a dataset, grouped or detailed, and in a grid or free-form layout. You can add a Tablix to your report by dragging a Table, Matrix, or List template from the Toolbox window. The template choice gives you the initial shape of the Tablix; however, you are not limited to this initial choice. You can modify the Tablix design later by adding groups, totals, labels, nested groups, independent adjacent groups, or even recursive groups.

Use the Table template as your starting point when your goal is to display detail data and row groups. Use the Matrix report item as your starting point when you need to present aggregated data summaries, grouped in rows and columns similar to Microsoft Office Excel PivotTables. And use the List template as your starting point to create a free-form layout.

A single Tablix, as is the case with a single chart, is bound to a single dataset. You will learn more about designing a Tablix in the practices for this chapter and in Chapter 11.

Adding Other Report Items

SSRS also lets you configure other graphical elements, such as lines and rectangles, to add visual effects to your report. To add a graphical item, drag the item from the Toolbox window onto the design surface in the Report Designer.

In addition, you can display another report—a subreport—inside the body of a main report. A subreport is similar to a frame in a Web page. Typically, you would use parameters to filter datasets in the subreport and then pass parameters from the main report to the subreport. For example, you could have a main report showing sum of sales per product and a subreport showing product details for a specific product, using ProductId as a parameter to filter the subreport’s dataset. You would open the subreport through an action defined on the product name field in the parent report. You will learn about dataset filters in Chapter 11, and you will see how actions work later in this chapter.

Practice: Creating and Modifying a Report

In this practice, you will use the Report Wizard in BIDS to create a report. You will then change the report properties so that the report fits into a landscape layout and modify the report to make it easier for users to understand. You will also change some item properties and then add a footer and a chart to the report.

EXERCISE 1: Use the Report Wizard to Create a Report

In this exercise, you create a report project and then use the Report Wizard to create a report.

1. Open BIDS.

2. From the main menu, select File, New, and then Project to create a new project in which you can develop the report.

3. In the New Project dialog box, select Business Intelligence Projects, and then select the Report Server Project Wizard template.

4. Type TK 448 Ch10 SSRS Purchasing as the name of the project. Name the solution TK 448 Ch10 SSRS. You will use this project to create Adventure Works purchasing reports. Click OK to create the project and start the Report Wizard.

5. On the Welcome To The Report Wizard page, click Next to begin the report configuration.

6. On the Select The Data Source page, type AdventureWorks2008 as the data source, and then click Edit to configure the connection string.

7. Leave the default data source type as Microsoft SQL Server. Click Edit to configure the connection string.

8. In the Connection Properties dialog box, type (local) as the server name. For the database name, type or select AdventureWorks2008. Leave the default security as Use Windows Authentication. Click Test Connection to validate the connection information, and then click OK to close the Connection Properties dialog box and continue the connection string configuration in the Report Wizard.

9. On the Select The Data Source page, select the Make This A Shared Data Source check box, and then click Next.

10. On the Design The Query page, click Query Builder to open the Query Designer dialog box. You use the Query Designer to enter the SQL command that will retrieve data for the report.

11. In the SQL pane, enter the following query to select monthly purchasing information by product category and subcategory:

    SELECT
      YEAR(poh.OrderDate) * 100 + MONTH(poh.OrderDate) AS OrderMonth
     ,pc.Name AS ProductCategory
     ,psc.Name AS ProductSubCategory
     ,p.Name
     ,SUM(pod.OrderQty) AS OrderQty
     ,SUM(pod.LineTotal) AS Amount
    FROM Production.ProductCategory AS pc
         INNER JOIN Production.ProductSubcategory AS psc
          ON pc.ProductCategoryID = psc.ProductCategoryID
         INNER JOIN Production.Product AS p
          ON psc.ProductSubcategoryID = p.ProductSubcategoryID
         INNER JOIN Purchasing.PurchaseOrderDetail AS pod
          ON p.ProductID = pod.ProductID
         INNER JOIN Purchasing.PurchaseOrderHeader AS poh
          ON pod.PurchaseOrderID = poh.PurchaseOrderID
    GROUP BY pc.Name
             ,psc.Name
             ,p.Name
             ,YEAR(poh.OrderDate) * 100 + MONTH(poh.OrderDate)
    ORDER BY ProductCategory, ProductSubCategory, p.Name, OrderMonth;

    In the installed practice files, you will find completed solutions for this chapter’s practices. The installed practice files are located in the C:\Users\username\Documents\Microsoft Press\MCTS Training Kit 70-448\Source\Ch 10\folder. You might want to copy queries—especially lengthy ones—from the folder to avoid a lot of typing.

12. Click OK to return to the wizard.

13. Click Next to accept the query and continue.

14. On the Select The Report Type page, select Matrix, and then click Next.

15. On the Design The Matrix page, configure the fields as shown in the following table.

    Field	Displayed Fields Section
    ProductCategory	Page
    OrderMonth	Column
    ProductSubCategory	Row
    OrderQty	Details
    Amount	Details

16. Click Next to continue.

17. On the Choose The Matrix Style page, select Ocean, and then click Next.

18. On the Choose The Deployment Location page, verify the deployment location. Note that SSRS 2008 uses port 8080 on the 32-bit Windows XP operating system, so you might have to change the Report Server Deployment option to http://localhost:8080/ReportServer, for example. You can verify the SSRS Web service URL in the Reporting Services Configuration Manager tool or in SSMS by right-clicking your report server in Object Explorer and then clicking Properties. For this step, leave the deployment folder as is, and then click Next.

19. On the last page of the Report Wizard, type PurchasingSummary as the name of the report, and then click Finish to generate the report.

20. To preview the report, in the Report Designer, click the Preview tab.

EXERCISE 2: Use the Report Designer to Modify Report Properties

In this exercise, you change the layout of the report and edit its general properties to configure the way the PDF version of the report will look.

1. Click the Preview tab in the Report Designer. Notice that the HTML version of the report is only three pages long. The HTML rendering format uses logical pages and not physical pages, and you get one page per product category.

2. Click the Export button on the Preview tab toolbar, and then select Acrobat (PDF) File. To review the file formatting, export the file to your desktop, accepting the default file name.

3. Open the exported file, and then browse the content. Notice that this version is 28 pages long, and it displays very few columns. Close the file, and then return to BIDS.

4. Select the Design pane in the Report Designer.

5. If the Properties window is not displayed, from the main menu, select View, Properties Window or press F4.

6. In the Object box in the Properties window, select Report.

7. Configure the properties as shown in the following table.

   Property	Value
   PageSize\Width	11 in
   PageSize\Height	8.5 in
   Margins\Left	.5 in
   Margins\Right	.5 in
   Margins\Top	.5 in
   Margins\Bottom	.5 in

8. Click the Preview tab. Notice that the HTML version of the report has not been affected by the change.

9. Click Export on the Preview tab toolbar again, and then select Acrobat (PDF) File. Replace the previous exported file on your desktop.

10. Open the exported file, and then browse the content. Notice that the report is now only 14 pages long and displays more columns than the previous version. Close the file, and then return to BIDS.

EXERCISE 3: Edit Report Item Properties

In this exercise, you change some properties generated by the Report Wizard, widening the Name column and using a customized format to display the sales amount.

1. In the Report Designer, click the Design tab.

2. Select the TextBox3 element, which is the last row and last column of the matrix element.

3. In the Properties window, select the Number, Format property, and then type the value $#,#.##.

4. Select the ProductSubCategory box, which is the last row and first column of the matrix element.

5. In the Properties window, expand the Size property, and then change Width to 2in.

6. Preview the report, noting the changes.

EXERCISE 4: Add a Page Footer

In this exercise, you continue your modifications by adding a page footer that displays the user name for the report and page numbers.

1. In the Report Designer, click the Design tab.

2. On the Report menu, select Add Page Footer. A new design area is added to the Report Designer.

3. Drag a Textbox report item from the Toolbox window to the page footer area. Position the text box in the top-left corner of the footer. Configure the properties of the text box, as shown in the following table.

   Property	Value
   Name	txtUser
   Width	3 in

4. In the Report Data window, expand Built-in Fields. (If the window is not visible, open it from the View menu by choosing Report Data.) Drag the User ID field to the txtUser box. BIDS creates the full expression for you. You can verify the full expression by right-clicking the text box and then clicking Expression.

5. Drag another Textbox report item from the Toolbox window to the page footer area. Position the text box in the bottom-left corner of the footer, under the UserId box, as Figure 10-2 shows.

   

   Figure 10-2 Report with page footer

6. Configure the properties of the text box, as shown in the following table.

   Property	Value
   Name	txtPage
   Width	3 in

7. Type Page: in the txtPage box. (Important: Be sure to type a colon and a space following the label text.)

8. Drag the Page Number field after the space following the colon. Type of (with a space before and after the text) after the Page Number field, and then drag the Total Pages field to the end of the expression.

EXERCISE 5: Add a Chart

Now you are ready to add a chart to the report.

1. In the Report Designer, click the Design tab.

2. Select the Body element. Body is a transparent element in the body of the report, behind the matrix and before the page footer. You can select Body by clicking an empty area in the body of the report or by selecting Body from the Object dropdown list in the Properties window. Because the wizard did not leave much empty space in the body of the report, it is probably easier to use the second method to select Body properties in this example.

3. In the Properties window, set the height of the Body element to 6 in. Also change the width to 6 in. You are making the Body element larger to make room for the Chart element.

4. Drag a Chart element from the Toolbox window to below the Matrix element. Select the Stacked Column chart type, and then click OK. Position the chart below the matrix, with left borders aligned. Leave some space for two matrix rows between the bottom of the matrix and the top of the chart. You will expand the matrix in the practice for Lesson 3 later in this chapter.

5. Right-click the chart, select Chart Properties, and then change the Name property to chtAmount.

6. In the Properties window, make sure that the chtAmount chart is selected and configure the properties as shown in the following table.

   Property	Value
   Height	4 in
   Width	6 in

7. From the Toolbox window, select the Dataset pane. If the Dataset pane is not visible in the Toolbox window, from the main menu, select View, Datasets.

8. In the Design tab, select the chart. Drag the ProductCategory field from the Toolbox window onto the Drop Series Fields Here area of the chart. Drag the OrderMonth field onto the Drop Category Fields Here area. Last, drag the Amount field from the Toolbox window onto the Drop Data Fields Here area.

9. Right-click the ProductCategory field in the series area, and then select Series Group Properties. In the Series Group Properties window, select the Sorting pane. Click Add, and in the Column dropdown list, select the ProductCategory field.

10. Right-click the OrderMonth field in the Series area, and then select Category Group Properties. In the Category Group Properties window, select the Sorting pane. Click Add, and in the Column dropdown list, select the Order Month field.

11. Double-click the vertical axis title, and then rename it Sales Amount.

12. Double-click the horizontal axis title, and then rename it Product Categories.

13. Double-click the chart title, and then rename it Sales Amount for Product Categories over Months.

14. Save the report, and then select the Preview pane to review it.