Repair Count By Type Report |
 |
| 27 Feb 2006 | SearchSQLServer.com |
|
|
 The following excerpt, courtesy of McGraw-Hill Osborne Media, is from Chapter 5 of the book Microsoft SQL Server 2005 Reporting Services 2005 written by Brian Larson. Click here to purchase the book.
Repair Count By Type Report
Now let's move down the road a little further and create a matrix
report without the wizard.
The Repair Count By Type Report
Features Highlighted
- Creating a matrix report from scratch
- Using a specialized property dialog box
Business Need
GDS needs to purchase several new transports to
update their delivery fleet. The company must decide which type of
transport to purchase. One factor in the decision is the amount of
time the new transports will spend in the maintenance hanger for
repairs and preventative maintenance.
Upper management has asked the GDS maintenance department to provide a
report showing the number of each type of repair required by each type
of transport. The report should include statistics from all
transports, both active and retired. Also, the report should group the
repairs by their cause.
Task Overview
- Reopen the Chapter05 Project, Create a New Report in the Chapter05
Project, Select the Shared Data Source, and Create a Dataset
- Place a Matrix Item on the Report and Populate It
- Add Column Grouping and Other Report Formatting
Repair Count By Type Report, Task 1: Reopen the Chapter05 Project,
Create a New Report in the Chapter05 Project, Select the Shared Data
Source, and Create a Dataset
- If you closed the Chapter05 project, reopen it.
- In the Solution Explorer on the right side of the screen,
right-click the Reports folder.
- Put your mouse pointer over Add in the Context menu and wait for the submenu
to appear. Select New Item from the Context menu. This displays the
Add New Item Chapter05 dialog box.
- Make sure the Report icon is selected in the Templates area. Enter
RepairCountByType for the name.
- Click Add. A new report called RepairCountByType.rdl is created in
the Chapter05 project. You are taken to the Data tab of this new
report.
- Select from the Dataset drop-down list. The Dataset
dialog box appears.
- Enter RepairsByType for the name in the Dataset dialog box.
- Galactic (shared) is selected for the data source by default.
Click OK. You return
to the Data tab, which now displays the Generic Query Designer.
- Click the Generic Query Designer button to switch to the Graphical
Query Designer.
- Right-click in the diagram pane of the Graphical Query Designer
screen. Select Add Table from the Context menu.
- Add the following tables to the query:
Repair (dbo)
Transport (dbo)
TransportType (dbo)
RepairWorkDoneLink (dbo)
WorkDone (dbo)
RepairCause (dbo)
- Click Close to exit the Add Table dialog box.
- Check the following column in the Repair table:
RepairID
- Check the following column in the TransportType table:
Description
- In the criteria pane, type TypeOfTransport in the Alias
column in the Description row.
- Check the following column in the WorkDone table:
Descrip tion
- In the criteria pane, type TypeOfWork in the Alias column
in the Description row for the WorkDone table.
- Check the following column in the RepairCause table:
Description
- In the criteria pane, type RepairCause in the Alias column
in the Description row for the RepairCause table.
- Type 1 in the Sort Order column for RepairCause. Type
2 in the Sort Order column for TypeOfWork
.
- Right-click in the SQL pane and select Execute SQL from the
Context menu. The Graphical Query Designer should appear similar to
this:
Figure 21
Task Notes Although this report is a pretty straightforward
request, we need to link together a number of tables to collect the
necessary data. What we are interested in is repairs, so we start with
the Repair table. However, none of the fields we need in the result
set are in the Repair table. To find the type of transport being
repaired, we need to join the Transport table with the Repair table,
and then join the TransportType table to the Transport table. To find
the type of work done, we need to join the RepairWorkDoneLink table to
the Repair table, and then join the WorkDone table to the
RepairWorkDoneLink table. Finally, to group by the cause of the
repair, we need to join the RepairCause table to the Repair table. If
you get confused by all of this, refer to Figure 3-23 in Chapter 3.
Repair Count By Type Report, Task 2:
Place a Matrix Item on the Report and Populate It
- Click the Layout tab to begin working on the report layout.
- Click the Matrix report item in the Toolbox. The mouse pointer
changes to a matrix icon and crosshairs when you move your mouse
pointer over the report layout area.
- Click-and-drag the mouse over the lower three-quarters of the
report layout.
- When you release the mouse button, after dragging, a matrix is
created to occupy the area you just defined. By default, every cell in
the matrix is occupied by an empty text box.
- In the Datasets window, expand the RepairsByType dataset. Drag the
TypeOfTransport field from the Datasets window and drop it on the cell
containing the word "Columns." The values in this column in the
dataset determine the columns in the matrix report.
- Drag the TypeOfWork field from the Datasets window and drop it on
the cell containing the word "Rows." The values in this column in the
dataset determine the rows in the matrix report.
- Drag the RepairID field from the Datasets window and drop it on
the cell containing the word "Data."
- In the cell where you just dropped the RepairID field, change Sum
to Count, so the contents of the cell appear as follows:
=Count(Fields!RepairID.Value)
- With this cell still selected, change the following property:
| Property | New Value |
| TextAlign | Center |
- Reduce the width and height of the columns in the matrix. When you
finish, your report design should look similar to this:
Figure 22
- Click the Preview tab. Your report should look similar to the
following illustration. The rows and columns in your report may appear
in a different order from those shown here.
Figure 23
Task Notes Because the matrix report always groups a number of
records from the dataset to create the entries in the matrix, the
field that supplies the data for the matrix must be enclosed in some
type of aggregate function. If the field placed in the data cell is a
number, Report Designer encloses the field in the SUM() aggregate
function.
The RepairID field, which we placed in the data cell in Step 7, is a
number. However, it does not make sense to add up the RepairIDs.
Instead, we want to count the number of RepairIDs. For this reason, we
changed the SUM() aggregate function to the COUNT() aggregate
function.
Repair Count By Type Report, Task 3:
Add Column Grouping and Other Report Formatting
- Click the Layout tab to return to the report layout.
- Click the cell in the upper-right corner of the matrix and change
the following properties:
| Property | New Value |
| FontWeight | Bold |
| TextDecoration | Underline |
- Click the square in the upper-left corner of the gray border to
select the matrix item.
- In the Properties window, click the Property Pages button shown in
the following illustration. The Matrix Properties dialog box
appears.
Figure 24
- Click the Groups tab.
- In the Rows area, click Add. The Grouping and Sorting Properties
dialog box appears.
- Next you set up your matrix for drilldown. Replace
matrix1_RowGroup2 with matrix1_RepairCause for the name. Select
Fields!RepairCause.Value from the drop-down list in the first row
under Expression. Click OK.
- In the Rows area, click Up to move matrix1_RepairCause to the top
of the list. Click the matrix1_TypeOfWork entry. Click Edit in the
Rows area. The Grouping and Sorting Properties dialog box
appears.
- Click the Visibility tab. Set the Initial Visibility to Hidden.
Click the Visibility Can Be Toggled by Another Report Item check box.
Select textbox2 from the Report Item drop-down list. (If textbox2 is
not in the drop-down list, type textbox2 for the Report item
value.)
- Click OK in the Grouping and Sorting Properties dialog box. Click
OK in the Matrix Properties dialog box.
- Click the cell in the upper-left corner of the matrix and change
the following properties:
| Property | New Value |
| FontWeight | Bold |
| TextDecoration | Underline |
| Value | Cause/Type of Repair Work |
- Click the Textbox report item in the Toolbox. Click-and-drag the
mouse over the area above the matrix on the report layout area. When
you release the mouse button, after dragging, a text box is created to
occupy the area you just defined. Click the text box and type the
following:
Repair Count By Type Report
- With the text box still selected, make the following changes in
the Properties window:
| Property | New Value |
| FontSize | Bold |
| TextDecoration | 16pt |
| FontWeight | Bold |
| TextAlign | Center |
- Your report layout should appear similar to the illustration.
Figure 25
- Click the Preview tab. The report should appear as follows.
NOTE
If the report displays an error message mentioning textbox2 when you
preview the report, then textbox2 was not the name assigned to the
text box containing the RepairCause group label. Return to the Layout
tab and select the text box containing the =Fields!RepairCause.Value
expression. Look at the Name property in the Properties window. Return
to the drop-down list you populated in Step 9 of this task and replace
textbox2 with this name. Click OK to exit the dialog boxes and preview
the report again.
Figure 26
- Click Save All in the toolbar.
Task Notes The Property Pages button in the Properties window
provides an alternative way to change the properties of a report item.
This button displays a dialog box that deals specifically with the
properties of the selected report item. These specialized property
dialog boxes can make it much easier to modify the properties of a
report item. You can also access the specialized property dialog boxes
by right-clicking a report item and selecting Properties from the
Context menu.
Click for the next excerpt in this series: Transport Information Sheet Task 1 and 2: Create New Report and Dataset
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
