Quick Start > Create an Excel Report from an Existing RDL

Create an Excel Report from an Existing RDL

This tutorial will help you design your first report in Excel using the OfficeWriter for Excel Designer from a report file created in Visual Studio. To follow this demonstration you need the AdventureWorks sample database which is an optional component in the Reporting Services installation.

We will build a simple employee list and display some basic information about each employee.

OfficeWriter Designer Toolbar

The control center for creating reports with OfficeWriter is the OfficeWriter Designer toolbar. When you open Excel, you will see the toolbar directly underneath the Excel toolbar, above your worksheet.

If you do not see the toolbar:

1. Open Excel's View menu and select >Toolbars > OfficeWriter.
   
   
2. Check OfficeWriter. If OfficeWriter is not listed, run the OfficeWriter client installer.
   

If your toolbar does not contain query-building buttons (Add Query, Edit Query, etc.), your registry settings have been set to disable MS Query. These buttons are not required for this tutorial, so you do not need to make any changes to your installation.

Opening a Report File

An a RDL file is an xml-based report definition file. We will open an RDL file created in Visual Studio. The file contains a database connection and a query. The query fields will appear in a drop-down list on the OfficeWriter toolbar. You will be able to insert these fields as merge fields (data placeholders) in the report template. You will not be able to create additional queries.

1. Open Microsoft Excel and create a new file.
   
   
2. Click the Open Report button on the OfficeWriter toolbar.
   
   
3. If your report defintion file was saved on a local or network drive, navigate to the folder containing the file, select it, and click Open. If your report defintion file was deployed to Reporting Services, click the Retrieve on the bottom right corner of the dialog box, and continue to step 3.
   
   
   
   
4. In the Report Browser dialog box, type in your report server's URL or Domain name and click Refresh. This will load all the available report folders into the list box. Navigate to your report file, select it, and click Ok.
   
   
   

Adding Fields to Your Report

For this demonstration we will use a report file created in Visual Studio containing the following query:

After opening your report in Excel with the OfficeWriter toolbar, you should find your query listed under the Select Query button.

1. Click Select Query on the OfficeWriter toolbar and select your query from the drop-down list.
   
   
2. Place your cursor in cell A2 cell and click inside it.
   
   
3. Click Insert Field on the OfficeWriter toolbar and select the ProductID field. Your template should now look like this:
   
   
   
   
4. Place your cursor in cell B2 cell and click inside it.
   
   
5. Click Insert Field again and select the Namefield. Your template should now look like this:
   
   
   
   
6. Place your cursor in cell C2 cell and click inside it.
   
   
7. Click Insert Field and select the ListPrice field.
   
   
8. Place your cursor in cell D2 cell and click inside it.
   
   
9. Click Insert Field and select the Days to Manufacture field.
   
   
10. In cell A1 type Product ID, in cell B1 type Product Name, in C1 type List Price, and in D1 type Days to Manufacture.
    
    

Your finished report template should look like this:

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 2005 © SoftArtisans, Inc. All Rights Reserved.