.
In Adding a Template to an Existing Report Defintion, we saw how you can 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 Excel with OfficeWriter and Microsoft Query.
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:
- Open the Start menu and select Run.
# Enter regedit and click Ok.
# Open the folder HKEY_CURRENT_USER\Software\SoftArtisans\OfficeWriter\Designer.
# Double-click Enable MSQuery. A dialog will open.
# In the Value Data field, enter 1.
Creating a Data Source
- Open Excel.
# Click Add Query on the OfficeWriter Designer toolbar.
# In the dialog box, type in a name for the query (or leave the default name "Query1"). Click Next.
!xlw_NewTemplate1.jpg!
# Click Add New to add a new database connection.
!xlw_NewTemplate2.jpg!
# Select New Data Source and click Ok.
!xlw_NewTemplate3.jpg!
# Enter a data source name and select SQL Server from the drop-down list of database drivers. Click Connect.
!xlw_NewTemplate4.jpg!
# 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.
!xlw_NewTemplate5.jpg!
# You should now be back at the Create New Data Source screen. Click
Ok.
# You will see your new data source in the Choose Data Source dialog. Select it and click Ok to open Microsoft Query.
!xlw_NewTemplate6.jpg!
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.
- In the Add Tables dialog box, find the HumanResources.Employee and Person.Contact table in the list and select Add.
!xlw_NewTemplate7.jpg!
# Click Close. The Employee and Contact tables should now be in MS Query.
!xlw_NewTemplate8.jpg!
# Add the Contact.FirstName, Contact.LastName, and Employee.EmployeeID fields to your query by double-clicking on the items. When you do, the columns will be added to the query.
\Your query should now look like this:
!xlw_NewTemplate10.jpg!
# From Microsoft Query's File menu, select Return to OfficeWriter Designer .
Adding Data Markers
A data marker is a placeholder for database values. When the report is published, OfficeWriter will replace the data markers with data from the database.
- Click Select Query on the OfficeWriter toolbar and make sure that the query you just created is selected.
# Place your cursor in cell A1.
# Click Insert Field on the OfficeWriter toolbar and select EmployeeID to insert the field in cell A1.
!xlw_NewTemplate13.jpg!
# Place your cursor in cell B1.
# Click Insert Field on the OfficeWriter toolbar and select Lastname.
# Place your cursor in cell C1.
# Click Insert Field on the OfficeWriter toolbar and select Firstname. Your report should look like this:
!xlw_NewTemplate14.jpg!
Saving and Publishing
Do not use Excel's File/Save menu item to save your template. Use the Save As button on the OfficeWriter toolbar to save your template as a report definition file in RDL format.
- From the OfficeWriter Designer toolbar select Save As.
# 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.
# Save the template as an RDL file.
Next, publish and view your report. Your results should look like this:
