Report Exercise One - Work Orders Assigned to Tradesmen

Produce a report that lists out the Work Orders created within a given date range that have had tradesmen assigned to them. Each Work Order should display the:

• Work Order Number
• Job Description
• Date Raised
• Status
• Tradesmen assigned to the Work Order

The report must have the following parameters:

• Department
• Trade
• Start and Finish Date

Step 1: Add the New Report to the Reports Listing

1. Go to the Reports Control file and click on “New” to open up a blank Report Details screen.
2. Enter a name for the report into the “Report Name” field (Work Orders Assigned to Tradesmen).
3. Enter a Description for the report (A list of the Work Orders that have had tradesmen assigned to them).
4. Enter a name for the report into the “Report Name” field (Work Orders Assigned to Tradesmen).
5. Enter a Description for the report (A list of the Work Orders that have had tradesmen assigned to them).

Step 2: Define the Report's Query

The next step is to create the SQL query that the report will be based on. The first step in creating a new report is to define the report's Query. 

1. Click the “design” button to access the Report Editor. The Report Designer screen will come up.
2. Click on the "Set Datasource" button to open up the Query Designer.

You will now select the tables that you need for your Query. You want information on the Work Order, the Work Order Status, and the tradesmen on the job, so based on the relational diagram below you will need the WorkOrder, WorkOrderStatus, WorkOrderTrade, and Contacttables.

To Select the tables:

Locate the tables in the list on the left-hand side of the screen and double click on them to add them to the query. The tables you will need are: WorkOrder, WorkOrderStatus, WorkOrderTrade, Contact

The WorkOrderTrade and Contact tables are related to each other with the “TradeContactID” field int he WorkOrderTrade table and the “ContactID” field in the Contact table. 

Create the relationship by:

1. Click on the TradeContactID field in the WorkOrderTrade table.
2. Hold the mouse button down.
3. Drag the TradeContactID field onto the ContactID field in the Contact table.
4. Release the mouse button.

Repeat the process for the remaining tables using these links:

First Table.Field	Second Table.Field
WorkOrderStatus.WorkOrderStatusID	WorkOrder.WorkOrderStatusID
WorkOrderTrade.WorkOrderID	WorkOrder.WorkOrderID

The tables will look something like this:

Locate the fields that you want to display in your report and tick their check-boxes to add them to the query. The fields are:

• WorkOrderNumber
• WorkOrderDescription
• RaisedDateTime
• WorkOrderStatusDescription
• FirstName
• LastName

Give your fields more meaningful names to be displayed into the report. User-friendly names are entered into the "Column Name" as shown below:

Because you want to be able to filter the report by Department and Trade, also include these fields:

• DepartmentID
• TradeContactID 

Because you want to be able to range the report by date, enter the following into the Criteria for the RaisedDateTime field: Between @Date1Start and @Date1End

Click the "Preview" button in the top right-hand corner of the screen to preview the results of your Query.

When you close the Report Query Designer screen you return to the Report Editor.

Step 3: Run the Report Wizard

The next step is to run the Report Wizard to lay out the results of the query onto the page.

1. In the Report Designer, click on the "Wizard" icon to start the Report Wizard.

   

2. In the first screen of the Report Wizard, select “Data-bound Report” and click on “Next” to continue.

3. In the second screen of the Report Wizard, select the fields that you wish to display in the report. Select all of the fields except for the DepartmentID and TradeContactID fields, they have only been included in the query for filtering:

• Tick on the MEXQuery check-box on the left.
• Tick the check-boxes for the fields that you wish to display in the report on the right.

4. In the third screen of the Report Wizard you define the headings that you wish to include in the report, and the fields that you wish to display in the headings. To make a field a heading, select the field that you wish to have a heading for in the left-hand list and click on the ">" button. To add additional fields into the heading that you have created select them in the left-hand list and click on the "+" button. In this case we want a heading which includes the first and last names of the tradesmen:

• Select the FirstName field and click on the > button
• Select the LastName field and click on the +button

5. In the fourth screen of the Report Wizard you can indicate which of the report's fields you wish to have summary calculations displayed for. For this report we would like a count of the Work Orders assigned to each tradesman, so:

• In the left-hand drop-down, you select the field that you want to do a summary calculation for, this case the
  Work Order Number.
• In the right-hand drop-down, you select the type of calculation that you want to use for your summary, in this case Count.

In the example below the report will display a total for the EstimatedDurationHours field and count the number of Work Orders.

6. Finally, enter a title for your report, in this case “Work Orders Assigned to Tradesmen”. Click on“Finish” to complete the wizard and generate our report.

Step 4: Editing Your Report

The Report Wizard rarely produces the exact report that you are looking for, so you will have to use the Editorto make some changes to it. This usually involves re-organising the labels and resizing the fields. The firststep is to preview the report and decide what changes you want to make:

1. Click on the "Preview" icon to preview the report.
   

When we look at the report preview we can see that three things need to be changed:

• In the tradesman heading the FirstName label could be changed to "Name" and the LastName label could be removed.
• The time component in the Raised Date field is not needed. The format should be changed to dd/MM/yyyy.
• More space could be given to the Description field.

2. Close the preview and return to the Report Designer by clicking on the "Design" icon.

In the Report Designer, re-organise the tradesman header by:

• Removing the "LAST NAME" label by clicking on it to select it, then pressing on the Delete button on your keyboard.
• Double-clicking the "FIRST NAME" label and edit its text to "NAME".
• Moving the LastName field next to the FirstName field.

4. Change the format of the "Raised Date" field by:

• Clicking on the "Raised Date" field to select it.
• Displaying the field's properties by clicking on the "Properties" icon in the top-left corner of the screen. 
• Clicking on the "..." icon in the "Text Format String" property.
• Select the appropriate date format from the "Types" list and click on "OK"

You can now move and re-size the fields and labels until you are happy with the layout of your report. Regularly switch between Preview and Design so that you can see the effect of your changes as you make them.

Save the report by clicking on the Save icon in the top left-hand corner of the screen.

Close the Report Editor to return to the report Details screen.

Step 5: Enabling the Report Parameters

In the Report Details screen, select the parameters that you want available for the report in the Reports Menu. 

With the query for this report you can tick the Department, Trade and Date1 check-boxes.

Close the Report Details screen to return to the Reports Control File. You can now go to the Reports Menu to test your report.