OfficeWriter Documentation
.
SSRS Integration v8 Docs
Space Shortcuts
Page tree
ConfigureSpace tools
Skip to end of metadata
Go to start of metadata

In Adding a Template to an Existing Report, we see how to add OfficeWriter template information to a saved RDL file. In that case, the database query was defined before OfficeWriter opened the file in Word. This section will explain how to develop a report from start to finish in Word with OfficeWriter and Microsoft Query.

Icon

Before you begin, make sure that MS Query is enabled for the OfficeWriter Designer. Instructions for enabling MS Query can be found in Using MS Query in Word to Design Reports.

Creating a Data Source

  1. Open Word and create a new blank document.

  2. Click Add DataSet on the OfficeWriter Designer toolbar.

  3. In the dialog box, type in a name for the query (or leave the default name "Query1"). Click Next.



  4. Click Add New to add a new database connection.



  5. Select New Data Source and click Ok.



  6. Enter a data source name and select SQL Server from the drop-down list of database drivers. Click Connect.



  7. From the drop-down list of server names, select the server where the AdventureWorks database is installed. Click Options and select AdventureWorks as the default database. Click Ok.



  8. You should now be back at the Create New Data Source screen. Click
    Ok.

  9. You will see your new data source in the Choose Data Sourcedialog. Select it and click Ok to open Microsoft Query.

Building a Query

Next, we will build a query that will get the data for our report from the database. OfficeWriter Designer uses Microsoft Query to create queries. When we finished creating our sample Data Source above, Microsoft Query opened.

  1. In the Add Tables dialog box, find the Employee table in the list and select Add. Then, find the Contact table and select Add.



  2. Click Close. The Employee and Contact tables should now be in MS Query.



  3. Add the FirstName and LastName fields to your query by dragging them from the Contact table box to the space below, as shown. Do the same with EmployeeID from the Employee table.



    Your query should now look like this:



  4. We will add a filter so that only current employees are returned from our query. Click Show/Hide Criteria on the Microsoft Query toolbar.



  5. Pull down the Criteria Field list and select Employee.CurrentFlag as shown. Enter the value 1.



  6. From Microsoft Query's File menu, select Return to OfficeWriter Designer.

Adding Merge Fields

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 at the top left of your document.

  2. Click Merge Fields on the OfficeWriter toolbar and make sure that the query you just created is selected from the list of DataSets.

  3. Under Insert Merge Field on the OfficeWriter toolbar, select EmployeeID to insert the field in the document.



  4. Press the Tab key to add a space after the field you inserted.

  5. Repeat the above steps to insert a field for Lastname.

  6. Type a comma and a space after the Lastname merge field.

  7. Repeat the steps to insert a merge field for Firstname.

  8. Press Enter. Your report should look like this:



  9. Highlight the line in your document that contains the merge fields, and a blank line below the line of merge fields.



  10. Go to Merge Fields > your data set > Set Selection to Repeat Region to set the highlighted area to be a repeat region.

Icon

Note on step 9: If you highlight the line of merge fields without highlighting a line after it, the report will repeat the fields for each row of data on the same line in the document. Since we want each row of data to appear on a new line in the document, we must select the blank line below the merge fields. This ensures that a line break is contained in the query range.

Saving and Publishing

Do not use Word's File/Save menu item to save your template. Use the Save button on the OfficeWriter toolbar to save your template as a report definition file in the RDL format.

  1. From the OfficeWriter Designer toolbar select Save > Save to Disk.

  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.

Next, deploy and view your report. Your results should look like this:

Samples

Download the .rdl file created in this section.

  • No labels