Quick Start > Create a Word Report with Microsoft Query

Create a Word Report with Microsoft Query

This tutorial will help you create your first report in Word using the OfficeWriter for Word Designer and Microsoft Query. This demonstration is based on the HRTemplate sample provided with OfficeWriter. The report displays data from the AdventureWorks database, which ships with Reporting Services. We will build a simple employee list and display some basic information about each employee.

By default, OfficeWriter Designer's Add Query, Edit Query, and Delete Query buttons are disabled. These buttons are required for creating queries using Microsoft Query with OfficeWriter. If the query buttons are not available on the OfficeWriter toolbar, reset the registry key Enable MSQuery as follows:

1. Open the Start menu and select Run.
   
   
2. Enter regedit and click Ok.
   
   
3. Open the folder HKEY_CURRENT_USER\Software\SoftArtisans\OfficeWriter\Designer.
   
   
4. Double-click Enable MSQuery. A dialog will open.
   
   
5. In the Value Data field, enter 1.

Before you start, verify the following:

• Reporting Services is installed on a server you can access, working properly, and you have rights to publish and view reports.
  
  
• The AdventureWorks database (which ships with Reporting Services) is installed on the Reporting Services server.
  
  
• OfficeWriter with the Reporting Services renderers is installed on the Reporting Services server.
  
  
• The OfficeWriter Designer Client is installed on your local PC.

For more information on installing and configuring OfficeWriter Reporting Services Integration, see Installation or your System Administrator.

Step 1. Create a Word File

The file you create will become an OfficeWriter Word template.

1. Open Microsoft Word and create a new file.
   
   
2. Add a table with two columns and two rows.
   
   
3. Optional: Add the following header to the document.

   AdventureWorks
   Human Resources Dept.
   
   Employee Listing

This is our sample document before adding data:

Step 2. Create a Database Query

Now we use OfficeWriter to turn this into a dynamic report. We will be using the OfficeWriter Designer toolbar to set up our report. You should see the Designer under Word's menu bar above your document.

Notice that not all the items on the toolbar are enabled. As we progress, more items will become available.

1. Select Add Query from the toolbar.
   
   
2. In the dialog box, type in a name for the query (or leave the default name "Query1"). Click Next.
   
   
3. In the database connection dialog box, click Add New. Microsoft Query will open.
   
   
4. Select New Data Source and click Ok.
   
   
5. Enter a name for the data source.
   
   
6. Click the down arrow on the right side of the step two box and find the SQL Server driver.
   
   
7. At the step 3 line, press the Connect button.
   
   
8. In the SQL Server Login dialog, pull down the Server list with the down arrow and choose the server on which the AdventureWorks database resides.
   
   
9. Click Options.
   
   
10. On the database pulldown, find the AdventureWorks database and select it. Click Ok.
    
    
11. You will now see your data source in the list. Make sure it is selected and click Ok.
    
    
12. Now you should see the AdventureWorks database listed next to the Connect button. Click Ok.
    
    
13. In the Add Tables dialog box, find the Employee table in the list and select Add. Then, find the Contact table and select Add. Click Close. The Employee and Contact tables should now be in MS Query and look like this:
    
    
    
    
14. In MS Query, scroll down the list of fields in the Employee table until you find the EmployeeID field.
    
    
    
    
15. Click the EmployeeID field and drag it to the small white box under the Employee table (shown by the arrow in the picture below).
    
    
    
    It should now look like this:
    
    
    
    
16. Do the same for FirstName, LastName, and EmailAddress from the Contact table and then for Title from the Employee table. The finished query should look like this:
    
    
    
    
17. From the MS Query menu, select File -> Return to OfficeWriter Designer.

Notice that now all the menu items on the OfficeWriter Designer toolbar are active.

Step 3. Add Merge Fields to the Template

A merge field is a placeholder for database values. When the report is published, OfficeWriter will replace the merge fields with data from the database.

1. Place your cursor in the top left cell of the table in your report and click inside it.
   
   
2. On the OfficeWriter Designer, click Insert Merge Field. You will see all the fields that you added to your query.
   
   
3. Select the LastName field. Your report should now look like this:
   
   
   
   
4. After the Query1.Lastname field, type in a comma and then a space.
   
   
5. Select Insert Merge Field again and pick the FirstName field. Your report should look like this:
   
   
   
   
6. Add the rest of the fields. Place your cursor in the top right cell of the table. Select Insert Merge Field and pick Title. In the bottom left cell, type Employee ID: and insert the EmployeeID field from the menu. Finally, in the bottom right cell, put the EmailAddress field. Your finished report document should look like this:
   
   
   
   
7. Click Select Query on the OfficeWriter Designer and choose your query from the list.
   
   
8. With your mouse highlight the entire table on your report. Select not only the table, but also include a blank row below the table.
   
   
   
   
9. Select Query Range from the OfficeWriter Designer.
   
   
10. Make sure that Selection is chosen and click Ok.

Note on step 8: If you highlight a table without highlighting a row after it, the report will include a separate table for each employee without spaces between them. It would look like one long continuous table. Many times you will want to do this, but for this demo, we want to make it look like separate tables.

Step 4. Publish and View Your Report

Before publishing the report on the Reporting Services server, we must save the template locally as an RDL file.

1. From the OfficeWriter Designer toolbar select Save As.
   
   
2. Navigate to a local folder where you will store the template report file. This will be where you store your work file in case you want to edit it later, not a location on the Reporting Services server.
   
   
3. Save the template as an RDL file.
   
   
4. To publish the report, from the toolbar, select Publish.
   
   
5. From the Publish dialog box select the drop-down to display the Server or URL.
   
   
6. Choose the server on which Reporting Services is installed.
   
   
7. Click Refresh.
   
   
8. Select a folder for which you have publish rights and click Ok.
   
   
9. If your report published with no errors, you will receive a Publish Successful message dialog. Click Ok to continue.
   
   
10. To view the report, click View on the OfficeWriter Designer. The report viewer will open another Word window with the report results. It should look like this:
    
    
    
    
11. Select Close Report View to return to your report template.

To verify that your report is actually running on Reporting Services:

1. Open a browser and go to the Reporting Services Report Manager (usually installed at http://<server address>/Reports).
   
   
2. Navigate to where you published your report.
   
   
3. Select the report. Reporting Sevices will display the report but only show you the data set.
   
   
4. From the Select a format drop-down, choose Word (.doc) designed by OfficeWriter.
   
   
5. Click Export and - when prompted - Open. Word will open and display your report the way you created it.

Samples

Download an RDL file with each result displayed in a separate Word table.

Download an RDL file with all results displayed in one Word table.

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.