Quick Start > Create an Excel Report with Microsoft Query

Create an Excel Report with Microsoft Query

This tutorial will help you create a report in Excel using the OfficeWriter for Excel Designer and Microsoft Query. The report displays data from the AdventureWorks database, which ships with Reporting Services. We will build a simple product list and display some information about each product.

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 OfficeWriter 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 an Excel File

The file you create will become an OfficeWriter Excel template.

1. Open Microsoft Excel and create a new file.
   
   
2. In cell A1, type Product ID.
   
   
3. In cell B1, type Product Name.
   
   
4. In cell C1, type List Price.
   
   
5. In cell D1, type Days to Manufacture.
   
   

Step 2. Create a Database Query

We will be using the OfficeWriter Designer toolbar to create the template. You should see the Designer under Excel's menu bar, above your worksheet.

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

1. Select Add Query from the toolbar.
   
   
2. It 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. In the Add Tables dialog box, find the Product table in the list and select Add. Then, find the ProductListPriceHistory table and select Add. Click Close. The Product and ProductListPriceHistory tables should now be in MS Query and look like this:
    
    
    
    
13. In MS Query, scroll down the list of the Product table until you find the ProductID field:
    
    
    
    
14. Click on the ProductID field and drag it to the small white box under the Product table (show by the arrow in the picture below).
    
    
    
    It should now look like this:
    
    
    
    
15. Do the same for Name from the Product table, ListPrice from the ProductListPriceHistory table, and DaystoManufacture from the Product table. The finished query should look like this:
    
    
    
    
16. 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 Data Markers to the Template

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.

1. Place your cursor in cell A2 and click inside it.
   
   
2. Select Insert Field from the OfficeWriter toolbar. You will see all the fields that you added to your query. Select the ProductID field. Your template should now look like this:
   
   
   
   
3. Place your cursor in cell B2.
   
   
4. Insert Field again select the Name field. Your template should look like this:
   
   
   
   
5. Place your cursor in cell C2.
   
   
6. Click Insert Field and select ListPrice.
   
   
7. Place your cursor in cell D2.
   
   
8. Click Insert Field and select DaystoManufacture. Your finished template should look like this:
   
   
   
   

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 temlate 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 Excel 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 Excel (.xls) designed by OfficeWriter.
   
   
5. Click Export and - when prompted - Open. Excel will open and display your report the way you created it.

Samples

Download the .rdl file described in this tutorial.

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.