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.NET from an existing report file (RDL) that was created in Visual Studio or Report Builder. We will build a simple employee list and display some basic information about each employee.

OfficeWriter Designer.NET Toolbar

The control center for creating reports with OfficeWriter is the OfficeWriter Designer.NET toolbar. When you open Excel, you will the OfficeWriter Designer.NET tab in the ribbon.

If you do not see the toolbar:

• Make sure that you ran the installer for the OfficeWriter Designer.NET, which is a separate installer from OfficeWriter.
• If you do not see the toolbar after running the installer, the toolbar may have been disabled. Please see this Knowledge Base article for more information.

Opening a Report File

An RDL file is an xml-based report definition file. In this tutorial we will assume the RDL was created in Visual Studio or Report Builder.

1. Open Microsoft Excel 
   
   
2. Go to the OfficeWriter Designer.NET tab and click Open.
   
   
3. If your report definition file was saved on a local or network drive, choose Open From Disk from the drop down.  
   
   
   
   
4. If your RDL file was deployed to Reporting services, choose Open From Server.
   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. 
   

 

Once the RDL file is opened in the Designer, there are two ways to design your report.

1. Manually Add Fields to Your Report
2. Import a Template 

Adding Fields to Your Report

When you open the report, the Designer.NET parses the RDL for information about the data sets and makes that information available while you are working in Excel. Data sets will appear in a drop-down list and you will be able to insert fields from those data sets into the Excel template as data placeholders called data markers.

For this tutorial we will use an RDL that was created in Visual Studio containing a data set with the following query:

 

1. Place your curse in the cell A2 and click inside it. This is where we will insert the data marker. 
   
   
2. Click Data Markers on the Designer.NET toolbar and locate your data set from the drop-down list. 
   
   
   
   
3. Hover over your data set to see the fields that are available. 
    
4. Select the ProductId field to insert the data marker into cell A2. Your template should now look like this: 
    
   
5. Place your cursor in cell B2 cell and click inside it. 
   
   
6. Click Data Markers again, hover over your data set, and select the Name field. Your template should now look like this: 
   
   
   
   
7. Place your cursor in cell C2 cell and click inside it. 
   
   
8. Click Data Markers, hover over the data set, and select the ListPrice field. 
   
   
9. Place your cursor in cell D2 cell and click inside it. 
   
   
10. Click Data Markers, hover over your data set, and select the Days to Manufacture field. 
    
    
11. 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:

Importing a Template

With the Designer.NET you can use the Import Template button to import a pre-existing .xlsx template file containing data markers and formatting. 

1. Click Import Template and browse to the file you want to import.
   
   

2. You will receive the following dialog box confirming the import:
   
   
   
   
3. The sample report will look like the following when it is imported.
   
   

Publish and View Your Report

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

1. From the OfficeWriter Designer.NET toolbar select Save. You will have the option to Save to Disk or Deploy to Remote Server. 
   
   
2. Select Save to Disk. 
   
   
3. 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. 
   
   
4. Save the template as an RDL file. 

Next, publish the report to the Reporting Services server.

1. To publish the report, from the toolbar, click Save and select Deploy To Remote Server. 
   
   
2. From the Publish dialog box select the drop-down to display the Server or URL. 
   
   
3. If the dropdown contains the server you need, select it.  
   If the dropdown does not contain the server, you will need to manually enter the  Web Service URL.  The Web Service URL can be found in the SSRS Configuration Manager on the report server.  It usually looks something like this: http://<servername>/ReportServer, or if there is a named instance of SQL Server, http://<servername>/ReportServer_IntanceName 
    
4. Click Refresh.  
   
   
5. Select a folder for which you have publish rights and click Ok.  
   
   
6. If your report published with no errors, you will receive a Publish Successful message dialog. Click Ok to continue.  
   
   
7. To view the report, click View on the OfficeWriter Designer.NET toolbar. The report viewer will open another Excel window with the report results. It should look like this: 
   
   
   
   
   
8. 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 export option drop-down, choose Excel designed by OfficeWriter. 
   
   
5. When prompted, click Open . Excel will open and display your report the way you created it.

Samples

RDL: Excel-QuickStartRDL2008R2.rdl

Template: ExcelQuickStartTemplate.xlsx